MySQL leftist where multiple table conditions SQL statements

SQL statement

The code is as follows	Copy Code
SELECT * FROM _test a LEFT join _test B on a.id=b.id where a.level= ' and a.month= ' and b.level= ' 03 ' ; Select a.*,b.* from (SELECT * to _test where level= ' and month= ') as a LEFT join (SELECT * from _test where level= ' ' and month= ') as B on a.id=b.id;

The results of these two statements are different and should be left join when the condition is that multiple tables MySQL automatically turns inline, the second statement achieves the correct purpose (in order to find the difference of two data).

The following examples illustrate the benefits of federated queries (inline, leftist, right, and full):
T1 table structure (username, password) userid (int) Usernamevarchar Passwordvarchar (20)
1, Jack Jackpwd.
2 Owen Owenpwd

T2 table structure (username, password) userid (int) Jifenvarchar Dengjivarchar (20)
1 20 3
3 50 6
First: inline (inner join).
If you want to list the user information, points, and ratings, you will typically write this: select * from T1, T3 where T1.userid = T3.userid (in fact, the result is equivalent to select * from T1 inner join T3 on T 1.userid=t3.userid).
It is possible to UserID rows of two tables into one line (inline), but the latter is much more efficient than the former, suggesting the latter (inline) notation.
SQL statement:

The code is as follows	Copy Code
SELECT * FROM T1 inner join T2 on T1.userid=t2.userid

Run results t1.userid username password T2.userid Jifen Dengji
1 Jack Jackpwd 1 20 3
Second: Leftist (left outer join).
All rows in the left table T1 are displayed, and the T2 in the right table is added to the left table T1, and the right table T2 does not conform to the criteria and is not included in the result table, and Null is represented.
SQL statement:

The code is as follows	Copy Code
SELECT * FROM T1 LEFT outer join T2 on T1.userid=t2.userid

Run results t1.userid username password T2.userid Jifen Dengji
1 Jack Jackpwd 1 20 3
2 Owen OWENPWD null NULL NULL
Third: Right outer join.
All rows in the right table T2 are displayed, and the T1 in the left table is added to the right table T2, and the left table T1 does not conform to the criteria and is not included in the result table, and Null is represented.

SQL statement:

The code is as follows	Copy Code
SELECT * FROM T1 right outer join T2 on T1.userid=t2.userid

Run results t1.userid username password T2.userid Jifen Dengji

1 Jack Jackpwd 1 20 3
NULL NULL NULL 3 50 6
Four: Total (full outer join).
Displays all the rows on both sides of the left table T1, right table T2, which combines the leftist result table + The right-hand result table, and then filters out the duplicates.
SQL statement:

The code is as follows	Copy Code
SELECT * FROM T1 full outer join T2 on T1.userid=t2.userid

Run results    t1.userid   username   password   t2.userid   Jifen    dengji  
    1   jack   jackpwd   1    20   3  
    2   owen   owenpwd   NULL    null   null  
    null   null   null    3   50   6  
Summary, regarding the joint query, the efficiency is quite high, 4 kinds of joint method if can use flexibly, basically complex sentence structure also can be simple. These 4 methods are: 1 Inner join  2) left outer join  3) right outer join  4) full outer join