sql中的leftjoin及on、where条件关键字的区别详解LEFT JOIN 关键字会从左表 (table_name1) 那⾥返回所有的⾏，即使在右表 (table_name2) 中没有匹配的⾏。LEFT JOIN 关键字语法
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON lumn_name=lumn_name
注释：在某些数据库中， LEFT JOIN 称为 LEFT OUTER JOIN。
创建两张表并插⼊⼀些数据
create table class(
class_id int,
class_name varchar(20),
class_grade char(1)
);
insert into class values (1,'语⽂','A');
insert into class values (2,'数学','B');
insert into class values (3,'英语','C');
create table score(
class_id int,
stu_id varchar(20),
Score int
);
insert into score values (1,'A001',91);
insert into score values (2,'A001',95);
sql left join 多表连接insert into score values (1,'A002',82);
insert into score values (2,'A002',87);
insert into score values (3,'B003',65);
查看表中的数据
mysql> select * from class;
+----------+------------+-------------+
| class_id | class_name | class_grade |
+----------+------------+-------------+
| 1 | 语⽂ | A |
| 2 | 数学 | B |
| 3 | 英语 | C |
+----------+------------+-------------+
3 rows in set (0.00 sec)
mysql> select * from score;
+----------+--------+-------+
| class_id | stu_id | Score |
+----------+--------+-------+
| 1 | A001 | 91 |
| 2 | A001 | 95 |
| 1 | A002 | 82 |
| 2 | A002 | 87 |
| 3 | B003 | 65 |
+----------+--------+-------+
5 rows in set (0.00 sec)
mysql>
⽐较下⾯⼏组查询结果
--如果你对left join⾜够熟悉的话，先不要看结果，是否可以直接说出下⾯查询的结果
mysql> select * from class A left join score B on A.class_id=B.class_id;
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
| 1 | 语⽂ | A | 1 | A001 | 91 |
| 2 | 数学 | B | 2 | A001 | 95 |
| 1 | 语⽂ | A | 1 | A002 | 82 |
| 2 | 数学 | B | 2 | A002 | 87 |
| 3 | 英语 | C | 3 | B003 | 65 |
+----------+------------+-------------+----------+--------+-------+
5 rows in set (0.00 sec)
mysql> select * from class A left join score B on A.class_id=B.class_id and 1=1;
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
| 1 | 语⽂ | A | 1 | A001 | 91 |
| 2 | 数学 | B | 2 | A001 | 95 |
| 1 | 语⽂ | A | 1 | A002 | 82 |
| 2 | 数学 | B | 2 | A002 | 87 |
| 3 | 英语 | C | 3 | B003 | 65 |
+----------+------------+-------------+----------+--------+-------+
5 rows in set (0.01 sec)
mysql> select * from class A left join score B on A.class_id=B.class_id and 1=0;
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
| 1 | 语⽂ | A | NULL | NULL | NULL |
| 2 | 数学 | B | NULL | NULL | NULL |
| 3 | 英语 | C | NULL | NULL | NULL |
+----------+------------+-------------+----------+--------+-------+
3 rows in set (0.00 sec)
mysql> select * from class A left join score B on 1=0;
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
| 1 | 语⽂ | A | NULL | NULL | NULL |
| 2 | 数学 | B | NULL | NULL | NULL |
| 3 | 英语 | C | NULL | NULL | NULL |
+----------+------------+-------------+----------+--------+-------+
3 rows in set (0.00 sec)
mysql> left join的最重要特点是：不管on后⾯是什么条件，都会返回左表中的所有⾏！
mysql> select * from class A left join score B on A.class_id=B.class_id and A.class_name='语⽂';
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
| 1 | 语⽂ | A | 1 | A001 | 91 |
| 1 | 语⽂ | A | 1 | A002 | 82 |
| 2 | 数学 | B | NULL | NULL | NULL |
| 3 | 英语 | C | NULL | NULL | NULL |
+----------+------------+-------------+----------+--------+-------+
4 rows in set (0.00 sec)
mysql> select * from class A left join score B on A.class_id=B.class_id and A.class_name='数学';
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
| 2 | 数学 | B | 2 | A001 | 95 |
| 2 | 数学 | B | 2 | A002 | 87 |
| 1 | 语⽂ | A | NULL | NULL | NULL |
| 3 | 英语 | C | NULL | NULL | NULL |
+----------+------------+-------------+----------+--------+-------+
4 rows in set (0.00 sec)
mysql> select * from class A left join score B on A.class_id=B.class_id and A.class_name='英语';
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
| 3 | 英语 | C | 3 | B003 | 65 |
| 1 | 语⽂ | A | NULL | NULL | NULL |
| 2 | 数学 | B | NULL | NULL | NULL |
+----------+------------+-------------+----------+--------+-------+
3 rows in set (0.01 sec)
mysql> select * from class A left join score B on A.class_id=B.class_id and A.class_name='体育';
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
| 1 | 语⽂ | A | NULL | NULL | NULL |
| 2 | 数学 | B | NULL | NULL | NULL |
| 3 | 英语 | C | NULL | NULL | NULL |
+----------+------------+-------------+----------+--------+-------+
3 rows in set (0.00 sec)
mysql>
如果on后⾯的条件是左表中的列(lName='***')，左表中满⾜条件的⾏和右表中的⾏进⾏匹配(根据on leftTable.id=rightTable.id)；左表中不满⾜条件的⾏，直接输出，其对应的右表中的列都是null。
mysql> select * from class A left join score B on A.class_id=B.class_id and B.Score=90;
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
| 1 | 语⽂ | A | NULL | NULL | NULL |
| 2 | 数学 | B | NULL | NULL | NULL |
| 3 | 英语 | C | NULL | NULL | NULL |
+----------+------------+-------------+----------+--------+-------+
3 rows in set (0.01 sec)
mysql> select * from class A left join score B on A.class_id=B.class_id and B.Score=65;
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
| 3 | 英语 | C | 3 | B003 | 65 |
| 1 | 语⽂ | A | NULL | NULL | NULL |
| 2 | 数学 | B | NULL | NULL | NULL |
+----------+------------+-------------+----------+--------+-------+
3 rows in set (0.01 sec)
mysql>
如果on后⾯的条件是右表中的列(lName='***'),⾸先会根据(lName='***')过滤掉右表中不满⾜条件的⾏；然后，左表中的⾏根据(on leftTable.id=rightTable.id)和右表中满⾜条件的⾏进⾏匹配。
mysql> select * from class A left join score B on A.class_id=B.class_id and A.class_name='语⽂' and B.Score=90;
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
| 1 | 语⽂ | A | NULL | NULL | NULL |
| 2 | 数学 | B | NULL | NULL | NULL |
| 3 | 英语 | C | NULL | NULL | NULL |
+----------+------------+-------------+----------+--------+-------+
3 rows in set (0.00 sec)
mysql> select * from class A left join score B on A.class_id=B.class_id and A.class_name='语⽂' and B.Score=91;
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
| 1 | 语⽂ | A | 1 | A001 | 91 |
| 2 | 数学 | B | NULL | NULL | NULL |
| 3 | 英语 | C | NULL | NULL | NULL |
+----------+------------+-------------+----------+--------+-------+
3 rows in set (0.01 sec)
mysql> select * from class A left join score B on A.class_id=B.class_id and A.class_name='体育' and B.Score=90;
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
| 1 | 语⽂ | A | NULL | NULL | NULL |
| 2 | 数学 | B | NULL | NULL | NULL |
| 3 | 英语 | C | NULL | NULL | NULL |
+----------+------------+-------------+----------+--------+-------+
3 rows in set (0.00 sec)
mysql> select * from class A left join score B on A.class_id=B.class_id and A.class_name='体育' and B.Score=82;
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
| 1 | 语⽂ | A | NULL | NULL | NULL |
| 2 | 数学 | B | NULL | NULL | NULL |
| 3 | 英语 | C | NULL | NULL | NULL |
+----------+------------+-------------+----------+--------+-------+
3 rows in set (0.00 sec)
mysql>
/**********************过滤条件在on中时**********************/
总结⼀下，如果 left join on leftTable.id=rightTable.id 后还有其他条件：
(1)lName='***'，过滤左表，但是左表不满⾜条件的⾏直接输出，并将右表对应部分置为null
(2)lName='***'，过滤右表，对左表没有影响
(3)lName='***' lName='***'，就是上⾯(1)和(2)⼀起发挥作⽤
不管on后⾯有哪些条件，left join都要返回左表中的所有⾏！
mysql> select * from class A left join score B on A.class_id=B.class_id where A.class_name='语⽂';
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
| 1 | 语⽂ | A | 1 | A001 | 91 |
| 1 | 语⽂ | A | 1 | A002 | 82 |
+----------+------------+-------------+----------+--------+-------+
2 rows in set (0.01 sec)
mysql> select * from class A left join score B on A.class_id=B.class_id where A.class_name='数学';
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
| 2 | 数学 | B | 2 | A001 | 95 |
| 2 | 数学 | B | 2 | A002 | 87 |
+----------+------------+-------------+----------+--------+-------+
2 rows in set (0.00 sec)
mysql> select * from class A left join score B on A.class_id=B.class_id where A.class_name='英语';
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
| 3 | 英语 | C | 3 | B003 | 65 |
+----------+------------+-------------+----------+--------+-------+
1 row in set (0.00 sec)
mysql> select * from class A left join score B on A.class_id=B.class_id where A.class_name='体育';
Empty set (0.00 sec)
mysql> select * from class A left join score B on A.class_id=B.class_id where B.Score=90;
Empty set (0.01 sec)
mysql> select * from class A left join score B on A.class_id=B.class_id where B.Score=91;
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
| 1 | 语⽂ | A | 1 | A001 | 91 |
+----------+------------+-------------+----------+--------+-------+
1 row in set (0.00 sec)
mysql> select * from class A left join score B on A.class_id=B.class_id where A.class_name='语⽂' and B.Score=90;
Empty set (0.00 sec)
mysql> select * from class A left join score B on A.class_id=B.class_id where A.class_name='语⽂' and B.Score=91;
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
| 1 | 语⽂ | A | 1 | A001 | 91 |
+----------+------------+-------------+----------+--------+-------+
1 row in set (0.00 sec)
mysql> select * from class A left join score B on A.class_id=B.class_id where A.class_name='体育' and B.Score=90;
Empty set (0.00 sec)
mysql> select * from class A left join score B on A.class_id=B.class_id where A.class_name='体育' and B.Score=91;
Empty set (0.00 sec)
mysql>
/**********************过滤条件在where中时**********************/
过滤条件写在where中时，先根据where条件对表进⾏过滤，然后再执⾏left join
总结
以上所述是⼩编给⼤家介绍的sql中的left join及on、where关键字的区别详解，希望对⼤家有所帮助，如果⼤家有任何疑问请给我留⾔，⼩编会及时回复⼤家的。在此也⾮常感谢⼤家对⽹站的⽀持！
发表评论