Shulou(Shulou.com)06/01 Report--

Sql query this thing, to say it is simple, can be very simple, usually only need to use add, delete, query and change with the logical expression ability of the programming language, you can achieve all the functions. However, adding, deleting, checking and modifying does not represent all of the sql statements, and the complete sql function will be daunting. Take the connection query which is slightly more complex than the ordinary addition, deletion, query and modification, for example, using it blindly, it will also produce unexpected and dangerous results, resulting in inexplicable BUG in the program.

In the join query syntax, the first thing to bear the brunt is the difference between on filtering and where filtering. When we write a query, whether the filter conditions are placed behind on or where, the results are always the same, so why bother to let the sql query support two kinds of filters? In fact, there are differences between the two filters, but it's not easy to find if you don't dig deep.

There are three kinds of join queries in sql, cross join,inner join and outer join. In cross join and inner join, it makes no difference whether the filter conditions are placed after on or where. At the extreme, there is no problem using only on instead of where when writing these two kinds of join queries. Therefore, the difference between on filtering and where filtering is only for outer join, which is the most commonly used left join and right join.

Let's look at an example. There are two data tables, and the structure and data are shown in the figure.

Table main

Table ext

Can think of these two tables as used to store user information, main places the main information, ext table places additional information, the relationship between the two tables is 1-to-1, with id characters as the corresponding key. Now we need to filter out all the user information whose address is not Hangzhou, and the results need to include 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 and run this SQL with your brain and human flesh, and imagine what the result will be.

When the filter address' Hangzhou'is placed on on, the result of the query seems to be different from what we expected. From the results, we can see that this filter only seems to filter out the corresponding records in the ext table, while the records in the main table are not filtered out, that is, the record marked red in the image above. One of the main features of outer join relative to inner join is that it is based on one side of the table, but here it is too domineering to ignore the filter criteria by using the left table as the base.

Change the query statement slightly to 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 was just as we expected.

This difference in results starts with the various stages of the logical query of the outer join query. Generally speaking, the implementation of outer join is divided into four steps.

1. Cross join the two tables first (Cartesian product)

2. Apply on filter

3. Add external lines

4. Apply where filter

Take the sql above, which does not use a where filter, and the whole detailed process is as follows

The first step is to perform a cross-join between the two tables, and the result is as follows, which results in 36 records (the figure is incomplete)

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

This seems to be the result of our expected query, but the result will be disrupted in the next steps

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

Is it not a feeling of icing on the cake, and that's what it turns out to be?

Step 4, apply the where filter

In this question sql, since there is no where filter, the result of the previous step is the final result.

For the sql whose address is filtered in the where condition, this step works by filtering out all the records whose addresses do not belong to Hangzhou.

Through the above explanation, we can reflect the difference between the filter conditions in outer join in on and where. If developers can understand the differences in detail, they can avoid a lot of inexplicable errors in the process of writing sql.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.