The difference between the SQL query condition placed on the left OUTER JOIN and the Where

These two conditions placed in different places is very easy to confuse people, so often query out the inexplicable results, especially when the condition of the replica and the main table does not match, the following with a B table for example, I understand the simple.

The first thing to understand is:

The condition behind on is that the data that participates in the left join is filtered, which works before the left join.

The condition behind the where is to filter the result set resulting from the left join, that is, after the left join.

I sent my conclusions directly and suggested that my friends should test them on their own, and here is the conclusion:

1) If the condition is composed of a field between the main table and the secondary table, then the result is the same as in the where sub-condition, that is, the condition can be placed casually, and even when the view is built in SQL, it is automatically optimized for the on condition.

As follows:

SELECT * from A left OUTER JOIN B on A.id=b.userid and A.name=b.username

SELECT * from A left OUTER JOIN B on A.id=b.userid WHERE a.name=b.username

The result of the above two statements is the same, if you use this statement to build the view, the SQL Manager will automatically optimize to the first sentence of the wording, you can personally experiment.

2) If the condition is made up of one of the table fields in the two tables in the left JOIN, the result will be very different.

1:select * from A left OUTER joins B on A.id=b.userid and b.isdel=0 (Isdel means record is deleted, 0 is no, 1 is yes. ）

2:select * from A left OUTER joins B on A.id=b.userid WHERE b.isdel=0

The above two sentences should be used with great care, the two types of data obtained are not the same.

The first sentence means: Before you make a LEFT join, filter out data from table B that is not marked for deletion and then make a left join with table A.

The second sentence means: After the left join in a A, B table, and then the resulting results of "b.isdel=0" condition filtering.

If there are 2 of the data in table A, which can be matched in the B table, the result is the same, but assuming that only one record in a is matched in B, the results of the two statements are different.

Because the value of the corresponding B.isdel column (assuming life is B_isdel) in the result set after the left join of the two tables is actually a null value, then the "b_isdel=0" will be filtered out.

The final result is that the first statement has two data, and the second statement has only one.

In addition, there is a more bizarre situation, assuming that our demand is to a, b two tables to the left join, but also want to filter out a table has been deleted data and B table has been deleted data. There are two possible ways to do this:

3:select * from A left OUTER joins B on A.id=b.userid and A.isdel=0 and b.isdel=0 (Isdel means record is deleted, 0 is no, 1 is yes. ）

4:select * from A left OUTER joins B on A.id=b.userid WHERE a.isdel=0 and B.isdel=0

Statement 4 can get the desired result.

Statement 3, this writing is very rare, after execution, you will find that the result of statement 3 contains a table of A.isdel=1 records, this is why?

The reason is simple because it is a left join, explained below:

We know that the logic of the left join is a table or that the data in the primary table will appear in the final result set, so what does the "a.isdel=0" condition play in the process of the LEFT JOIN in statement 3?

The function is that the system in the left joint, the first in a table with "a.isdel=0" conditions to filter the data (assuming filtering out the R1 this data), with the filtered results and B table to the left JOIN,

However, the final result set returned by the entire statement will contain the R1 data, except that the fields in table B of this record are all null values. Please test your understanding yourself.

At this point, if you want to achieve our purpose, you must add the a.isdel=0 condition in the WHERE clause, namely:

SELECT * from A left OUTER JOIN B on A.id=b.userid and a.isdel=0 and b.isdel=0 WHERE a.isdel=0

But the above statement is logical, and there is absolutely no need to write this, so my advice is: for a single table field composition of the filter condition, the best way is to directly put the condition into the WHERE clause.

The difference between the SQL query condition placed on the left OUTER JOIN and the Where