Summary left join and intermediate Table query, summary leftjoin Query

Summary left join and intermediate Table query, summary leftjoin Query

Example:

Table

 

Table B

AB table

 

I. left join process (select * from a left join AB on a. a_id = AB. a_id ):

Use the records in a to combine AB records based on the on condition for filtering and perform Cartesian product.

Filtering Process:

A: 1, "lige" AB: 1, 3

1, 2

A: 2, "jk" AB: 2, 3

A: 3, "df" AB: 3, 3

A: 4, "kk" AB: NULL

Cartesian product:

1, "lige", 1, 3

1, "lige", 1, 2

2, "jk", 2, 3

3, "df", 3, 3

4, "kk", null, null

The query result.

Ii. Three-table join Query Process

The connection query is performed with B on the result of the connection query between a and AB.

Select * from a left join AB on a. a_id = AB. a_id left join B on AB. B _id = B. B _id;

Iii. Differences between left join conditions after ON and WHERE (figure