我想知道如何区分所有这些不同的连接...
简单示例:假设您有一个Students
表和一个Lockers
表.在SQL中,您在连接中指定的第一个表Students
是LEFT表,第二个表Lockers
是RIGHT表.
每个学生都可以被分配到一个储物柜,因此表格中有一LockerNumber
列Student
.不止一个学生可能会在一个储物柜中,但特别是在学年开始时,你可能有一些没有储物柜的入学学生和一些没有学生分配的储物柜.
为了这个例子,假设您有100名学生,其中70名有储物柜.您共有50个储物柜,其中40个至少有1个学生,10个储物柜没有学生.
INNER JOIN相当于" 给我所有带储物柜的学生 ".
没有储物柜的学生或没有学生的任何储物柜都不见了.
返回70行
LEFT OUTER JOIN将" 向我展示所有学生,如果他们有一个相应的储物柜 ".
这可能是一般学生列表,或者可用于识别没有储物柜的学生.
返回100行
RIGHT OUTER JOIN将" 向我展示所有储物柜,如果有任何储物柜,则分配给他们 ".
这可用于识别没有学生分配的储物柜,或者有太多学生的储物柜.
返回80行(40个储物柜中的70名学生名单,以及没有学生的10个储物柜)
FULL OUTER JOIN会很傻,可能用处不大.
像" 向我展示所有学生和所有储物柜,并在可能的地方匹配"之类的东西 "
返回110行(所有100名学生,包括没有储物柜的学生.还有10个没有学生的储物柜)
在这种情况下,CROSS JOIN也相当愚蠢.
它不使用lockernumber
学生表中的链接字段,因此您最终会得到一个巨大的列表,其中列出了每个可能的学生与学生之间的配对,无论它是否真的存在.
返回5000行(100名学生x 50个储物柜).可能有用(带过滤)作为一个起点,以匹配新学生与空储物柜.
有三种基本类型的连接:
INNER
join比较两个表,只返回匹配存在的结果.第一个表中的记录在第二个表中匹配多个结果时会重复.INNER连接倾向于使结果集更小,但由于记录可以重复,因此无法保证.
CROSS
join比较两个表并返回两个表中每个可能的行组合.您可以从这种可能甚至没有意义的联接中获得大量结果,因此请谨慎使用.
OUTER
join比较两个表并在匹配可用时返回数据,否则返回NULL值.与INNER join一样,当它匹配另一个表中的多个记录时,这将复制一个表中的行.OUTER连接倾向于使结果集更大,因为它们本身不会从集合中删除任何记录.您还必须限定OUTER连接以确定添加NULL值的时间和位置:
LEFT
表示无论如何都保留第一个表中的所有记录,并在第二个表不匹配时插入NULL值.
RIGHT
意思相反:保留第二个表中的所有记录,无论如何,并在第一个表不匹配时插入NULL值.
FULL
表示保留两个表中的所有记录,如果没有匹配则在任一表中插入NULL值.
通常你会看到OUTER
语法中省略了关键字.相反,它只是"LEFT JOIN","RIGHT JOIN"或"FULL JOIN".这样做是因为INNER和CROSS连接对于LEFT,RIGHT或FULL没有任何意义,因此这些连接本身足以明确指示OUTER连接.
以下是您可能希望使用每种类型的示例:
INNER
:您想要从"发票"表中返回所有记录,以及相应的"InvoiceLines".这假定每个有效的发票至少有一行.
OUTER
:您希望返回特定发票的所有"InvoiceLines"记录及其对应的"InventoryItem"记录.这是一个也销售服务的企业,因此并非所有InvoiceLines都有IventoryItem.
CROSS
:您有一个包含10行的数字表,每行包含值"0"到"9".您希望创建要加入的日期范围表,以便最终在该范围内的每一天都有一条记录.通过CROSS将该表与其自身重复连接,您可以根据需要创建任意数量的连续整数(假设从10到1次幂开始,每个连接将1加到指数中).然后使用DATEADD()函数将这些值添加到范围的基准日期.
只有4种:
内连接:最常见的类型.为连接条件匹配的每对输入行生成输出行.
左外连接:与内连接相同,只是如果有任何行可以找到右边表中没有匹配的行,则输出一行包含左边表格中的值NULL
,每行右边表格中的值.这意味着左侧表格中的每一行在输出中至少出现一次.
右外连接:与左外连接相同,除了表的角色颠倒.
全外连接:左外连接和右外连接的组合.两个表中的每一行都将至少出现一次输出.
"交叉连接"或"笛卡尔连接"只是一个内部连接,没有指定连接条件,导致输出所有行对.
感谢RusselH指出FULL join,我省略了.
SQL JOINS的区别:
很容易记住:
INNER JOIN
仅显示两个表共有的记录.
OUTER JOIN
两个表的所有内容都合并在一起或者是否匹配.
LEFT JOIN
与LEFT OUTER JOIN
- 相同- (从第一个(最左侧)表中选择具有匹配的右表记录的记录.)
RIGHT JOIN
与RIGHT OUTER JOIN
- 相同- (从第二个(最右侧)表中选择具有匹配左表记录的记录.)
在Wikipedia上查看Join(SQL)
内连接 - 给定两个表,内连接返回两个表中存在的所有行
left/right(outer)join - 给定两个表返回连接的左表或右表中存在的所有行,当join子句匹配时将返回另一侧的行,否则将返回null那些专栏
Full Outer - 给定两个表返回所有行,并且当左列或右列不存在时将返回空值
交叉连接 - 笛卡尔连接,如果不小心使用可能会很危险
LEFT JOIN
和RIGHT JOIN
是的类型OUTER JOIN
。
INNER JOIN
是默认值-两个表中的行必须匹配连接条件。