SQL连接表(内连接、左连接、右连接、交叉连接、全外连接)

分享一些自己的连表总结

什么是连接表？

• 多表查询原理：将多个表通过笛卡尔积形成一个虚表，再根据查询条件筛选符合条件的数据。

• 在关系数据库中，数据分布在多个逻辑表中。 要获得完整有意义的数据集，需要使用连接来查询这些表
  中的数据。 SQL Server支持多种
  • 连接包括
    • INNER JOIN:内连接,关键字在表中存在至少一个匹配时返回行。
    • left join : 左连接，返回左表中所有的记录以及右表中连接字段相等的记录。
    • right join : 右连接，返回右表中所有的记录以及左表中连接字段相等的记录。
    • inner join : 内连接，又叫等值连接，只返回两个表中连接字段相等的行。
    • full join : 外连接，返回两个表中的行：left join right join。
    • cross join : 结果是笛卡尔积，就是第一个表的行数乘以第二个表的行数。
    • GROUP BY:全外连接， 子句必须放在 WHERE 子句中的条件之后，必须放在 ORDER BY 子句之前

每种连接类型指定SQL Server如何使用一个表中的数据来选择另一个表中的行

A. SQL Server内连接

• 内连接是SQL Server中最常用的连接之一。 内部联接子句用于查询来自两个或多个相关表的数据。
• SQL Server INNER JOIN 子句的语法：
  在此语法中，从 T1 和 T2 表中查询检索数据：
  • 首先，在 FROM 子句中指定主表( T1 )
  • 其次，在 INNER JOIN 子句和连接谓词中指定第二个表( T2 )。 只有连接谓词计算为 TRUE 的行才包
    含在结果集中。

**INNER JOIN **子句将表 T1 的每一行与表 T2 的行进行比较，以查找满足连接谓词的所有行对。 如果连接
谓词的计算结果为 TRUE ，则匹配
的 T1 和 T2 行的列值将合并为一个新行并包含在结果集中。

下图说明了两个结果集的内联接的结果：
使用两个 INNER JOIN 子句来查询三个表中的数据：

图中左右两边的表是有关联的，中间黄色的交互区就是他们两个关联表中关联的数据。
使用INNER JOIN可以在两个有关联数据的表中把关联的数据查询出来

使用两个 INNER JOIN 子句来查询三个表中的数据：
INNER JOIN 关键字在表中存在至少一个匹配时返回行。

SELECT
	--在下面吗三张表中需要查询的数据
	product_name,
	category_name,
	brand_name, 
	list_price
FROM
	数据表(1) p    -- p是数据表的引用变量，可以通过p.出数据表格(1)中的列名 
--使用INNER JOIN通过数据表(1)category_id值查询出对应在数据表(2)的关联数据
INNER JOIN 数据表(2) c ON c数据表(2).category_id = p数据表(1).category_id 
--使用INNER JOIN通过数据表(1)中brand_id值查询出对应在数据表(3)的关联数据
INNER JOIN 数据表(3) b ON b数据表(3).brand_id = p数据表(1).brand_id 
ORDER BY 	--查询结果升序排序
	product_name DESC;

B. SQL Server左连接

• LEFT JOIN 子句用于查询来自多个表的数据。它返回左表中的所有行和右表中的匹配行。
  如果在右表中找不到匹配的行，则使用 NULL 代替显示。

以下图说明了两个结果集的左连接结果：

以下说明如何使用 LEFT JOIN 子句来连接两个表 student(学生表) 和 sc(成绩表) ：

下面的小案例左表(学生表)通过某列名在右表(成绩表)中查询出在相同列名中的一行数据

SELECT
	s.sno,--学生表中的学生学号		
	c.cno,--成绩表中的课程号
	c.score--成绩表中学生的在此课程中的成绩
FROM 
	student s 			
LEFT JOIN sc c ON--使用LEFT JOIN获取sc(成绩表)
	c.sno=s.sno; --通过student(学生ID)等于成绩表中(学生ID)选择到学生表在成绩表中的数据

以下是上面左连接查询语句的查询结果:*

图中为空的格子是在右表(成绩表)中 没有该学生学号下的考试课程与成绩，所以默认返回了一个NULL值

对于 学生表中的每一行，查询将其与 成绩 表中的所有行进行比较。 如果一对行导致连接谓词计算为
TRUE ，则将组合这些行中的列值以形成新行，然后将其包含在结果集中。

• 如果左表( T1 )中的行没有与来自 T2 表的任何匹配行，则查询将左表中的行的列值与来自右表的每个列
  值的 NULL 组合。

C. ON与WHERE子句

关键字 on

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。
在使用 left jion 时，on 和 where 条件的区别如下：

• 1、 on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录。
• 2、where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

假设有两张表：

两条SQL语句:

select * from tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name='AAA'
select * from tab1 left join tab2 on (tab1.size = tab2.size and tab2.name='AAA')

