Detailed explanation of multi-table query in mysql
- 迷茫Original
- 2017-03-26 11:28:281234browse
Multiple table query
1. Category:
* Merge result set (understand)
* Join query
* Subquery
Merge result set
* In the table that is required to be merged, The type and number of columns are the same
* UNION, remove duplicate rows
## * UNION ALL, do not remove duplicate rows
SELECT * FROM cd/*cd表*/UNION ALLSELECT * FROM ab;/*ab表*
Connection Query
1. Category
* Inner Join
* Outer Join
> Left Outer Join
> Right Outer join
> Full outer join (not supported by MySQL)
* Natural join (a simplified method)
2. Inner join
* Dialect: SELECT * FROM Table 1 Alias 1, Table 2 Alias 2 WHERE Alias 1.xx=Alias 2.xx
* Standard: SELECT * FROM Table 1 Alias 1 INNER JOIN Table 2 Alias 2 ON Alias 1.xx=Alias 2.xx
* Natural: SELECT * FROM Table 1 Alias 1 NATURAL JOIN Table 2 Alias 2
* All records queried by the inner join meet the conditions.
3. Outer join
*Left outer connection： SELECT * FROM Table 1 Alias 1 LEFT OUTER JOIN Table 2 Alias 2 ON Alias 1.xx=Alias 2.xx
> The records in the left table will be queried regardless of whether they meet the conditions, while the records in the right table can only be retrieved if they meet the conditions. Records in the left table that do not meet the conditions will be NULL in the right table part * Left external natural
: SELECT * FROM Table 1 Alias 1 NATURAL LEFT OUTER JOIN Table 2 Alias 2 ON Alias 1.xx=Alias 2.xx * Right outer
：SELECT * FROM Table 1 Alias 1 RIGHT OUTER JOIN Table 2 Alias 2 ON Alias 1.xx=Alias 2. xx >
The records in the right table will be queried regardless of whether they meet the conditions, while the records in the left table can only be retrieved if they meet the conditions. For records in the right table that do not meet the conditions, their left table parts are all NULL * Right outer natural
: SELECT * FROM Table 1 Alias 1 NATURAL RIGHT OUTER JOIN Table 2 Alias 2 ON Alias 1.xx=Alias 2.xx * Full link: You can use
UNION to complete the full link
subquery ：There is a query in the query (see the select keyword Number! )
1. Appearance position:
* where exists as a condition ## * Exists as a table after from (multiple rows and multiple columns)
2. Conditions
* (***) Single row and single column: SELECT * FROM table 1 alias 1 WHERE column 1 [=, >, 95ec6993dc754240360e28e0de8de30a=, <=, !=]
(SELECT column FROM table 2 alias 2 WHERE condition) * (**)Multiple rows and single column: SELECT * FROM table 1 alias 1 WHERE column 1
[IN, ALL, ANY] (SELECT column FROM table 2 alias 2 WHERE condition) * (*) Single row and multiple columns: SELECT * FROM table 1 alias 1 WHERE (column 1, column 2) IN
(SELECT column 1, column 2 FROM table 2 alias 2 WHERE condition) * (***)Multiple rows and multiple columns: SELECT * FROM table 1 alias 1, (SELECT ....)
Alias 2 WHERE condition
The above is the detailed content of Detailed explanation of multi-table query in mysql. For more information, please follow other related articles on the PHP Chinese website!