A left B join on and A left B join on and

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

If and is followed by a.id =1 or b.id =1, all records in table A are displayed, and the corresponding records in table A with ID 1 or the corresponding records in table B with ID 1 are displayed.

Run the SQL:

select * from student s left join class c on s.classId=c.id order by s.id
Copy the code

Run the SQL:

Select * from student s left join class c on s.classid =c.id and s.name=" c "order by s.idCopy the code

Run the SQL:

Select * from student s left join class c on s.classid =c.id and c.name="三 级三 级三 级 "order by S.idCopy the code

When a database joins two or more tables to return a record, it generates an intermediate temporary table, which is then returned to the user.

When using left Jion, the difference between on and WHERE conditions is as follows:

1. The ON condition is a condition used when generating temporary tables, which returns the records in the left table regardless of whether the condition in ON is true.

2. The WHERE condition is used to filter temporary tables after they are generated. At this point, there is no left JOIN meaning (must return the records of the left table), and all conditions are filtered if they are not true.

Suppose there are two tables:

Two SQL:

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')Copy the code

First SQL procedure:

1, the middle table on conditions:

tab1.size = tab2.size

Select * from table_name where table_name;

Tab2. Name = "AAA"

 

The second SQL procedure:

1, the middle table on conditions:

Tab1. size = tab2.size and tab2.name= 'AAA'Copy the code

(If the condition is not true, records in the left table will be returned.)

In fact, the key reason for the above results is the particularity of left Join,right Join and full Join. No matter whether the condition on is true or not, records in the left or right table will be returned. Full has the union of features of left and right. Inner jion does not have this particularity. If the condition is placed in ON and WHERE, the result set returned is the same.