sql中的left join及on、where条件关键字的区别详解

数据库 2024/11/23 佚名

3 2 1

LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。

LEFT JOIN 关键字语法

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2 
ON table_name1.column_name=table_name2.column_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);
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后面的条件是左表中的列(and leftTable.colName='***'),左表中满足条件的行和右表中的行进行匹配(根据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后面的条件是右表中的列(and rightTable.colName='***'),首先会根据(and rightTable.colName='***')过滤掉右表中不满足条件的行；然后，左表中的行根据(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)and leftTable.colName='***'，过滤左表，但是左表不满足条件的行直接输出，并将右表对应部分置为null
(2)and rightTable.colName='***'，过滤右表，对左表没有影响
(3)and leftTable.colName='***' and rightTable.colName='***'，就是上面(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关键字的区别详解，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对网站的支持！

sql,left,join,sql,on,sql,where

标签：

sql,left,join,sql,on,sql,where

免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com

评论“sql中的left join及on、where条件关键字的区别详解”

sql中的left join及on、where条件关键字的区别详解

暂无“sql中的left join及on、where条件关键字的区别详解”评论...

www.imxmx.com 杰晶网络

8,675无损音乐

1,324高清电影

213破解软件

120,141站长资源

sql中的left join及on、where条件关键字的区别详解

sql,left,join,sql,on,sql,where

SQL Server 分页编号的另一种方式【推荐】

MyBatis SQL xml处理小于号与大于号正确的格式

评论“sql中的left join及on、where条件关键字的区别详解”

RTX 5090要首发性能要翻倍！三星展示GDDR7显存

更新动态

友情链接

sql中的left join及on、where条件关键字的区别详解

sql,left,join,sql,on,sql,where

SQL Server 分页编号的另一种方式【推荐】

MyBatis SQL xml处理小于号与大于号正确的格式

评论“sql中的left join及on、where条件关键字的区别详解”

RTX 5090要首发 性能要翻倍！三星展示GDDR7显存

更新动态

友情链接

RTX 5090要首发性能要翻倍！三星展示GDDR7显存