Example to explain MySQL Federated query

SQL inline Syntax basic syntax (INNER join)

SELECT * FROM table 1 inner JOIN table 2 on table 1.userid= table 2.userid

SQL Inline Syntax Description: The above statement is the query Table 1 and table 22 tables, as long as the UserID field in two tables in the same line to display.

Note: The above syntax is equivalent to select * FROM table 1, table 2 where table 1.userid= table 2.userid, and using SQL inline, will improve a lot of efficiency.

SQL Leftist syntax basic syntax (left OUTER join)

SELECT * FROM table 1 left OUTER JOIN table 2 on table 1.userid= table 2.userid

SQL Leftist syntax Description: Displays all the rows in table 1 and displays all eligible data in table 2 to the result set, if none of the data in table 2 does not appear in the result set, and is displayed as null.

SQL Right syntax base syntax (outer join)

SELECT * FROM table 1 right OUTER JOIN table 2 on table 1.userid= table 2.userid

SQL Leftist syntax Description: Displays all the rows in table 2 and displays all eligible data in table 1 to the result set, if none of the data in the left table 1 does not appear in the result set, and is displayed as null.

SQL Total Syntax basic syntax (full outer join)

SELECT * FROM table 1 full OUTER JOIN table 2 on table 1.userid= table 2.userid

SQL Leftist syntax Description: The above SQL full-link meaning is to display table 1 and table 2 all the rows, to put it simply, is the table 1 and table 2 all the rows together, then filter out duplicate data and display in the result set.

Okay, let me see. Performance comparison inline, leftist, right, full-union

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.
Displays all the rows in the right table T2, www.111cn.net the criteria in the left table T1 to the right table T2, and does not qualify in the left table T1 without adding to the result table and null representation.
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

The use of Union

code is as follows

copy code

Select * FROM (SELECT ' id ', ' subject ' from ' article ' WHERE ' active ' = ' 1 ' and ' subject ' like ') to resize picture% ' order by ' Add_time ' DE SC) As T1 Union ALL Select * to (SELECT ' id ', ' class_name ' as ' subject ' from ' Web_class ' wher E ' active ' = ' 1 ' and ' class_name ' like '% resize picture% ' order by ' class_id ' DESC) As T2 Union Select * from (SELECT ' id ', ' subject ' from ' article ' WHERE ' active ' = ' 1 ' and (' subject ') '% ' OR ' subject ' like '% picture Www.111cn.N Et% ') Order by ' Add_time ' DESC as T3;

The Union all and Union are the main uses of the combined query for the above SQL statements, and the difference is that union all enumerates the results of all eligible queries, and Union will filter out the duplicate results by doing all the query results that match the criteria.

The explanation for the above SQL statements is that the article table and the Web_class table are divided into two different tables, so there is no need to eliminate duplicate results. However, the third branch of the joint query of the SQL query is a combination of the query statement, and the result of this SQL statement query is sure to contain the first branch of the query results of the SQL statement, here is not necessary, so did not use all to remove duplicate query results.

The last thing to say is that after the MySQL5.0, the order of operations has been paid attention to, so the query for multiple tables may be incorrectly linked to the subquery. For example, you need to perform multiple table joins, so you enter the following join query:

The code is as follows	Copy Code
SELECT t1.id,t2.id,t3.id From T1,t2 Left JOIN T3 on (t3.id=t1.id) WHERE t1.id=t2.id;

But MySQL does not do this, and the real way the backend is executed is the following statement:

The code is as follows	Copy Code
SELECT t1.id,t2.id,t3.id from T1, (T2 left JOIN T3 on (t3.id=t1.id)) WHERE t1.id=t2.id;

That's not the effect we want, so we need to type this:

The code is as follows	Copy Code
SELECT t1.id,t2.id,t3.id From (T1,T2) Left JOIN T3 on (t3.id=t1.id) WHERE t1.id=t2.id;

Parentheses are quite important here, so in the future, when writing such queries, we should not forget to write more parentheses, at least to avoid many errors (because such errors are difficult to be discovered by developers).