MySQL Table connection query

Original address: https://www.cnblogs.com/qiuqiuqiu/p/6442791.html

1=  <> 之类的比较运算符）。包括相等联接和自然联接。     内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 students和courses表中学生标识号相同的所有行。       2、外联接。外联接可以是左向外联接、右向外联接或完整外部联接。     在 FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：     1）LEFT  JOIN或LEFT OUTER JOIN     左向外联接的结果集包括  LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。       2）RIGHT  JOIN 或 RIGHT  OUTER  JOIN     右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。       3）FULL  JOIN 或 FULL OUTER JOIN完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。   3、交叉联接   交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。    FROM 子句中的表或视图可通过内联接或完整外部联接按任意顺序指定；但是，用左或右向外联接指定表或视图时，表或视图的顺序很重要。有关使用左或右向外联接排列表的更多信息，请参见使用外联接。     例子：   

1) Internal connection

2) Left Connection

3) Right Connection

4) Fully connected

Cross joins (Cross joins): There are two, explicit and implicit, without an ON clause, which returns the product of two tables, also called the Cartesian product. For example: The following statement1and statements2The result is the same. Statement1: Implicit cross-joins, no crosses join. SELECT o.id, O.order_number, C.id, C.namefrom ORDERS O, CUSTOMERS cwhere o.id=1;Statement2: Explicit cross-joins, using crosses join. SELECT o.id,o.order_number,c.id,c.namefrom ORDERS O cross JOIN CUSTOMERS cwhere o.id=1;Statement1and statements2The result is the same, the query results are as follows: two, inner join (INNER join) INNER JOIN (INNER join): There are two kinds, explicit and implicit, that return data rows in the join table that meet the join criteria and query criteria. (The so-called link table is the database in the form of queries formed in the intermediate table). For example: The following statement3and statements4The result is the same. Statement3: Implicit inner join, without inner join, forms a Cartesian product of two tables. SELECT o.id,o.order_number,c.id,c.namefrom CUSTOMERS c,orders owhere c.id=o.customer_id;Statement4: The inner connection, commonly referred to as an inner connection, has a inner join, and the resulting intermediate table is a Cartesian product with two tables filtered by on condition. SELECT o.id,o.order_number,c.id,c.namefrom CUSTOMERS C INNER JOIN ORDERS O on C.id=o.customer_id;Statement3and statements4Query results for: three, outer joins (OUTER join): The outer join not only returns rows that meet the connection and query criteria, but also returns some rows that do not meet the criteria. The outer joins are divided into three categories: Left OUTER join (OUTER join), right outer join (OUTER join), and full outer join (fully OUTER join). What all three have in common is that they return rows of data that meet the join criteria and query criteria (that is, inner joins). The difference is as follows: the left OUTER join also returns data rows in the left table that do not meet the criteria of the join criteria for the query. The right outer join also returns the data rows in the right table that do not meet the criteria for the join criteria. The full outer join also returns rows of data in the left table that do not meet the criteria for the join criteria, and also returns rows in the right table that do not meet the criteria for a join condition. The full outer join is actually a mathematical collection of upper left outer joins and right outer joins (removing duplicates), i.e. "full=Left outer UNION right outside ". Description: The left table is the table to the left of the "(OUTER JOIN)" keyword. The right table is, of course, on the right. In three types of outer joins, the OUTER keyword can be omitted. The following are examples: statements5: Left outer connection (OUTER join) SELECT o.id,o.order_number,o.customer_id,c.id,c.namefrom ORDERS O OUTER join CUSTOMERS C on C.I. D=o.customer_id;Statement6: Right outer join (OUTER join) SELECT o.id,o.order_number,o.customer_id,c.id,c.namefrom ORDERS O R OUTER JOIN CUSTOMERS C on C . ID=o.customer_id;Note: The result of the where condition is placed on the back of the query is not the same. For example: statement7: Where condition is independent. SELECT o.id,o.order_number,o.customer_id,c.id,c.namefrom ORDERS O left OUTER joins CUSTOMERS C on C.id=O.customer_idwhere O.order_number<>' mike_order001 ';Statement8: the statement7The Where condition in is placed behind on. SELECT o.id,o.order_number,o.customer_id,c.id,c.namefrom ORDERS O left OUTER joins CUSTOMERS C on C.id=O.CUSTOMER_ID and O.order_number<>' mike_order001 ';From the statement7and statements8The result of the query is obviously not the same, the statement8The results shown are difficult to understand. Therefore, it is recommended that when writing a connection query, on is followed only by the join condition, and the conditions for the intermediate table restrictions are written in the WHERE clause. Statement9: All-out connection (full OUTER join). SELECT o.id,o.order_number,o.customer_id,c.id,c.namefrom ORDERS O full OUTER JOIN CUSTOMERS C on C.id=o.customer_id;Note: MySQL does not support all-out connections, and the notation given here is for Oracle and DB2. However, it is possible to obtain the query result of the full outer join through the left outer and right outside to find the collection. Is the result of the above SQL execution under Oracle: statementsTen: Out-of-the-left and right-hand collections, actually query results and statements9is the same. SELECT o.id,o.order_number,o.customer_id,c.id,c.namefrom ORDERS O left OUTER joins CUSTOMERS C on C.id=O.customer_idunionselect o.id,o.order_number,o.customer_id,c.id,c.namefrom ORDERS O right OUTER joins CUSTOMERS C on C.id=o.customer_id;Statement9and statementsTenThe results of the query are the same as: Iv. union join: This is a rare way to connect. Both Oracle and MySQL do not support the purpose of finding all the rows that are different between the full outer and inner connections. This is more commonly used in troubleshooting data analysis. You can also use the collection operations of the database to implement this functionality. Statement One: Union query (Union JOIN) example, no SQL environment was found to execute. SELECT o.id,o.order_number,o.customer_id,c.id,c.namefrom ORDERS O UNION JOIN CUSTOMERS C on C.id=o.customer_id statements A: statement OneThe equivalent realization under the DB2. I don't know if DB2 supports statements OneIt! SELECT o.id,o.order_number,o.customer_id,c.id,c.namefrom ORDERS O full OUTER JOIN CUSTOMERS C on C.id=O.customer_idexceptselect o.id,o.order_number,o.customer_id,c.id,c.namefrom ORDERS O INNER JOIN CUSTOMERS C on C.id=o.customer_id;Statement -: statement OneAn equivalent implementation under Oracle. SELECT o.id,o.order_number,o.customer_id,c.id,c.namefrom ORDERS O full OUTER JOIN CUSTOMERS C on C.id=O.customer_idminusselect o.id,o.order_number,o.customer_id,c.id,c.namefrom ORDERS O INNER JOIN CUSTOMERS C on C.id=o.customer_id;The results of the query are as follows: V. Natural connection (NATURAL INNER join): Honestly, the value of this connection query does not exist, since it is defined in the SQL2 standard, give an example to see it. Natural connections without specifying a connection column, SQL checks two tables for columns of the same name, assuming they are used in the join condition, and contains only one connection column in the join condition. The ON statement is not allowed, the display column is not allowed, the display column can only be used*(Tested in Oracle environment). You can specify natural for each type of connection (except for cross-connections). Here are a few examples. Statement -: SELECT*From ORDERS O NATURAL INNER JOIN CUSTOMERS C;Statement the: SELECT*From ORDERS O NATURAL left OUTER JOIN CUSTOMERS C;Statement -: SELECT*From ORDERS O NATURAL right OUTER JOIN CUSTOMERS C;Statement -: SELECT*From ORDERS O NATURAL full OUTER JOIN CUSTOMERS C;Six, the basic principle of SQL query: Two kinds of situations are introduced. First, single-table query: Filter the records in the table according to where conditions, form an intermediate table (this intermediate table is not visible to the user), and then select the corresponding column according to the Select column of select to return the final result. Second, two-table connection query: The two-table quadrature (Cartesian product) with on conditions and connection type filtering to form an intermediate table; then the records of the intermediate table are filtered based on the Where condition and the query results are returned based on the column specified by select. Third, multi-table connection query: First and second table according to two table connection query, and then use the query results and the third table to make a connection query, and so on, until all the tables are connected, and eventually form an intermediate result table, and then filter the records of the intermediate table according to where conditions, and returns the result of the query based on the column specified by select. The process of understanding SQL query is the theoretical basis for SQL optimization. Seven, on after the condition (on condition) and where condition difference: on condition: is the filter two link table Cartesian product form the intermediate table constraints. Where Condition: In a SELECT statement with an on condition, the constraint that filters the intermediate table. In a single-table query that does not have on, it is a constraint that restricts the return of records to physical tables or intermediate query results. In a two-table or multiple-table connection, a constraint that restricts the return result of the connection to form the final intermediate table. As you can see from here, it is inappropriate to move the where condition into the back. The recommended practice is to connect only, where only the records of the intermediate table are filtered. Viii. Summary Connection query is the core of SQL query, the connection type of connection query is selected according to the actual demand. If you choose improperly, not only can not improve query efficiency, but will bring some logic errors or poor performance. Here is a summary of the two-table connection query selection method based on:1, check two tables related columns equal data in the connection.2, col_l is a subset of col_r with a right outer join.3, Col_r is a subset of col_l with a left outer join.4, Col_r and col_l intersect each other but are not subsets of each other when used in the whole outside.5, differential operation when using a joint query. When querying multiple tables, these different connection types can be written to a piece. For example: SELECT T1. C1,t2. Cx,t3. Cyfrom TAB1 T1 INNER JOIN TAB2 T2 on (T1. C1=T2. C2) INNER JOIN TAB3 T3 on (T1. C1=T2. C3) left OUTER JOIN TAB4 on (T2. C2=T3. C3);WHERE T1. X>T3. Y;The above SQL query is a demonstration of multi-table joins.

MySQL Table connection query