全网：(数据过滤)连接查询的各种应用之MySQL（参考）

一、7种连接

1、内连接

隐式内连接

SELECT <select_list> FROM TableA A,TableB B WHERE A.Key = B.Key

显示内连接(效率比隐式更高，推荐)

SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.Key = B.Key

区别

• 隐式内连接: 在查询出结果的基础上去做的WHERE条件过滤(形成的中间表为两个表的笛卡尔积，即两张表的交集)
• 显示内连接: 使用了关键字，带着条件去查询结果, 执行效率要高(形成的中间表为两个表经过ON条件过滤后的笛卡尔积)
  

2、左连接

SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key

3、右连接

SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key

4、只有A

SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL

5、只有B

SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL

6、全连接

# MySQL没有FULL OUTER语法
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key
union
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;

7、A，B各自独有

# MySQL没有FULL OUTER语法。
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL
union
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL

二、SQL查询的基本原理(SQL优化基本理论)

单表查询

根据WHERE条件过滤表中的记录，形成中间表(这个中间表对用户是不可见的)，然后根据SELECT的选择列选择相应的列进行返回最终结果。

两表连接查询

对两表求积(笛卡尔积)并用ON条件和连接连接类型进行过滤形成中间表，然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。

多表连接查询

先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询。以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。

三、ON条件和WHERE条件的区别

ON条件

是过滤两个链接表笛卡尔积形成中间表的约束条件。

WHERE条件

---

在有ON条件的SELECT语句中

是过滤中间表的约束条件。

在没有ON条件的，单表查询中

是限制物理表或者中间查询结果返回记录的约束。

在没有ON条件的，在两表或多表连接中

是限制连接形成最终中间表的返回结果的约束。

---

从这里可以看出，将WHERE条件移入ON后面是不恰当的
推荐的做法是：
ON只进行连接操作，WHERE只过滤中间表的记录。

四、两表连接查询选择方式

1、 查两表关联列相等的数据用内连接。
2、 Col_L是Col_R的子集时用右外连接。
3、 Col_R是Col_L的子集时用左外连接。
4、 Col_R和Col_L彼此有交集但彼此互不为子集时候用全外。
5、 求差操作的时候用联合查询。
多个表查询的时候，这些不同的连接类型可以写到一块。

例如：
SELECT
	A.C1,
	B.CX,
	C.CY 
FROM
	TableA A
	INNER JOIN TableB B ON ( A.C1 = B.C2 )
	INNER JOIN TableC C ON ( A.C1 = B.C3 )
	LEFT OUTER JOIN TableD ON ( B.C2 = C.C3 ) 
WHERE
	A.X > C.Y;

上面这个SQL查询是多表连接的一个示范。

五、关于连接查询的实际应用，可参考博客

跳转到...