The basic usage of mysql left join and the difference between on and where

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	数学	B	A002	87
2	数学	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	数学	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	数学	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	数学	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	数学	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	数学	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	数学	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，所以需要拿左表与右表的这一条数据进行关联，左表只有语文可以与右表的那一条数据对上，所以结果为：

1	语文	A	A001	91
2	数学	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。

右表中没有数据的score为90，同样左表中也没有class_name为体育，但是这并不意味着最后的结果就是空了，只要没有where条件，最终的结果数量最起码也会是左表中原先的数据数量，所以这条sql会返回左表的全部数据。

1	语文	A		
2	数学	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=‘英语’。

需要注意的点：条件是在where中的，也就是在表关联之后，再进行过滤的，所以最终的结果只会有一条：

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的数据：

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。

我们将两表连接查询后，找出结果中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!