Preface

When we write SQL statements, we cannot avoid using connection keywords, such as inner connections and outer connections. There are many types. I will post here a picture I found elsewhere:

I think this picture is very detailed. It shows the SQL statements. Common link types, taking left join in this article as an example, are defined online as follows: The LEFT JOIN keyword will return all rows from the left table, even if there are no matching rows in the right table.

In fact, just from the literal meaning, left join is relatively easy to understand, but in the process of using it, there will still be some problems, such as the condition after on and after After where, their results are completely different. Next, we will learn about left join from shallow to deep.

Example

CREATE TABLE `class` (
  `class_id` int NOT NULL,
  `class_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `class_grade` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`class_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=COMPACT;

score table:

CREATE TABLE `score` (
  `class_id` int NOT NULL,
  `stu_id` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `score` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=COMPACT;

They each have data:

Q1: select c.class_id,c.class_name,c.class_grade,s.stu_id,s.score from class c left join score s on c.class_id=s.class_id.

This sentence can be rewritten as: We analyzed this statement. The left table is the class table, the right table is the score table, and their associated field is class_id. When the class_id in the left table is 1, there are two records in the right table with class_id 1; when the class_id in the left table is 2, the class_id in the two records in the right table is 2; when the class_id in the left table is 3, there is one record in the right table The class_id of the record is 3, so we should get five records:

1	語文	A	A002	82
1	語文	A	A001	91
2	數(shù)學(xué)	B	A002	87
2	數(shù)學(xué)	B	A001	95
3	英語	C	B003	65

Q2: select c.class_id,c.class_name,c.class_grade,s.stu_id,s.score from class c left join score s on c.class_id=s.class_id and s.score=90.
Based on the example of the first question, this one has an additional condition of s.score=90. This means that the score field of the right table score is 90, but when we look at the data in the table, we find that there is no score of 90 in the right table. , ok, will the result be empty? After all, the table on the right does not have data that meets the conditions.

In fact, if you execute this SQL statement, you will eventually get the result, but you will only get three pieces of data. All fields in the left table are displayed, and all fields in the right table are empty. This is because there is no record in the right table with a score of 90, so the fields in the right table are empty.

on filters the data first and then connects it, while where filters the data after performing a related query between the two tables. The difference between on and where is here, and will be covered in subsequent examples. These two are different.

1	語文	A		
2	數(shù)學(xué)	B		
3	英語	C

Q3: select c.class_id,c.class_name,c.class_grade,s.stu_id,s.score from class c left join score s on c.class_id=s.class_id where c.class_name=&lsquo ;Chinese’ and s.score=90.

You need to pay attention to the difference between where and on, because this statement involves the where keyword. The query result of this sql is empty. This is because the two tables are connected to query the results first, and then filtered. The result of the query of the connected table is that there is no record matching class_name=‘中文’, and score=90, so The query result is empty.

Q4: select c.class_id,c.class_name,c.class_grade,s.stu_id,s.score from class c left join score s on c.class_id=s.class_id and 1=0.

We sometimes add 1=1 after the where condition of SQL, and 1=0 here means that the association between the two tables has failed, so the result will only display the data from the left table.

1	語文	A	null	null
2	數(shù)學(xué)	B	null	null
3	英語	C	null	null

Q5: select c.class_id,c.class_name,c.class_grade,s.stu_id,s.score from class c left join score s on 1=0.

The execution result of this is the same as the above SQL statement. In fact, all the contents of the left table are displayed.

1	語文	A	null	null
2	數(shù)學(xué)	B	null	null
3	英語	C	null	null

Q6: select c.class_id,c.class_name,c.class_grade,s.stu_id,s.score from class c left join score s on c.class_id=s.class_id and c.class_name=&lsquo "Chinese".

This requires filtering out the records with class_name as language in the right table, there are two, and then connecting the two records in the left table, so it is not difficult to see that there are four results:

1	語文	A	A002	82
1	語文	A	A001	91
2	數(shù)學(xué)	B	null	null
3	英語	C	null	null

Q7: select c.class_id,c.class_name,c.class_grade,s.stu_id,s.score from class c left join score s on c.class_id=s.class_id and c.class_name=‘English’.

Analyze the same question as the previous one (the result in the blank area is null):

1	語文	A		
2	數(shù)學(xué)	B		
3	英語	C	B003	65

Q8: select c.class_id,c.class_name,c.class_grade,s.stu_id,s.score from class c left join score s on c.class_id=s.class_id and c.class_name=‘體育’.

There is no data in the right table and the class_name is sports, so the right table is empty. All data in the left table is displayed. The corresponding fields in the right table are empty:

1	語文	A		
2	數(shù)學(xué)	B		
3	英語	C

Q9: select c .class_id,c.class_name,c.class_grade,s.stu_id,s.score from class c left join score s on c.class_id=s.class_id and c.class_name=‘中文’ and s.score=91.

右表中只有一條記錄的score為91，所以需要拿左表與右表的這一條數(shù)據(jù)進(jìn)行關(guān)聯(lián)，左表只有語文可以與右表的那一條數(shù)據(jù)對上，所以結(jié)果為：

1	語文	A	A001	91
2	數(shù)學(xué)	B		
3	英語	C

Q10：select c.class_id,c.class_name,c.class_grade,s.stu_id,s.score from class c left join score s on c.class_id=s.class_id and c.class_name=‘體育’ and s.score=90。

右表中沒有數(shù)據(jù)的score為90，同樣左表中也沒有class_name為體育，但是這并不意味著最后的結(jié)果就是空了，只要沒有where條件，最終的結(jié)果數(shù)量最起碼也會(huì)是左表中原先的數(shù)據(jù)數(shù)量，所以這條sql會(huì)返回左表的全部數(shù)據(jù)。

1	語文	A		
2	數(shù)學(xué)	B		
3	英語	C

Q11：select c.class_id,c.class_name,c.class_grade,s.stu_id,s.score from class c left join score s on c.class_id=s.class_id where c.class_name=‘英語’。

需要注意的點(diǎn)：條件是在where中的，也就是在表關(guān)聯(lián)之后，再進(jìn)行過濾的，所以最終的結(jié)果只會(huì)有一條：

3	英語	C	B003	65

Q12：select c.class_id,c.class_name,c.class_grade,s.stu_id,s.score from class c left join score s on c.class_id=s.class_id where s.score=91。

和上面一樣，是在連表查詢之后，找出score=90的數(shù)據(jù)：

1	語文	A	A001	91

Q12；select c.class_id,c.class_name,c.class_grade,s.stu_id,s.score from class c left join score s on c.class_id=s.class_id where c.class_name=‘語文’ and s.score=91。

我們將兩表連接查詢后，找出結(jié)果中class_name為語文，score為91的記錄，只有一條：

1	語文	A	A001	91

The above is the detailed content of The basic usage of mysql left join and the difference between on and where. For more information, please follow other related articles on the PHP Chinese website!