sql 的 join、left join、full join的区别图解总结，测试，注意事项

相关文章

• sql中union all、union、intersect、minus的区别图解，测试

1. 结论图

见 https://www.runoob.com/sql/sql-join.html

2. 测试

2.1. 造数据

数据表

mysql脚本

DROP TABLE IF EXISTS `class`;
CREATE TABLE `class`
(
    `c_id`   INTEGER      NOT NULL COMMENT '班级ID',
    `c_name` VARCHAR(100) NOT NULL COMMENT '班级名',
    PRIMARY KEY (`c_id`)
) COMMENT ='班级';

INSERT INTO class (`c_id`, C_NAME)
VALUES (1, '一班'),
       (2, '二班'),
       (3, '三班');

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`
(
    `s_id`   INTEGER      NOT NULL COMMENT '学生ID',
    `s_name` VARCHAR(100) NOT NULL COMMENT '学生名',
    `c_id`   INTEGER      NOT NULL COMMENT '班级ID',
    PRIMARY KEY (`s_id`)
) COMMENT ='学生';

INSERT INTO `student` (`s_id`, `s_name`, `c_id`)
VALUES (1, '张三', 1),
       (2, '李四', 2),
       (3, '赵六', 4);

2.2. 测试

2.2.1、内连接（最常用）

***ON c.C_ID = s.C_ID 可以替换为 using(C_ID) ***

-- 1、内连接（最常用）
SELECT
	* 
FROM
	class c
	INNER JOIN student s ON c.C_ID = s.C_ID;

2.2.2、左连接1（常用）

-- 2、左连接1（常用）
SELECT
	* 
FROM
	class c
	LEFT JOIN student s ON c.c_id = s.c_id ;

2.2.3、左连接2（一般不用）

-- 3、左连接2（一般不用）
SELECT
	* 
FROM
	class c
	LEFT JOIN student s ON c.c_id = s.c_id 
WHERE
	s.s_id IS NULL;

2.2.4、全连接1（一般不用）【注意：mysql不支持full join】

-- 4、全连接1（一般不用）【注意：mysql不支持full join】
SELECT
	* 
FROM
	class c
	FULL JOIN student s ON c.c_id = s.c_id ;

2.2.5、全连接2（一般不用）【注意：mysql不支持full join】

-- 5、全连接2（一般不用）【注意：mysql不支持full join】
SELECT
	* 
FROM
	class c
	FULL JOIN student s ON c.c_id = s.c_id 
WHERE
	c.c_id IS NULL 
	OR s.s_id IS NULL;

3. 注意事项

3.1 join on 的 on后面务必只写连表条件

join on 的 on后面务必只写连表条件，不能写过滤条件，过滤条件写where后面（见下面第3个sql）。并且where写在最后面，不要先where做子表（见下面第4个sql）。
否则导致意外的数据

-- 1、正确
SELECT
	* 
FROM
	class c
	LEFT JOIN student s ON c.c_id = s.c_id;

-- 2、正确
SELECT
	* 
FROM
	class c
	LEFT JOIN student s ON c.c_id = s.c_id 
WHERE
	c.c_id IN ( '2', '3' );

-- 3、错误
SELECT
	* 
FROM
	( SELECT * FROM class WHERE c_id IN ( '2', '3' ) ) c
	LEFT JOIN student s ON c.c_id = s.c_id;

-- 4、错误
SELECT
	* 
FROM
	class c
	LEFT JOIN student s ON c.c_id = s.c_id 
	AND c.c_id IN ( '2', '3' );

3.2 mysql等数据库不支持full join

mysql、h2不支持full join
sqlite不支持full join、right join

3.3 mysql、sqlite、h2等数据库不支持sql92连表

SELECT
	* 
FROM
	class c,
	student s 
WHERE
	c.c_id = s.c_id ( + )

相关文章

sql92和SQL99的区别