表连接本质:把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户.
举例:
mysql> CREATE TABLE t1 (m1 int, n1 char(1)); Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE t2 (m2 int, n2 char(1)); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO t1 VALUES(1, ‘a‘), (2, ‘b‘), (3, ‘c‘); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t2 VALUES(2, ‘b‘), (3, ‘c‘), (4, ‘d‘); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 (admin@g1-db-test-v07:5001)[jinhailan]>select * from t1; +------+------+ | m1 | n1 | +------+------+ | 1 | a | | 2 | b | | 3 | c | +------+------+ 3 rows in set (0.00 sec) (admin@g1-db-test-v07:5001)[jinhailan]>select * from t2; +------+------+ | m2 | n2 | +------+------+ | 2 | b | | 3 | c | | 4 | d | +------+------+ 3 rows in set (0.00 sec)
t1和t2连接过程:
连接查询的结果集中包含一个表中的每一条记录与另一个表中的每一条记录相互匹配的组合，像这样的结果集就可以称之为笛卡尔积
t1和t2表连接查询如下:
(admin@g1-db-test-v07:5001)[jinhailan]>select * from t1,t2; +------+------+------+------+ | m1 | n1 | m2 | n2 | +------+------+------+------+ | 1 | a | 2 | b | | 2 | b | 2 | b | | 3 | c | 2 | b | | 1 | a | 3 | c | | 2 | b | 3 | c | | 3 | c | 3 | c | | 1 | a | 4 | d | | 2 | b | 4 | d | | 3 | c | 4 | d | +------+------+------+------+ 9 rows in set (0.00 sec)
表连接过程
没有限制的表连接产生笛卡尔积,结果集是非常巨大的,所以在连接的时候过滤掉特定记录组合是有必要的.
连接查询中,过滤条件可以分为两种:
1.涉及单表的条件:
也一直称为搜索条件，比如t1.m1 > 1是只针对t1表的过滤条件，t2.n2 < ‘d‘是只针对t2表的过滤条件。
2.涉及两表的条件
比如t1.m1 = t2.m2、t1.n1 > t2.n2等，这些条件中涉及到了两个表.
看以下例子
SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < ‘d‘;
在这个查询中我们指明了这三个过滤条件：
-
t1.m1 > 1 -
t1.m1 = t2.m2 -
t2.n2 < ‘d‘
整个过程如下:
1.首先确定第一个需要查询的表，这个表称之为驱动表。怎样在单表中执行查询语句我们在前一章都唠叨过了，只需要选取代价最小的那种访问方法去执行单表查询语句就好了（就是说从const、ref、ref_or_null、range、index、all这些执行方法中选取代价最小的去执行查询）。此处假设使用t1作为驱动表，那么就需要到t1表中找满足t1.m1 > 1的记录，因为表中的数据太少，我们也没在表上建立二级索引，所以此处查询t1表的访问方法就设定为all吧，也就是采用全表扫描的方式执行单表查询。
2.
针对上一步骤中从驱动表产生的结果集中的每一条记录，分别需要到t2表中查找匹配的记录，所谓匹配的记录，指的是符合过滤条件的记录。因为是根据t1表中的记录去找t2表中的记录，所以t2表也可以被称之为被驱动表。上一步骤从驱动表中得到了2条记录，所以需要查询2次t2表。此时涉及两个表的列的过滤条件t1.m1 = t2.m2就派上用场了：
-
当
t1.m1 = 2时，过滤条件t1.m1 = t2.m2就相当于t2.m2 = 2，所以此时t2表相当于有了t2.m2 = 2、t2.n2 < ‘d‘这两个过滤条件，然后到t2表中执行单表查询。 -
当
t1.m1 = 3时，过滤条件t1.m1 = t2.m2就相当于t2.m2 = 3，所以此时t2表相当于有了t2.m2 = 3、t2.n2 < ‘d‘这两个过滤条件，然后到t2表中执行单表查询。
最后结果:
(admin@g1-db-test-v07:5001)[jinhailan]>SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < ‘d‘; +------+------+------+------+ | m1 | n1 | m2 | n2 | +------+------+------+------+ | 2 | b | 2 | b | | 3 | c | 3 | c | +------+------+------+------+ 2 rows in set (0.00 sec)
从上边两个步骤可以看出来，我们上边唠叨的这个两表连接查询共需要查询1次t1表，2次t2表。当然这是在特定的过滤条件下的结果，如果我们把t1.m1 > 1这个条件去掉，那么从t1表中查出的记录就有3条，就需要查询3次t2表了。也就是说在两表连接查询中，驱动表只需要访问一次，被驱动表可能被访问多次。
内连接和外连接
例子
(admin@g1-db-test-v07:5001)[jinhailan]>CREATE TABLE student (
-> number INT NOT NULL AUTO_INCREMENT COMMENT ‘学号‘,
-> name VARCHAR(5) COMMENT ‘姓名‘,
-> major VARCHAR(30) COMMENT ‘专业‘,
-> PRIMARY KEY (number)
-> ) Engine=InnoDB CHARSET=utf8 COMMENT ‘学生信息表‘;
CREATE TABLE score (
number INT COMMENT ‘学号‘,Query OK, 0 rows affected (0.15 sec)
(admin@g1-db-test-v07:5001)[jinhailan]>
(admin@g1-db-test-v07:5001)[jinhailan]>CREATE TABLE score (
-> number INT COMMENT ‘学号‘,
-> subject VARCHAR(30) COMMENT ‘科目‘,
-> score TINYINT COMMENT ‘成绩‘,
-> PRIMARY KEY (number, subject)
-> ) Engine=InnoDB CHARSET=utf8 COMMENT ‘学生成绩表‘;
Query OK, 0 rows affected (0.11 sec)
(admin@g1-db-test-v07:5001)[jinhailan]>insert into student values(20180101,‘杜子腾‘,‘软件学院‘);
Query OK, 1 row affected (0.05 sec)
(admin@g1-db-test-v07:5001)[jinhailan]>insert into student values(20180102,‘范统‘,‘计算机科学与工程‘);
Query OK, 1 row affected (0.03 sec)
(admin@g1-db-test-v07:5001)[jinhailan]>insert into student values(20180103,‘史珍香‘,‘计算机科学与工程‘);
Query OK, 1 row affected (0.05 sec)
(admin@g1-db-test-v07:5001)[jinhailan]>insert into score values(20180101,‘母猪的产后护理‘,78);
Query OK, 1 row affected (0.01 sec)
(admin@g1-db-test-v07:5001)[jinhailan]>insert into score values(20180101,‘萨达姆的战争准备‘,88);
Query OK, 1 row affected (0.05 sec)
(admin@g1-db-test-v07:5001)[jinhailan]>insert into score values(20180102,‘萨达姆的战争准备‘,100);
Query OK, 1 row affected (0.01 sec)
(admin@g1-db-test-v07:5001)[jinhailan]>insert into score values(20180102,‘母猪的产后护理‘,98);
Query OK, 1 row affected (0.07 sec)
(admin@g1-db-test-v07:5001)[jinhailan]> SELECT * FROM student;
+----------+-----------+--------------------------+
| number | name | major |
+----------+-----------+--------------------------+
| 20180101 | 杜子腾 | 软件学院 |
| 20180102 | 范统 | 计算机科学与工程 |
| 20180103 | 史珍香 | 计算机科学与工程 |
+----------+-----------+--------------------------+
3 rows in set (0.00 sec)
(admin@g1-db-test-v07:5001)[jinhailan]>SELECT * FROM score;
+----------+--------------------------+-------+
| number | subject | score |
+----------+--------------------------+-------+
| 20180101 | 母猪的产后护理 | 78 |
| 20180101 | 萨达姆的战争准备 | 88 |
| 20180102 | 母猪的产后护理 | 98 |
| 20180102 | 萨达姆的战争准备 | 100 |
+----------+--------------------------+-------+
4 rows in set (0.00 sec)
想查每个学生的成绩,根据学号做关联
(admin@g1-db-test-v07:5001)[jinhailan]>SELECT * FROM student, score WHERE student.number = score.number; +----------+-----------+--------------------------+----------+--------------------------+-------+ | number | name | major | number | subject | score | +----------+-----------+--------------------------+----------+--------------------------+-------+ | 20180101 | 杜子腾 | 软件学院 | 20180101 | 母猪的产后护理 | 78 | | 20180101 | 杜子腾 | 软件学院 | 20180101 | 萨达姆的战争准备 | 88 | | 20180102 | 范统 | 计算机科学与工程 | 20180102 | 母猪的产后护理 | 98 | | 20180102 | 范统 | 计算机科学与工程 | 20180102 | 萨达姆的战争准备 | 100 | +----------+-----------+--------------------------+----------+--------------------------+-------+ 4 rows in set (0.05 sec) (admin@g1-db-test-v07:5001)[jinhailan]>SELECT s1.number, s1.name, s2.subject, s2.score FROM student AS s1, score AS s2 WHERE s1.number = s2.number; #缩减一下显示的列 +----------+-----------+--------------------------+-------+ | number | name | subject | score | +----------+-----------+--------------------------+-------+ | 20180101 | 杜子腾 | 母猪的产后护理 | 78 | | 20180101 | 杜子腾 | 萨达姆的战争准备 | 88 | | 20180102 | 范统 | 母猪的产后护理 | 98 | | 20180102 | 范统 | 萨达姆的战争准备 | 100 | +----------+-----------+--------------------------+-------+ 4 rows in set (0.00 sec)
史珍香同学，也就是学号为20180103的同学因为某些原因没有参加考试，如果想把没有成绩的同学信息也显示,就出现了两个概念.
内连接和外连接
内连接:
对于内连接的两个表，驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集，我们上边提到的连接都是所谓的内连接。
外连接:
对于外连接的两个表，驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。
根据选择的驱动表的不同,外连接还能分为:
-
左外连接
选取左侧的表为驱动表。
-
右外连接
选取右侧的表为驱动表。
即使对于外连接来说，有时候我们也并不想把驱动表的全部记录都加入到最后的结果集。这就犯难了，有时候匹配失败要加入结果集，有时候又不要加入结果集，这咋办，有点儿愁啊。。。噫，把过滤条件分为两种不就解决了这个问题了么，所以放在不同地方的过滤条件是有不同语义的
-
WHERE子句中的过滤条件WHERE子句中的过滤条件就是我们平时见的那种，不论是内连接还是外连接，凡是不符合WHERE子句中的过滤条件的记录都不会被加入最后的结果集。 -
ON子句中的过滤条件对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配
ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充。需要注意的是，这个
ON子句是专门为外连接驱动表中的记录在被驱动表找不到匹配记录时应不应该把该记录加入结果集这个场景下提出的，所以如果把ON子句放到内连接中，MySQL会把它和WHERE子句一样对待，也就是说：内连接中的WHERE子句和ON子句是等价的。
一般情况下，我们都把只涉及单表的过滤条件放到WHERE子句中，把涉及两表的过滤条件都放到ON子句中，我们也一般把放到ON子句中的过滤条件也称之为连接条件。
小贴士： 左外连接和右外连接简称左连接和右连接，所以下边提到的左外连接和右外连接中的`外`字都用括号扩起来，以表示这个字儿可有可无。
左（外）连接的语法
SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];
其中中括号里的OUTER单词是可以省略的。对于LEFT JOIN类型的连接来说，我们把放在左边的表称之为外表或者驱动表，右边的表称之为内表或者被驱动表。所以上述例子中t1就是外表或者驱动表，t2就是内表或者被驱动表。需要注意的是，对于左（外）连接和右（外）连接来说，必须使用ON子句来指出连接条件。
(admin@g1-db-test-v07:5001)[jinhailan]>SELECT s1.number, s1.name, s2.subject, s2.score FROM student AS s1 LEFT JOIN score AS s2 ON s1.number = s2.number; +----------+-----------+--------------------------+-------+ | number | name | subject | score | +----------+-----------+--------------------------+-------+ | 20180101 | 杜子腾 | 母猪的产后护理 | 78 | | 20180101 | 杜子腾 | 萨达姆的战争准备 | 88 | | 20180102 | 范统 | 母猪的产后护理 | 98 | | 20180102 | 范统 | 萨达姆的战争准备 | 100 | | 20180103 | 史珍香 | NULL | NULL | +----------+-----------+--------------------------+-------+
右（外）连接的语法
SELECT * FROM t1 RIGHT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];
只不过驱动表是右边的表，被驱动表是左边的表，具体就不唠叨了。
内连接的语法
内连接和外连接的根本区别就是在驱动表中的记录不符合ON子句中的连接条件时不会把该记录加入到最后的结果集，我们最开始唠叨的那些连接查询的类型都是内连接。不过之前仅仅提到了一种最简单的内连接语法，就是直接把需要连接的多个表都放到FROM子句后边。其实针对内连接，MySQL提供了好多不同的语法，我们以t1和t2表为例瞅瞅：
SELECT * FROM t1 [INNER | CROSS] JOIN t2 [ON 连接条件] [WHERE 普通过滤条件];
也就是说在MySQL中，下边这几种内连接的写法都是等价的：
SELECT * FROM t1 JOIN t2; SELECT * FROM t1 INNER JOIN t2; SELECT * FROM t1 CROSS JOIN t2;
上边的这些写法和直接把需要连接的表名放到FROM语句之后，用逗号,分隔开的写法是等价的
SELECT * FROM t1, t2;
现在我们虽然介绍了很多种内连接的书写方式，不过熟悉一种就好了，这里我们推荐INNER JOIN的形式书写内连接（因为INNER JOIN语义很明确嘛，可以和LEFT JOIN和RIGHT JOIN很轻松的区分开）。这里需要注意的是，由于在内连接中ON子句和WHERE子句是等价的，所以内连接中不要求强制写明ON子句。
连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。不论哪个表作为驱动表，两表连接产生的笛卡尔积肯定是一样的。而对于内连接来说，由于凡是不符合ON子句或WHERE子句中的条件的记录都会被过滤掉，其实也就相当于从两表连接的笛卡尔积中把不符合过滤条件的记录给踢出去，所以对于内连接来说，驱动表和被驱动表是可以互换的，并不会影响最后的查询结果。但是对于外连接来说，由于驱动表中的记录即使在被驱动表中找不到符合ON子句条件的记录时也要将其加入到结果集，所以此时驱动表和被驱动表的关系就很重要了，也就是说左外连接和右外连接的驱动表和被驱动表不能轻易互换。
(admin@g1-db-test-v07:5001)[jinhailan]>select * from t1; +------+------+ | m1 | n1 | +------+------+ | 1 | a | | 2 | b | | 3 | c | +------+------+ 3 rows in set (0.00 sec) (admin@g1-db-test-v07:5001)[jinhailan]>select * from t2; +------+------+ | m2 | n2 | +------+------+ | 2 | b | | 3 | c | | 4 | d | +------+------+ 3 rows in set (0.00 sec) (admin@g1-db-test-v07:5001)[jinhailan]>SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2; +------+------+------+------+ | m1 | n1 | m2 | n2 | +------+------+------+------+ | 2 | b | 2 | b | | 3 | c | 3 | c | | 1 | a | NULL | NULL | +------+------+------+------+ 3 rows in set (0.00 sec) (admin@g1-db-test-v07:5001)[jinhailan]>SELECT * FROM t1 RIGHT JOIN t2 ON t1.m1 = t2.m2; +------+------+------+------+ | m1 | n1 | m2 | n2 | +------+------+------+------+ | 2 | b | 2 | b | | 3 | c | 3 | c | | NULL | NULL | 4 | d | +------+------+------+------+ 3 rows in set (0.00 sec) (admin@g1-db-test-v07:5001)[jinhailan]>SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2; +------+------+------+------+ | m1 | n1 | m2 | n2 | +------+------+------+------+ | 2 | b | 2 | b | | 3 | c | 3 | c | +------+------+------+------+ 2 rows in set (0.00 sec)
连接的原理
原文：https://www.cnblogs.com/asea123/p/12372236.html
如果您也喜欢它,动动您的小指点个赞吧
602392714
清零编程群