Federated queries in MySQL (inline, leftist, outer, right, full)

Joint queries are more efficient, with examples to illustrate Federated queries: The benefits and usage of inline inner join, leftist left outer join, right-outer JOIN, full-union, fully outer join.

The efficiency of joint queries is high, and the following examples illustrate the benefits of federated queries (inline, leftist, right-click, full-Union):

T1 table Structure (user name, password)	Userid (int)	Username varchar (20)	Password varchar (20)
	1	Jack	Jackpwd
	2	Owen	Owenpwd
T2 table Structure (user name, password)	Userid (int)	Jifen varchar (20)	Dengji varchar (20)
	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, the result is identical to the SELECT * from T1 inner join T3 on T 1.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 statement: 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 matching conditions in the right table T2 are added to the left table T1, and the right table T2 does not fit in the result table, and the null representation is not included.

SQL statement: 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 fit in the result table, and Null is represented.

SQL statement: 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 statement: 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. These 4 ways are: 1) Inner join 2) LEFT OUTER join 3) Right outer join 4) Full outer JOIN

Federated queries in MySQL (inline, leftist, outer, right, full)