Article Title multi-Table query: merged result set, connection query, subquery, and article title merge

Article Title multi-Table query: merged result set, connection query, subquery, and article title merge

Multi-Table query
Category:
* Merge result sets
* Connection Query
* Subquery

[1] merge result sets

* The number of columns in the table to be merged must be the same. The column types must be the same. * union all must be removed for duplicate rows. Duplicate row examples are not removed: select * from table1 union select * from table2; select * from table1 union all select * from table2;

[2] connection Query

CATEGORY * inner connection * outer connection> left outer connection> right outer connection> all outer connections (not supported by MySQL) * natural connections (a simplified method) 1. inner join * Dialect: SELECT * FROM table 1 alias 1, Table 2 alias 2 WHERE alias 1.xx= alias 2.xx( MySQL can) * standard: SELECT * FROM table 1 alias 1 inner join Table 2 alias 2 ON Alias 1.xx= alias 2.xx * nature: SELECT * FROM table 1 alias 1 natural join Table 2 alias 2 is automatically matched using the same columns of the two tables, the same column only shows one column * All records connected to the query in the same column meet the conditions. Note: The condition after where is called the join condition. Cartesian product must be removed for multi-table queries, and association conditions must be removed. outer join * outer left: SELECT * FROM table 1 alias 1 left outer join Table 2 alias 2 ON Alias 1.xx= alias 2.xx// all records of coordinates are displayed, if no corresponding right table exists, the right table is supplemented with null * left outer natural: SELECT * FROM table 1 alias 1 natural left outer join Table 2 alias 2; * Right OUTER: SELECT * FROM table 1 alias 1 right outer join Table 2 alias 2 ON Alias 1.xx= alias 2.xx * right outer nature: SELECT * FROM table 1 alias 1 natural right outer join Table 2 alias 2; * Full link: UNION can be used to complete the full link: left outer union right outer Note: outer link: if the condition is not met, it will also be queried. Left outer right outer uses null complement bit, right outer left uses null complement bit

Subquery

1. location: * The where clause exists as a condition * The from clause exists as a table (generally multiple rows and multiple columns). 2. condition ***** single row Single Column: SELECT * FROM table 1 alias 1 WHERE column 1 [=,>, <,> =, <= ,! =] (SELECT column FROM table 2 alias 2 WHERE condition) *** multiple rows Single Column: SELECT * FROM table 1 alias 1 WHERE column 1 [IN, ALL, ANY] (SELECT column FROM table 2 alias 2 WHERE condition) SELECT * FROM table name WHERE column> ANY (SELECT ....) 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) // The columns corresponding to the where and sub-tables are the same. *** multiple rows and multiple columns: SELECT * FROM table 1 alias 1, (SELECT ....) alias 2 WHERE Condition

Note: For multi-table queries, you need to find association conditions. on is followed by association conditions.

Multi-Table query small exercise