手把手测试数据库外连接，内连接

目录

1 数据库设计

2 外连接(out join)

3 内连接（inner join）

4 全连接（Full join）

5  交叉连接

---

1 数据库设计

表名A

表A数据

/*创建表*/
DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
  `id` int NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  `age` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*创建数据*/
INSERT INTO `a` VALUES ('1', '上海', '23', '李四');
INSERT INTO `a` VALUES ('2', '北京', '33', '张三');
INSERT INTO `a` VALUES ('3', '哈哈', '23', '亲戚');
INSERT INTO `a` VALUES ('4', '深圳', '85', '李四');

表名B

表B的数据

/*创建表*/
DROP TABLE IF EXISTS `b`;
CREATE TABLE `b` (
  `id` int NOT NULL,
  `age` int DEFAULT NULL,
  `sex` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


/*创建数据*/
INSERT INTO `b` VALUES ('1', '20', '男', '李四');
INSERT INTO `b` VALUES ('2', '30', '女', '马六');
INSERT INTO `b` VALUES ('3', '10', '男', '李四');

2 外连接(out join)

在左连接和右连接时都会以一张A表为基础表，该表的内容会全部显示，然后加上A表和B表匹配的内容。 如果A表的数据在B表中没有记录。 那么在相关联的结果集行中列显示为空值（NULL）。

外连接分为外左连接(left outer join)和外右连接(right outer join)

注释：left outer join 与 left join 等价，    一般写成left join 
           right outer join 与 right join等价，一般写成right join

左连接，取左边的表的全部，右边的表按条件，符合的显示，不符合则显示null

举例：select <select list> from A left join B on A.id=B.id

 

右连接：取右边的表的全部，左边的表按条件，符合的显示，不符合则显示null

举例：select <select list> from A right join B on A.id=B.id

1. SELECT * from A LEFT JOIN B ON A.id= B.id and A.`name` = B.`name` 

SELECT * from A LEFT JOIN B ON A.id= B.id and A.`name` = B.`name` 

可以看到A表中所有数据，满足条件（A.id=B.id AND A.name=B.name）的B表的数据

2. SELECT * from A LEFT JOIN B ON A.id= B.id WHERE A.name = B.name

SELECT * from A LEFT JOIN B ON A.id= B.id WHERE A.name = B.name

这里是在SELECT * from A LEFT JOIN B ON A.id= B.id作为一张中间的临时表，然后再将这张临时表满足条件的数据返回给用户！

3. SELECT * from A LEFT JOIN B ON A.id= B.id

SELECT * from A LEFT JOIN B ON A.id= B.id

可以看到A表中所有数据，满足条件（A.id=B.id ）的B表的数据

4. SELECT * from A LEFT JOIN B ON A.name = B.name

特别注意：这个属于A表中所有数据，和满足条件的B表数据；但是由于A作为主表，B作为从表有着一对多的关系。所以会多次显示A中数据。

5. SELECT * from A LEFT JOIN B ON A.name= B.name WHERE B.age =10

SELECT * from A LEFT JOIN B ON A.name= B.name WHERE B.age =10

由以上 3条 SQL 的分析 可知 

1. sql 中的 on 和 where 的 区别？

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户！

在使用left jion时，on和where条件的区别如下：

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

2. Left Join
select * from tbl1 Left Join tbl2 where tbl1.ID = tbl2.ID
左连接后的检索结果是显示tbl1的所有数据和tbl2中满足where 条件的数据。
简言之 Left Join影响到的是右边的表

6 SELECT * from A RIGHT  JOIN B on A.id = B.id

SELECT * from A RIGHT  JOIN B on A.id = B.id

B中所有数据都显示，A中有一条数据没有显示，只显示符合条件的

7 SELECT * from A RIGHT  JOIN B ON A.id= B.id WHERE A.name = B.name

SELECT * from A RIGHT  JOIN B ON A.id= B.id WHERE A.name = B.name

8 SELECT * from A RIGHT  JOIN B on A.id = B.id AND  A.name = B.name WHERE B.age =10

SELECT * from A RIGHT  JOIN B on A.id = B.id AND  A.name = B.name WHERE B.age =10

分析可知：

   当进行 右连接的时候 会显示 的 显示 右边表的数据 和 满足 左边表的消息

   select * from tbl1 Right Join tbl2 where tbl1.ID = tbl2.ID 检索结果是tbl2的所有数据和tbl1中满足where 条件的数据。
   简言之 Right Join影响到的是左边的表。

关于左连接和右连接总结性的一句话：
左连接where只影向右表，右连接where只影响左表。

3 内连接（inner join）

内连接：也称为等值连接，返回两张表都满足条件的部分

注释：inner join 就等于 join 

inner join：理解为“有效连接”，两张表中都有的数据才会显示

1 .SELECT * from A INNER  JOIN B on A.id = B.id

SELECT * from A INNER  JOIN B on A.id = B.id

2 SELECT * from A INNER  JOIN B on A.name = B.name

SELECT * from A INNER  JOIN B on A.name = B.name

内连接和where的区别：

数据库表连接数据行匹配时所遵循的算法就是以上提到的笛卡尔积，表与表之间的连接可以看成是在做乘法运算。

比如现在数据库中有两张表，A表和B表，如下所示：

表A

表B

我们执行以下的sql语句，只是纯粹的进行表连接。

SELECT * from A JOIN B;
SELECT * from B JOIN A;

从执行结果上来看，结果符合我们以上提出的两点结论（红线标注部分）；

以第一条sql语句为例我们来看一下他的执行流程，

1，from语句把A表 和 B表从数据库文件加载到内存中。

2，join语句相当于对两张表做了乘法运算，把A表中的每一行记录按照顺序和student_subject表中记录依次匹配。

3，匹配完成后，我们得到了一张有 （A中记录数 × B表中记录数）条的临时表。 在内存中形成的临时表如表1.0所示。我们又把内存中表1.0所示的表称为‘笛卡尔积表’。　　

再看一下sql中主要关键字的执行顺序：

from  
on  
join  
where  
group by  
having  
select  
distinct  
union  
order by  

 我们看到on是在join和where前面的

如果两张表的数据量都比较大的话，那样就会占用很大的内存空间这显然是不合理的。所以，我们在进行表连接查询的时候一般都会使用JOIN xxx ON xxx的语法，ON语句的执行是在JOIN语句之前的，也就是说两张表数据行之间进行匹配的时候，会先判断数据行是否符合ON语句后面的条件，再决定是否JOIN。

因此，有一个显而易见的SQL优化的方案是，当两张表的数据量比较大，又需要连接查询时，应该使用 FROM table1 JOIN table2 ON xxx的语法，避免使用 FROM table1,table2 WHERE xxx 的语法，因为后者会在内存中先生成一张数据量比较大的笛卡尔积表，增加了内存的开销。

4 全连接（Full join）

全外连接是在结果中除了显示满足连接的条件的行外，还显示了join两侧表中所有满足检索条件的行

SELECT * from A Full join B on A.id = B.id

注意：MySQL是不支持全外的连接的，这里给出的写法适合Oracle和DB2。但是可以通过左外和右外求合集来获取全外连接的查询结果。下图是上面SQL在Oracle下执行的结果：

5  交叉连接

    使用cross joi实现交叉连接，可以将两个表的交叉连接，所得到的结果是将这两个表中各行数据的所有的组合，即这两个表所有数据行的笛卡儿积。
    交叉连接与简单连接操作非常相似，不同的是，使用交叉连接时，在from子句中多个表名之间不是逗号，而是使用cross join关键字隔开。令外，在交叉连接中不需要使用关键字on限定连接条件，但是可以添加where子句设置连接条件。