Summary of the Differences Between on and where filtering in SQL connection query statements, sqlwhere

Summary of the Differences Between on and where filtering in SQL connection query statements, sqlwhere

Preface

I believe that SQL queries are simple and simple for every programmer. Generally, you only need to add, delete, query, modify, and use the logic expression capabilities of the programming language to implement all functions. However, addition, deletion, query, modification, and modification do not represent all SQL statements. The complete SQL function is daunting. For a connection query that is a little more complex than a normal addition, deletion, query, and modification, blind use may lead to unexpected and dangerous results, leading to inexplicable bugs in the program.

In the connection query syntax, the first question to be confused is the difference between on filtering and where filtering. When we compile a query, when filtering conditions are placed after the on clause or the where clause, the results are always the same. In this case, why does one need to allow SQL queries to support two filters? In fact, there are differences between the two filters, but it is not easy to find them.

Join queries in SQL are divided into three types: cross join, inner join, and outer join. In cross join and inner join, there is no difference between filtering conditions after on or after where, to be extreme, when writing these two connection queries, there is no problem if you only use on without where. Therefore, the differences between on filtering and where filtering are only for outer join, that is, the most commonly used left join and right join.

Let's take a look at the details below:

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

Table main

Table ext

The two tables can be viewed as used to store user information. The main table stores the main information, and the ext table stores additional information. The relationship between the two tables is 1-to-1, use the id character as the corresponding link key. Now we need to filter out the addresses not all users in Hangzhou, and the results need to contain all the field data in the main table and ext table.

Select * from main left JOIN exton main. id = ext. id and address <> 'hangzhou'

Close your eyes and use your brain to execute this SQL statement and imagine what the result is.

WhenAddress <> 'hangzhou'After this filter condition is placed on, the query results seem to be different from what we expected. From the results, we can see that this filter condition seems to have only filtered out the corresponding records in the ext table, the records in the main table are not filtered out, that is, the record marked as red.outer joinRelativeinner joinOne of the main features of is to use a table on one side as the basis, but here the left table is used as the basis, but the filtering conditions can be ignored, which is too domineering.

Change the query statement slightly and transfer the filtering condition 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 expected.

The difference in this result is fromouter joinStart from each stage of logical query.

In general, the execution process of outer join is divided into four steps.

1. Perform a cross join (Cartesian Product) on the two tables first)

2. Apply the on Filter

3. Add external rows

4. Apply where Filter

Take the SQL statement that does not use the where filter above for example, the detailed process of execution is as follows:

Step 1: perform a cross join operation on the two tables. The result is as follows. 36 records are generated in this step (this figure is incomplete)

Step 2: Apply the on filter. The filter has two conditions,Main. id = ext. id and address <> 'hangzhou'The following table lists the records that meet the requirements.

This seems to be the result we want to query, but in the next step, this result will be disrupted.

Step 3: Add external rows.outer joinOne feature is that the table on one side is used as the base. If the table on the other side does not have a record that meets the on filter conditions, null is used. In this query, the role of this step is to add the record that should have been filtered back.

Isn't it just a superfluous sensation?

Step 4: Apply the where Filter

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

This step filters out the SQL statement that filters out the address in the where condition and filters out records that do not belong to the address in Hangzhou.

The above explanation shows thatouter joinThe differences between the filtering conditions in on and where can be avoided by developers who can learn the differences in detail.

Summary

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message, thank you for your support.