Restrictions on the left and right connections on the database

Test environment:

MySQL 5.7.19

Heidisql 9.3 Database Interface Connection tool (very useful)

The problems encountered are:

 and where t2.age>18 Two statement returns whether the result set is the same

Before on the only two tables in the connection conditions, did not consider the left and right connections on the following column values have restrictions, so carefully analyze the effect of this constraint

I. Establishment of test Form 1, T_basicinfo

Table T_basicinfo has 3 column information values, the first column marks a unique ID, and the second column is the basic information

2, T_detailinfo

Table T_detailinfo has 4 column information values, and the first three columns represent the same information as table T_basicinfo. But this table does not have a table t_basicinfo all the people's information, only part of the person's details (address).

Let's take a look at the left and right connection statements we usually write, taking left join as an example

#测试易于理解情况下, use the * to output all column information directly, and do not use * under non-test conditions.

Select * from T_basicinfo T1 left outer joins t_detailinfo t2 on t1.id = t2.id

The output results are as follows:

The above is our most commonly used left-link syntax, but what about adding a restriction after on?

Second, after on add other than the connection conditions of the restrictions of conditions

Test how to limit the filter results to different conditions:

① to limit the ID column (connection column)

② restrictions on name in T1

③ restrictions on name in T2

1. Limit the ID

(1) Restrictions on t1.id

T1.id > 2

The query results are as follows:

(2) Restrictions on T2.id

T2.id > 2

The query results are as follows:

Result Analysis: query results are the same. restricting the column (ID) in the join condition after on is only limited to the information in the secondary table (T2), and the number of rows in the T1 does not decrease

We can't jump to conclusions, then test.

2. Limit the columns (non-associative columns) in T1

T1.age > 19

The query results are as follows:

Results analysis: after the columns in the T1 are restricted, only the T1 rows that satisfy the conditions are left connected, and the number of rows in the T1 is not reduced.

3. Limit the columns (non-associative columns) in T2

T2.address! = ' Shanghai '

The query results are as follows:

Results analysis: after the columns in the T2 are restricted, only the columns T2 satisfy the condition are left connected, and the number of rows in the T1 is not reduced.

Conclusion:

Combined with the test results in 1/2/3: If the constraint is added after on, the constraint is considered when connecting, and if the condition is not met, the data of the bank is not connected. All data as the primary table must be present in the result set, and row data that is not connected by the secondary table must not be present in the result set.

In the last picture to illustrate

　　

Third, back to our original question

 and T1.id > 2

And

where t1.id > 2

　　

What is the result set if and is replaced by where?

The first statement is a test statement in two, (1), and I repeat the result to compare

Then the result of the second sentence runs as follows:

Consider the reason carefully and then look at the conclusion:

If the on after and is replaced by where. The constraints after the where have not been restricted when the T1 and T2 tables are connected, but rather the result set that is associated under t1.id=t2.id conditions in the T1 and T2 tables and then the SELECT * FROM ( connection result set ) where t1.id>2.

　

If there is a mistake, also look correct!

Restrictions on the left and right connections on the database