其实以上结果的关键原因就是left join、right join、full join的特殊性，不管 on 上的条件是否为真都会返回 left 或 right 表中的记录，full 则具有 left 和 right 的特性的并集。 而 inner jion 没这个特殊性，则条件放在 on 中和 where 中，返回的结果集是相同的。

D. SQL Server右连接

RIGHT JOIN 子句组合来自两个或多个表的数据。

• RIGHT JOIN 开始从**右表中选择数据并与左表中的行
  匹配。 RIGHT JOIN 返回一个结果集，该结果集包含右表中的所有行，无论是否具有左表中的匹配行。
  如果右表中的行没有来自右表的任何匹配行，则结果集中右表的列将使用 NULL 值。

以下是 RIGHT JOIN 的语法：

SELECT
	select_list
FROM
	T1
RIGHT JOIN T2 ON join_predicate; 
SQL

在此语法中， T1 是左表， T2 是右表。
请注意， RIGHT JOIN 和 RIGHT OUTER JOIN 是相同的。 OUTER 关键字是可选的。
下图说明了 RIGHT JOIN 操作

橙色部分表示返回的结果集。
SQL Server RIGHT JOIN示例
以下语句返回 production.products 表中的产品名称和 sales.order_items 所有 order_id ：

SELECT
	product_name,
	order_id 
FROM
	sales.order_items o 
RIGHT JOIN 
	production.products p ON o.product_id = p.product_id 
ORDER BY 
	order_id;

E. SQL Server 交叉连接

CROSS JOIN 连接两个或多个不相关的表。
以下是两个表的SQL Server CROSS JOIN 的语法：

SELECT 
	select_list 
FROM 
	T1 
 	CROSS JOIN T2;

CROSS JOIN 将第一个表(T1)中的每一行与第二个表(T2)中的每一行连接起来。 换句话说，交叉连接返回
两个表中行的笛卡尔积。 与INNER JOIN或LEFT JOIN不同，交叉连接不会在连接的表之间建立关系。

假设 T1 表包含三行： 1 , 2 和 3 ， T2 表包含三行： A ， B 和 C 。 CROSS JOIN 从第一个表(T1)获取一行，然后为第二个表(T2)中的每一行创建一个新行。 然后它对第一个
表(T1)中的下一行执行相同操作，依此类推。

在此图中， CROSS JOIN 总共创建了 9 行。 通常，如果第一个表有 n 行，第二个表有 m 行，则交叉连接
将产生 n x m 行。

F. SQL Server自连接

自联接用于将表连接到自身(同一个表)。 它对于查询分层数据或比较同一个表中的行很有用。
自联接使用内连接或左连接子句。 由于使用自联接的查询引用同一个表，因此表别名用于为查询中的表
分配不同的名称

请注意，如果在不使用表别名的情况下在查询中多次引用同一个表，则会出现错误。

SELECT
	t1.sno,t2.sno
FROM
	sc t1,sc t2 

上面查询语句中两次引用表 T 。表别名 t1 和 t2 用于为 T 表分配不同的名称。

staffs 表存储员工信息，如身份证，名字，姓氏和电子邮件。 它还有一个名为 manager_id 的列，用
于指定直接管理者。 例如，员工 Mireya 向管理员者 Fabiola 汇报工作，因为 Mireya 的 manager_id
列中的值是 Fabiola 。 Fabiola 没有经理，因为它的 manager_id 列是一个 NULL 值。
要获取工作汇报关系，请使用自联接，如以下查询中所示：

SELECT
	e.first_name   ' '   e.last_name employee, 
	m.first_name   ' '   m.last_name manager 
FROM
	sales.staffs e 
	INNER JOIN sales.staffs m ON m.staff_id = e.manager_id 
ORDER BY 
	manager;

在这个例子中，两次引用了 staffs 表：一个是员工的 e ，另一个是管理者的 m 。
连接谓词使用 e.manager_id 和 m.staff_id 列中的值匹配 employee 和 manager 关系。
由于 INNER JOIN 效应， employee 列没有 Fabiola Jackson 。 如果用 LEFT JOIN 子句替换 INNER JOIN 子句，将获得在 employee 列中包含 Fabiola Jackson 的结果集

G. SQL Server全外链接?

FULL OUTER JOIN当左表或右表中存在匹配项时，该命令将返回所有行。
以下SQL语句选择所有客户和所有订单：

SELECT 
	Customers.CustomerName, 
	Orders.OrderID 
FROM 
	Customers 
FULL OUTER JOIN 
	Orders ON Customers.CustomerID=Orders.CustomerID 
ORDER BY 
Customers.CustomerName;

注：FULL OUTER JOIN关键字返回左表（Customers）所有行，以及所有来自右表（Orders）的行。如
果“Customers”中的某些行在“Orders”中没有匹配项，或者在“Orders”中的某些行在“Customers”中没有
匹配项，则这些行也将被列出。

这篇好文章是转载于：学新通技术网