Shulou(Shulou.com)06/01 Report--

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matching rows in the right table (table_name2).

LEFT JOIN keyword syntax

SELECT column_name (s) FROM table_name1LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name

Note: in some databases, LEFT JOIN is called LEFT OUTER JOIN.

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 (1meme 'Chinese','A'); insert into class values ('maths','B'); insert into class values ('English','C'); create table score (class_id int, stu_id varchar (20), Score int); insert into score values (1meme 'A001'); insert into score values (2meme 'A001') Insert into score values (1); insert into score values (2); insert into score values (3).

View the data in the table

Mysql > select * from class +-+ | class_id | class_name | class_grade | +-+ | 1 | language | 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 are familiar with left join, do not look at the results yet. Can you directly state 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 | language | 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 1room1 | +-+ | class_id | class_name | class_grade | class_id | stu_id | Score | +- -+ | 1 | language | 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.01sec) mysql > select * from class A left join score B on A.class_id=B.class_id and 10.00 | +-+ | class_id | class_name | class_grade | class_id | stu_id | Score | +- -+ | 1 | language | A | NULL | | 2 | Mathematics | B | NULL | | 3 | English | C | 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 | language | A | NULL | | 2 | Mathematics | B | NULL | | 3 | English | C | NULL | + -+ 3 rows in set (0.00 sec)

The most important feature of mysql > left join is that all rows in the left table are returned no matter what condition follows on!

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

If the condition after on is the column in the left table (and leftTable.colName='***'), the rows in the left table that meet the conditions are matched with the rows in the right table (according to on leftTable.id=rightTable.id); the rows in the left table that do not meet the conditions are output directly, and the columns in the corresponding 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 | | 2 | Mathematics | B | NULL | | 3 | English | C | NULL | + -+ 3 rows in set (0.01sec) 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 | 2 | Mathematics | B | NULL | + -+ 3 rows in set (0.01sec) mysql >

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

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

/ * * when the filter condition is in on * * /

To sum up, if there are other conditions after left join on leftTable.id=rightTable.id:

(1) and leftTable.colName='***', filters the left table, but the rows of the left table that do not meet the conditions are output directly, and the corresponding part of the right table is set to null

(2) and rightTable.colName='***', filters the right table and has no effect on the left table.

(3) and leftTable.colName='***' and rightTable.colName='***', works together with (1) and (2) above.

Regardless of the conditions that 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.classically named 'language' +-+ | class_id | class_name | class_grade | class_id | stu_id | Score | +- -+ | 1 | language | A | 1 | A001 | 91 | | 1 | language | 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.classically named '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.classically named '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.classically named 'Sports' Empty set (0.00 sec) mysql > select * from class A left join score B on A.class_id=B.class_id where B.Scorekeeper 90 emptiness 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 | language | 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.classically named 'language' 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.classically named 'language' and B.Score=91 +-+ | class_id | class_name | class_grade | class_id | stu_id | Score | +- -+ | 1 | language | 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.classically named '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.classically named 'Sports' and B.Scorestones 91 mysql empty set (0.00 sec) mysql >

/ * * when the filter condition is in where * * /

When the filter condition is written in where, the table is filtered according to the where condition before performing left join

Summary

The above is the detailed explanation of the differences between left join and on and where keywords in sql introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to the website!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.