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

LEFT JOIN 关键字从左表(table1)返回所有的行,即使右表(table2)中没有匹配。如果右表中没有匹配,则结果为 NULL。这篇文章主要介绍了sql中的left join以及on、where关键字的区别,需要的朋友可以参考下

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

LEFT JOIN 关键字语法

?

1

2

3

4

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。

创建两张表并插入一些数据

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

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);

查看表中的数据

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

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足够熟悉的话,先不要看结果,是否可以直接说出下面查询的结果

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

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后面是什么条件,都会返回左表中的所有行!

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

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。

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

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)和右表中满足条件的行进行匹配。

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

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都要返回左表中的所有行!

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

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关键字的区别详解,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对钦钦技术栈网站的支持!

原文链接:https://www.cnblogs.com/abclife/archive/2018/08/30/9559658.html

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/24638/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022年9月22日 上午11:18
下一篇 2022年9月22日 上午11:18
软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!