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

This article mainly explains "what is the difference between using on or where after left join in SQL sentences". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. please follow the editor's train of thought to study and learn "what is the difference between using on or where after left join in SQL sentences?"

When I wrote SQL the day before yesterday, I wanted to turn the two records found out into one through the conditions behind the A left B join on and, but I found that there were still two.

Later, it is found that join on and will not filter the number of result records, but will only display the records of table B according to the conditions after and, and the records of table A must be displayed.

Regardless of whether and is followed by A.id=1 or B.id=1, all records in table An are displayed and associated with records in table B with an id of 1 in table An or a record with id of 1 in table B.

Run sql:

Select * from student s left join class c on s.classId=c.id order by s.id

Run sql:

Select * from student s left join class c on s.classId=c.id and s.name = "Zhang San" order by s.id

Run sql:

Select * from student s left join class c on s.classId=c.id and c.name = "Class three, Grade three" order by s.id

When a database returns a record by joining two or more tables, it generates an intermediate temporary table, and then returns the temporary table to the user.

When using left jion, the differences between on and where conditions are as follows:

1. The on condition is the condition used when generating the temporary table, which returns the record in the left table regardless of whether the condition in on is true or not.

2. The where condition is the condition that the temporary table is filtered after the temporary table is generated. At this point, there is no meaning of left join (the record of the table on the left must be returned). If the condition is not true, it will be filtered out.

Suppose there are two tables:

Table 1:tab2 Table 2:tab2 two SQL:

1 、

Select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name='AAA'

2 、

Select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name='AAA')

The first SQL process:

1. Intermediate table on condition:

Tab1.size = tab2.size

2. Filter the where condition on the intermediate table:

Tab2.name='AAA'

The second process of SQL:

1. Intermediate table on condition:

Tab1.size = tab2.size and tab2.name='AAA'

(the record in the left table will be returned if the condition is not true.) in fact, the key reason for the above result is the particularity of left join,right join,full join. No matter whether the condition on on is true or not, it will return the record in left or right table, while full has the union of left and right. While inner jion does not have this particularity, the condition is put in on and where, and the returned result set is the same.

Thank you for your reading, the above is the "SQL sentence after the difference between on or where" of the content, after the study of this article, I believe that everyone in the SQL sentence after the use of left join after the difference between on or where have a more profound understanding of this problem, the specific use of cases still need to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.