左联接、右联接、外联接和内联接有什么区别? [英] What is the difference between Left, Right, Outer and Inner Joins?

问题描述

我想知道如何区分所有这些不同的连接......

I am wondering how to differentiate all these different joins ...

推荐答案

简单示例:假设您有一个 Students 表和一个 Lockers表.在 SQL 中，您在联接中指定的第一个表 Students 是 LEFT 表，第二个表 Lockers 是 <强>右表.

Simple Example: Let's say you have a Students table, and a Lockers table. In SQL, the first table you specify in a join, Students, is the LEFT table, and the second one, Lockers, is the RIGHT table.

每个学生都可以分配到一个储物柜，因此Student 表中有一个LockerNumber 列.一个储物柜里可能会有不止一个学生，但尤其是在学年开始时，您可能会有一些没有储物柜的新生和一些没有分配学生的储物柜.

Each student can be assigned to a locker, so there is a LockerNumber column in the Student table. More than one student could potentially be in a single locker, but especially at the beginning of the school year, you may have some incoming students without lockers and some lockers that have no students assigned.

就本示例而言，假设您有 100 名学生，其中 70 人有储物柜.您总共有 50 个储物柜，其中 40 个储物柜至少有 1 个学生，10 个储物柜没有学生.

For the sake of this example, let's say you have 100 students, 70 of which have lockers. You have a total of 50 lockers, 40 of which have at least 1 student and 10 lockers have no student.

INNER JOIN 等效于向我展示所有带储物柜的学生".
任何没有储物柜的学生，或任何没有学生的储物柜都丢失了.
返回 70 行

LEFT OUTER JOIN 将向我展示所有学生，如果他们有相应的储物柜".
这可能是一个一般的学生名单，或者可以用来识别没有储物柜的学生.
返回 100 行

LEFT OUTER JOIN would be "show me all students, with their corresponding locker if they have one".
This might be a general student list, or could be used to identify students with no locker.
Returns 100 rows

RIGHT OUTER JOIN 将是向我展示所有储物柜，以及分配给它们的学生(如果有的话)".
这可用于识别没有分配学生的储物柜，或有太多学生的储物柜.
返回 80 行(40 个储物柜中的 70 个学生的列表，加上没有学生的 10 个储物柜)

RIGHT OUTER JOIN would be "show me all lockers, and the students assigned to them if there are any".
This could be used to identify lockers that have no students assigned, or lockers that have too many students.
Returns 80 rows (list of 70 students in the 40 lockers, plus the 10 lockers with no student)

FULL OUTER JOIN 会很傻，可能没有多大用处.
像给我展示所有学生和所有储物柜，并尽可能地匹配它们"之类的东西.
返回 110 行(所有 100 名学生，包括没有储物柜的学生.加上没有学生的 10 个储物柜)

FULL OUTER JOIN would be silly and probably not much use.
Something like "show me all students and all lockers, and match them up where you can"
Returns 110 rows (all 100 students, including those without lockers. Plus the 10 lockers with no student)

CROSS JOIN 在这种情况下也相当愚蠢.
它不使用学生表中链接的 lockernumber 字段，所以你基本上最终得到每个可能的学生与储物柜配对的巨大清单，无论它是否确实存在.
返回 5000 行(100 名学生 x 50 个储物柜).作为将新学生与空储物柜相匹配的起点，可能很有用(通过过滤).

CROSS JOIN is also fairly silly in this scenario.
It doesn't use the linked lockernumber field in the students table, so you basically end up with a big giant list of every possible student-to-locker pairing, whether or not it actually exists.
Returns 5000 rows (100 students x 50 lockers). Could be useful (with filtering) as a starting point to match up the new students with the empty lockers.

这篇关于左联接、右联接、外联接和内联接有什么区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！