Talking! Difference between left join on where and left join on and in SQL statements.

Talking! Difference between left join on where and left join on and in SQL statements.

The road to work and study today is a small knowledge of a database. At that time, there was no distinction between them. I would like to record and share it all at once.

As we all know, database tables exist independently, but when we perform a joint query (Multi-Table query), we obtain the value returned by the database as if it were in a table, this is because the database will generate a temporary table to return the desired data information during the joint query. At this time, we are all associated through left join and other statements, we will also filter the data we want to query, and then we will use the filter statement.

Left join on where: after a temporary table is generated, the data in the temporary table is filtered and the LEFT table is returned.

Left join on and: When a temporary table is generated, the LEFT table is returned no matter whether the conditions in ON are true or not.

For example:

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

100 100 happy

2 200 200 very happy

3 300 300 super happy

4 400 400 invincible

The SQL statement is 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 [1] is executed:

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

100 100 happy

When [2] is executed:

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

100 100 happy

2 200 NULL

3 300 NULL

4 400 NULL

Likewise, both right join and full join have this feature. Note that inner join does not.

So happy to end today!

My wish is to change the world!