MySQL federated query syntax inline, leftist, right-join, full-join

MySQL federated queries are more efficient, with the following examples to illustrate the benefits of federated queries (inline, leftist, right-click, full-Union):

T1 table structure (user ID, user name, password)
UserID Username Password
1 Jack Jackpwd
2 Owen Owenpwd


T2 table structure (user ID, user score, User level)
UserID (int) Jifen Dengji
1 20 3
3 50 6

First: inline ( inner join )
If you want to list the user information, points, ratings, then generally write:
Select * from T1, T3 where t1.userid = T3.userid

(In fact, this result is equivalent to Select * from T1 inner join T3 on T1.userid= T3.userid）。

The line that has the UserID in all two tables is put into one line (that is, inline), but the latter is much more efficient than the former, it is recommended to use the latter (inline) notation.

SQL statements:
Select * from T1 inner join T2 on T1.userid =< /c15> T2.userid

Run results
T1.userid username password T2.userid Jifen Dengji
1 Jack Jackpwd 1 20 3


Second: Leftist (Left join ）
Displays all rows in the left table T1, and adds the eligible conditions in the right table T2 to the left table T1;
The right table T2 does not match the criteria, and it is not added to the result table, and Null is represented.

SQL statements:
Select * from T1 left 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-side ( right join  ). The
displays all the rows in the right table T2 and adds the T1 in the left table to the right table T2, and the
left table T1 does not fit in the result table, and Null is represented.

SQL statement:
 select * from T1 right join T2 on t1.userid = T2.userid  

Run result   
T1.userid   username   password   t2.userid   Jifen   Dengji   
1   Jack   jackpwd   1     3   
null   NULL   NU LL   3     6   

Fourth: Full Union ( full join  )
shows all the rows in the left table T1, the right table T2, the leftist result table + right-side result table, and then filters out the duplicates.

SQL statement:
 select * from T1 full join T2 on t1.userid = T2.userid  
 
Run result   
T1.userid   username   password   t2.userid   Jifen   Dengji   
1   Jack   jackpwd   1     3   
2   Owen   ; OWENPWD   NULL   null   NULL   
NULL   null   NULL   3     6   ;  

concludes that, with regard to joint queries, the efficiency is indeed high, and the 4 kinds of syndication can be used flexibly, and the complex statement structure will be simpler.

MySQL federated query syntax inline, leftist, right-join, full-join