The difference between on filtering and where filtering in SQL connection queries

SQL query This thing, to say that it is simple, can be very simple, usually only need to use additions and deletions to the programming language with the logical expression ability, you can achieve all functions. But additions and deletions do not represent all of the SQL statements, and the full SQL functionality will be daunting to others. Take more than ordinary additions and deletions to change a bit more complex a level of connection query, blind use, there will be unexpected dangerous results, causing the program to appear inexplicably bug.

In the connection query syntax, the other person is the first to be confused on the filter and where the difference between the filter, when we write the query, the filter conditions are placed on the back or in the back, the results are always the same, in this case,  Then why do you have to superfluous the SQL query to support both filters? In fact, there are differences between the two filters, but it's not easy to find them if you don't dig deep.

There are 3 types of connection queries in SQL, cross Join,inner joins, and outer joins, and in cross join and inner joins, it is no different whether the filter condition is placed behind or where, at the extreme, when writing these two connection queries , only on does not use where and there is no problem. Therefore, the difference between on filtering and where filtering is only for outer joins, which is the left join and right join that are most commonly used at ordinary times.

Take a look at an example with two tables, structure and data

Table Main

Table ext

You can think of these two tables to hold the user information, main place the main information, ext table to put additional information, the relationship between the two tables is 1 to 1, with the ID character as the corresponding relationship key. Now we need to filter out all the user information of the address not for Hangzhou, the result needs to contain all the field data of the main table and the Ext table.

SELECT * from main left JOIN Exton main.id = ext.id and address <> ' Hangzhou '

Close your eyes, please run this SQL with your brain, and imagine what the results are.

When the address <> ' Hangzhou ' is placed on the filter, the results of the query seem to be different from what we expected, and from the results we can see that it seems to filter out only the corresponding records in the Ext table. The records in the main table are not filtered out, that is, the record marked in red. One of the main features of the outer join with respect to the inner join is based on a table on one side, but it is too overbearing to ignore the filter when it is based on left table.

Make a slight change to the query, and transfer the filter of the address from on to where

SELECT * FROM main left JOIN ext on main.id = ext.id where address <> ' Hangzhou '

The result is as we expected.

The difference in this result should be made from the various stages of the logical query for the outer join query. Overall, the execution of the outer join is divided into 4 steps

1. Perform cross-connect (cartesian product) to two tables first

2. Apply on Filter

3. Add external rows

4. Apply where filter

Take the SQL that does not use the where filter above, the entire detailed process of execution is as follows

The first step is to cross-connect the two tables, and the result is as follows, and this step produces 36 records (this figure shows incomplete)

The second step is to apply the on filter. There are two conditions in the filter,main.id = ext.id and address<> ' Hangzhou ', which meet the required records as follows

This seems to be the result of the query we expect, but the result will be disrupted in the next steps.

The third step is to add the outer row. One feature of the outer join is that it is based on a table on one side and null instead if the other side of the table does not have a record that meets the on filter criteria. In this query, this step is to add the record that should have been filtered back

is not the feeling of the lily, the result has become such

Fourth Step , apply the Where filter

In this issue of SQL, because there is no where filter, the result of the previous step is the final result.

And for that address filtered in the Where condition of SQL, this step has played a role, all the addresses are not part of the Hangzhou records filtered out

The above explanation has been able to reflect the difference between on and where in the outer join, and developers who can understand the differences in detail can circumvent many of the inexplicable errors that occur during the writing of SQL.

The difference between on filtering and where filtering in SQL connection queries