小白学习MySQL Day13 20240828

一、JOIN子句

JOIN是SQL语言中的一个特性，用于结合来自不同表的数据。

这个特性是通过SQL查询语句中的JOIN子句来实现的。

1、语法与功能

JOIN can be used to combine tables in a SELECT query .

JOIN操作是用来组合来自两个或多个表中的行的。

基于两个表之间的共同属性（通常是列）。

• Syntax:

SELECT columns

FROM table1

JOIN table2

ON table1.common_field = table2.common_field;

table1和table2是要结合的两个表。

common_field是这两个表之间共有的字段，用于指定如何连接这两个表。

2、类型

（1）交叉连接 CROSS JOIN

• Syntax: 

SELECT * FROM A

CROSS JOIN B

WHERE......;

• Same as: SELECT * FROM A, B;

• Usually needs WHERE to filter out unrelated tuples.

返回第一个表中的每一行与第二个表中的每一行的笛卡尔积。

如果第一个表有m行，第二个表有n行，那么结果集将有m*n行。

（2）内连接 INNER JOIN

返回两个表中满足指定条件的行对。Specifies a condition that pairs of rows must satisfy.

如果某个表中有行在另一个表中没有匹配的行，那么这些行就不会出现在查询结果中。

• 使用ON子句指定条件：

SELECT columns

FROM table1

INNER JOIN table2

ON table1.common_field = table2.common_field;

• 使用USING子句指定具有相同值的列名：

当两个表中要连接的列名相同时，使用USING子句直接指定列名，无需要在每个表名后都重复该列名。

SELECT columns

FROM table1

INNER JOIN table2

USING (common_field);

E.g.

1)

（3）自然连接 NATURAL JOIN

将两个表中具有相同名称的列，自动进行匹配并合并数据。

NATURAL JOIN是一种特殊的等值连接。

特点

• 自动匹配，无需在查询语句中显式指定连接条件：

       NATURAL JOIN会自动识别并匹配两个表中具有相同名称的列。

• 去除重复列：

       在结果集中，会去除重复的列，即只保留一次具有相同名称的列。

使用时需保证

• 列名一致性
• 数据类型一致性

（4）外连接 OUTER JOINs

用于返回两个表中满足连接条件的行，并且还会返回左表（LEFT OUTER JOIN）

或右表（RIGHT OUTER JOIN）中不满足连接条件的行。

对于这些不满足连接条件的行，在结果集中，对应的表的部分将包含NULL值。

1. 左外连接 LEFT OUTER JOIN

返回左表（FROM子句中指定的表）的所有行，即使右表中没有匹配的行。

如果右表中没有匹配的行，则结果中这些行的右表部分将包含NULL。

注：在MySQL中，LEFT OUTER JOIN和LEFT JOIN是等价的，通常简写为LEFT JOIN。

SELECT columns

FROM table1

LEFT OUTER JOIN table2

ON table1.common_field = table2.common_field;

2. 右外连接 RIGHT OUTER JOIN

返回右表的所有行（JOIN子句中指定的表），即使左表中没有匹配的行。

如果左表中没有匹配的行，则结果中这些行的左表部分将包含NULL。

SELECT columns

FROM table1

RIGHT OUTER JOIN table2

ON table1.common_field = table2.common_field;

3. 全外连接 FULL OUTER JOIN （MySQL不支持）

返回左表和右表中的所有行 。

当某行在另一表中没有匹配时，另一表的部分以NULL填充。

3、总结

（1）外连接和内连接的区别

1.连接结果

• 内连接：

连接结果仅包含符合连接条件的行。

只有当两个表中的行在连接条件上相匹配时，这些行才会出现在查询结果中。

如果表A和表B进行内连接，并且连接条件是A.x = B.y，那么查询结果将只包含A和B中x和y值相等的行。

• 外连接：

连接结果不仅包含了符合连接条件的行，还包括了不符合连接条件但属于连接表之一的行。

这些不符合条件的行在结果中会以NULL值填充缺失的列。

外连接根据基表的不同，可以分为：

左外连接（LEFT OUTER JOIN）：返回左表的所有行

右外连接（RIGHT OUTER JOIN）：返回右表的所有行

全外连接（FULL OUTER JOIN）：返回左表和右表中的所有行

2.应用场景的不同

• 内连接：

适用于需要同时从两个或多个表中检索匹配行的场景。

如查询某个学生及其对应的成绩信息。

• 外连接：

适用于需要保留一个表中的所有记录，同时获取与之关联的另一个表中匹配记录的场景。

如查询所有学生及其成绩（包括没有成绩的学生）。

4、示例

SELECT ID, Name, aStreet, aTown, aPostcode, pNumber, Classification

FROM

(

        (Student LEFT OUTER JOIN Phone ON Student.pID = Phone.pID)

                LEFT OUTER JOIN Address ON Student.aID = Address.aID

)

INNER JOIN Degree

ON Student.ID = Degree.ID

WHERE Grad = ‘A’;

括号的使用：明确表示首先进行的一系列左外连接操作，然后再与Degree表进行内连接。

                     第一步，从Student表开始，与Phone表进行左外连接；

                     第二步，形成的新表与Address表进行左外连接；

                    括号结束，表示完成Student-Phone-Address的连接。

最后与Degree表进行内连接。

                    WHERE Grad = 'A' 表示过滤条件，只选择Grad字段为'A'的记录。