左，右，外和内连接之间有什么区别？ [英] What is the difference between Left, Right, Outer and Inner Joins?

问题描述

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

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

推荐答案

：假设您有学生表格和储物柜表格。

Simple Example: Lets say you have a Students table, and a Lockers 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, lets 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

右外部加入会显示所有储物柜，如果有任何分配给他们的学生。

这可用于识别没有分配学生的储物柜，或具有太多学生的储物柜。
返回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屋！