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 name, password)
UserID (int) Usernamevarchar (Passwordvarchar) (20)
1JackJackpwd
2OwenOwenpwd


T2 table structure (user name, password)
UserID (int) Jifenvarchar (Dengjivarchar) (20)
1203
3506


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 equates to the 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 = T2.userid

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



Second: leftist (left Outer join)
The right table T2 is not eligible, it is not included in the result table, and Null is represented.

sql statement:
select * from T1 left outer join T2 On t1.userid = T2.userid

running results   
t1.userid   username   password   t2.userid   Jifen   DENGJ I   
1   Jack   jackpwd   1     3    2   Owen   OWENPWD   NULL   null   NULL   

Third: Right-outer JOIN.
display all the rows in the right table T2, and add the matching conditions in the left table T1 to the right table T2;
The left table T1 is not eligible, and is not added to the result table, and Null is represented.

SQL statements:
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

IV: FULL OUTER JOIN
Display the left table T1, the right table T2 all the rows in both sides, that is, the leftist result table + right-side result table is combined, and then filter out the duplicate.

SQL statements:
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, about the joint query, the efficiency is indeed relatively high, 4 kinds of joint methods if flexible to use, basically complex sentence structure will be simple.

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