On The difference between the in and left joins on and of the left join in the SQL statement

Today's work learning path is a database of small knowledge, at that time did not distinguish the why, hereby recorded share a sudden.

As we all know, the tables of the database are all separate, but when we do a union query (multi-table query), we get the value returned by the database as if it were in a table, because the database generates a temporary table to return to the data information we want when we make a federated query, and we all go through the left Joins and other statements are associated, and we also filter the data we want to query, and then we use the filter statement.

Left JOIN on where: After the temporary table is generated, the data of the temporary table is filtered, and then the table is returned.

Left JOIN on and: When a temporary table is generated, the condition in on, regardless of whether it is true, returns a table of the right.

For example:

Table 1.id table 1.value table 2.value table 2.name

1 100 100 Happy

2 200 200 very happy.

3 300 300 Super Happy

4 400 400 great fun

The SQL statements are as follows:

[1] SELECT * FROM table 1 left JOIN table 2 on (Table 1.value = table 2.value) WHERE Table 2.name = Happy

[2] SELECT * FROM table 1 left JOIN table 2 on (Table 1.value = table 2.value) on table 2.name = Happy

When executing [1], you have to:

Table 1.id table 1.value table 2.value table 2.name

1 100 100 Happy

When executing [2], you have to:

Table 1.id table 1.value table 2.value table 2.name

1 100 100 Happy

2-NULL NULL

3 NULL NULL

4 NULL NULL

Similarly, both right join and full join have this feature, and note that INNER join does not have this feature.

Let's have a happy ending today!

My wish is to change the world!

On The difference between the in and left joins on and of the left join in the SQL statement