The difference between left join and on and where keywords in sql

Keywords: MySQL

Create two tables and insert some data

create table class(
  class_id int,
  class_name varchar(20),
  class_grade char(1)
);
 
insert into class values (1,'Chinese','A');
insert into class values (2,'Mathematics','B');
insert into class values (3,'English?','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);

View the data in the table

mysql> select * from class;
+----------+------------+-------------+
| class_id | class_name | class_grade |
+----------+------------+-------------+
|        1 | Chinese       | A           |
|        2 | Mathematics       | B           |
|        3 | English?       | 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>

Compare the following sets of query results
If you're familiar with left join, don't look at the results first. Can you say directly the results of the following query?

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 | Chinese       | A           |        1 | A001   |    91 |
|        2 | Mathematics       | B           |        2 | A001   |    95 |
|        1 | Chinese       | A           |        1 | A002   |    82 |
|        2 | Mathematics       | B           |        2 | A002   |    87 |
|        3 | English?       | 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 | Chinese       | A           |        1 | A001   |    91 |
|        2 | Mathematics       | B           |        2 | A001   |    95 |
|        1 | Chinese       | A           |        1 | A002   |    82 |
|        2 | Mathematics       | B           |        2 | A002   |    87 |
|        3 | English?       | 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 | Chinese       | A           |     NULL | NULL   |  NULL |
|        2 | Mathematics       | B           |     NULL | NULL   |  NULL |
|        3 | English?       | 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 | Chinese       | A           |     NULL | NULL   |  NULL |
|        2 | Mathematics       | B           |     NULL | NULL   |  NULL |
|        3 | English?       | C           |     NULL | NULL   |  NULL |
+----------+------------+-------------+----------+--------+-------+
3 rows in set (0.00 sec)

mysql> left join The most important feature is: no matter what. on What conditions follow will return all rows in the left table!

mysql> select * from class A left join score B on A.class_id=B.class_id and A.class_name='Chinese';
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
|        1 | Chinese       | A           |        1 | A001   |    91 |
|        1 | Chinese       | A           |        1 | A002   |    82 |
|        2 | Mathematics       | B           |     NULL | NULL   |  NULL |
|        3 | English?       | 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='Mathematics';
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
|        2 | Mathematics       | B           |        2 | A001   |    95 |
|        2 | Mathematics       | B           |        2 | A002   |    87 |
|        1 | Chinese       | A           |     NULL | NULL   |  NULL |
|        3 | English?       | 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='English?';
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
|        3 | English?       | C           |        3 | B003   |    65 |
|        1 | Chinese       | A           |     NULL | NULL   |  NULL |
|        2 | Mathematics       | 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='Sports';
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
|        1 | Chinese       | A           |     NULL | NULL   |  NULL |
|        2 | Mathematics       | B           |     NULL | NULL   |  NULL |
|        3 | English?       | C           |     NULL | NULL   |  NULL |
+----------+------------+-------------+----------+--------+-------+
3 rows in set (0.00 sec)

mysql>

If on The latter condition is the column in the left table(and leftTable.colName='***'),Match the qualified rows in the left table with the rows in the right table(according to on leftTable.id=rightTable.id)；Lines that do not satisfy the conditions in the left table are output directly, and the corresponding columns in the right table are all 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 | Language | A | NULL | NULL | NULL|
| 2 | Mathematics | B | NULL | NULL | NULL|
| 3 | English | 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 | English | C | 3 | B003 | 65|
| 1 | Language | A | NULL | NULL | NULL|
| 2 | Mathematics | B | NULL | NULL | NULL|
+----------+------------+-------------+----------+--------+-------+
3 rows in set (0.01 sec)

mysql>

If the condition after on is the column in the right table (and rightTable.colName='***'), the rows that do not satisfy the condition in the right table are filtered first according to (and rightTable.colName='***'); then the rows in the left table are matched according to (on leftTable.id=rightTable.id) and the rows that satisfy the condition in the right table.

mysql> select * from class A left join score B on A.class_id=B.class_id and A.class_name='Chinese' and B.Score=90;
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
|        1 | Chinese       | A           |     NULL | NULL   |  NULL |
|        2 | Mathematics       | B           |     NULL | NULL   |  NULL |
|        3 | English?       | 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='Chinese' and B.Score=91;
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
|        1 | Chinese       | A           |        1 | A001   |    91 |
|        2 | Mathematics       | B           |     NULL | NULL   |  NULL |
|        3 | English?       | 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='Sports' and B.Score=90;
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
|        1 | Chinese       | A           |     NULL | NULL   |  NULL |
|        2 | Mathematics       | B           |     NULL | NULL   |  NULL |
|        3 | English?       | 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='Sports' and B.Score=82;
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
|        1 | Chinese       | A           |     NULL | NULL   |  NULL |
|        2 | Mathematics       | B           |     NULL | NULL   |  NULL |
|        3 | English?       | C           |     NULL | NULL   |  NULL |
+----------+------------+-------------+----------+--------+-------+
3 rows in set (0.00 sec)

mysql>

/ ************************************************ Filtration Conditions in on/
To sum up, if left join on leftTable.id=rightTable.id, there are other conditions:
(1)and leftTable.colName='***', filter the left table, but the left table does not meet the conditions of the row output directly, and the corresponding part of the right table set to null
(2)and rightTable.colName='***', filter the right table, but have no effect on the left table.
(3) and leftTable. colName ='***'and rightTable. colName ='***', that is, above (1) and (2) work together.

No matter what conditions follow on, left join returns all rows in the left table!

mysql> select * from class A left join score B on A.class_id=B.class_id where A.class_name='Chinese';
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
|        1 | Chinese       | A           |        1 | A001   |    91 |
|        1 | Chinese       | 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='Mathematics';
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
|        2 | Mathematics       | B           |        2 | A001   |    95 |
|        2 | Mathematics       | 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='English?';
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
|        3 | English?       | 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='Sports';
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 | Chinese       | 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='Chinese' 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='Chinese' and B.Score=91;
+----------+------------+-------------+----------+--------+-------+
| class_id | class_name | class_grade | class_id | stu_id | Score |
+----------+------------+-------------+----------+--------+-------+
|        1 | Chinese       | 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='Sports' 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='Sports' and B.Score=91;
Empty set (0.00 sec)

mysql>

/ ******************************************** Filtration Conditions where in ********************************************************/

When the filter condition is written in where, the table is filtered according to where condition, and then left join is executed.

Posted by joliocesar on Sun, 03 Feb 2019 15:48:17 -0800

Programmer Group

The difference between left join and on and where keywords in sql

Hot Keywords