In-depth understanding of four SQL connections-left outer join, right outer join, INNER JOIN, full join _ MySQL

In-depth understanding of four SQL connections-left outer join, right outer join, inner join, and full link bitsCN.com

1. INNER JOIN(Typical join operations use comparison operators such as = or <> ). Including equal join and natural join.
The inner join uses the comparison operator to match rows in two tables based on the values of the columns in each table. For example, retrieve all rows with the same student ID in the students and courses tables.

2. Outer join.Outer join can be left outer join, right outer join, or complete external join.
When an external join is specified in the FROM clause, it can be specified by one of the following sets of keywords:
1) left join or LEFT OUTER JOIN
The result set of the left outer join includes all rows in the LEFT table specified in the left outer clause, not just the rows matched by the join column. If a row in the left table does not match a row in the right table, all selection list columns in the right table in the row of the associated result set are null.
2) right join or RIGHT OUTER JOIN
The right outer join is the reverse join of the left outer join. All rows in the right table are returned. If a row in the right table does not match a row in the left table, a null value is returned for the left table.
3) full join or FULL OUTER JOIN
The complete external join operation returns all rows in the left and right tables. If a row does not match a row in another table, the selection list column of the other table contains a null value. If there are matched rows between tables, the entire result set row contains the data value of the base table.

3. Cross join
Returns all rows in the left Table. Each row in the left table is combined with all rows in the right table. Cross join is also called Cartesian product.
Tables or views in the FROM clause can be specified in any order through internal join or complete external join. However, when you use left or right outer join to specify a table or view, the order of tables or views is very important. For more information about using left or right outer join to arrange tables, see using outer join.

Example:
-------------------------------------------------
Table a id name Table B id job parent_id
1 piece 3 1 23 1
2 Li Si 2 34 2
3 Wang Wu 3 34 4
A. id is related to parent_id
--------------------------------------------------
1) internal connection
Select a. *, B. * from a inner join B on a. id = B. parent_id
The result is
1 piece 3 1 23 1
2 Li Si 2 34 2

2) left join
Select a. *, B. * from a left join B on a. id = B. parent_id
The result is
1 piece 3 1 23 1
2 Li Si 2 34 2
3 Wang Wu null

3) Right join
Select a. *, B. * from a right join B on a. id = B. parent_id
The result is
1 piece 3 1 23 1
2 Li Si 2 34 2
Null 3 34 4

4) full connection
Select a. *, B. * from a full join B on a. id = B. parent_id
The result is
1 piece 3 1 23 1
2 Li Si 2 34 2
Null 3 34 4
3 Wang Wu null
Bytes --------------------------------------------------------------------------------------------I. CROSS JOIN)
Cross join: There are two types: explicit and implicit, without the ON clause. the returned result is the product of the two tables, also called Cartesian product.
For example, the following statements 1 and 2 have the same results.

Statement 1: implicit cross join, without cross join.
Select o. ID, O. ORDER_NUMBER, C. ID, C. NAME
From orders o, CUSTOMERS C
Where o. ID = 1;

Statement 2: an explicit cross join with cross join.
Select o. ID, O. ORDER_NUMBER, C. ID,
C. NAME
FROM ORDERS O CROSS JOIN CUSTOMERS C
Where o. ID = 1;
Statement 1 and statement 2 have the same results. The query results are as follows:

2. INNER JOIN)
Inner join: There are two types of explicit and implicit data rows that meet the connection conditions and query conditions in the connection table. (The so-called chain table is the intermediate table formed by the database for query ).
For example, the following statements 3 and 4 have the same results.

Statement 3: implicit inner join without inner join. the intermediate table is the Cartesian product of the two tables.
Select o. ID, O. ORDER_NUMBER, C. ID, C. NAME
From mers c, ORDERS O
Where c. ID = O. CUSTOMER_ID;

Statement 4: the displayed inner join is generally called an inner join. the intermediate table is the Cartesian product of the two tables filtered by the ON condition.
Select o. ID, O. ORDER_NUMBER, C. ID, C. NAME
From mers c inner join orders o on c. ID = O. CUSTOMER_ID;
Query Results of statements 3 and 4:

3. outer join ):The external connection not only returns data rows that meet the connection and query conditions, but also some rows that do not meet the conditions. Outer join is divided into three types: left outer join, right outer join, and full outer join ).
All three return data rows that meet the connection conditions and query conditions (that is, internal connections. The differences are as follows:
The left outer join also returns the rows in the left table that do not meet the query conditions.
The right outer join also returns the data rows in the right table that do not meet the query conditions.
The outer join operation also returns the rows in the left table that do not meet the query conditions and the rows in the right table that do not meet the query conditions. The total outer join is actually a mathematical set of the upper left outer join and the right outer join (removing duplicates), that is, "Total outer = left outer UNION right outer ".
Note: The LEFT table is the table on the LEFT of the "(left outer join)" keyword. The right table is of course the right table. Among the three types of external connections, the OUTER keyword can be omitted.

The following is an example:
Statement 5: LEFT OUTER JOIN)
Select o. ID, O. ORDER_NUMBER, O. CUSTOMER_ID, C. ID, C. NAME
From orders o left outer join mers c on c. ID = O. CUSTOMER_ID;

Statement 6: RIGHT OUTER JOIN)
Select o. ID, O. ORDER_NUMBER, O. CUSTOMER_ID, C. ID, C. NAME
From orders o right outer join mers c on c. ID = O. CUSTOMER_ID;
Note: the query results after the WHERE condition is placed ON are different. For example:

Statement 7: The WHERE condition is independent.
Select o. ID, O. ORDER_NUMBER, O. CUSTOMER_ID, C. ID, C. NAME
From orders o left outer join mers c on c. ID = O. CUSTOMER_ID
Where o. ORDER_NUMBER <> 'Mike _ order001 ';

Statement 8: place the WHERE condition in Statement 7 after the ON clause.
Select o. ID, O. ORDER_NUMBER, O. CUSTOMER_ID, C. ID, C. NAME
From orders o left outer join mers c on c. ID = O. CUSTOMER_ID and o. ORDER_NUMBER <> 'Mike _ order001 ';

The query results of statement 7 and statement 8 are obviously different, and the results displayed in statement 8 are hard to understand. Therefore, when writing a join query, we recommend that you only follow the join condition after ON, and write the conditions for the central table to the WHERE clause.

Statement 9: FULL OUTER JOIN ).
Select o. ID, O. ORDER_NUMBER, O. CUSTOMER_ID, C. ID, C. NAME
From orders o full outer join mers c on c. ID = O. CUSTOMER_ID;
Note: MySQL does not support all external connections. the method provided here is suitable for Oracle and DB2. However, you can obtain the query results of the all outer connections through the left outer and right outer query sets. Is the result of the preceding SQL statement execution in Oracle:

Statement 10: The Union of Left outer and right outer. In fact, the query result is the same as that of statement 9.
Select o. ID, O. ORDER_NUMBER, O. CUSTOMER_ID, C. ID, C. NAME
From orders o left outer join mers c on c. ID = O. CUSTOMER_ID
UNION
Select o. ID, O. ORDER_NUMBER, O. CUSTOMER_ID, C. ID, C. NAME
From orders o right outer join mers c on c. ID = O. CUSTOMER_ID;
The query results for statement 9 and 10 are the same, as shown below:

4. union join ):This is a rare connection method. Oracle and MySQL do not support this function. the function is to find all rows with the difference between the full outer connection and the internal connection. This is often used in data analysis troubleshooting. You can also use database collection operations to achieve this function.
Statement 11: for example, union join, the SQL environment that can be executed is not found.
Select o. ID, O. ORDER_NUMBER, O. CUSTOMER_ID, C. ID, C. NAME
From orders o union join customers c on c. ID = O. CUSTOMER_ID

Statement 12: equivalent implementation of statement 11 in DB2. I still don't know if DB2 supports statement 11!
Select o. ID, O. ORDER_NUMBER, O. CUSTOMER_ID, C. ID, C. NAME
From orders o full outer join mers c on c. ID = O. CUSTOMER_ID
EXCEPT
Select o. ID, O. ORDER_NUMBER, O. CUSTOMER_ID, C. ID, C. NAME
From orders o inner join mers c on c. ID = O. CUSTOMER_ID;

Statement 13: equivalent implementation of statement 11 in Oracle.
Select o. ID, O. ORDER_NUMBER, O. CUSTOMER_ID, C. ID, C. NAME
From orders o full outer join mers c on c. ID = O. CUSTOMER_ID
MINUS
Select o. ID, O. ORDER_NUMBER, O. CUSTOMER_ID, C. ID, C. NAME
From orders o inner join mers c on c. ID = O. CUSTOMER_ID;
The query result is as follows:

5. natural inner join ):To be honest, this connection query has no value. since it is defined in the SQL2 standard, let's look at an example. You do not need to specify the connection column for the natural connection. SQL checks whether two columns with the same name are used in the connection condition and only contains one connection column in the connection condition. The ON statement is not allowed. you cannot specify a display column. The Display Column can only be represented by * (tested in the ORACLE environment ). NATURAL can be specified for each connection type (except for cross-join. The following are several examples.
Statement 14:
SELECT *
From orders o natural inner join mers c;

Statement 15:
SELECT *
From orders o natural left outer join mers c;

Statement 16:
SELECT *
From orders o natural right outer join mers c;

Statement 17:
SELECT *
From orders o natural full outer join mers c;

6. basic principles of SQL query: Two cases are introduced.
First,Single table query: filters records in a table based on the WHERE condition to form an intermediate table (this intermediate table is invisible to users ); then SELECT the corresponding column based on the SELECT column to return the final result.

Second,Two-table join query: the product (Cartesian product) of the two tables is filtered using the ON condition and connection type to form an intermediate table. then, the records of the intermediate table are filtered based ON the WHERE condition, return the query result based on the column specified by SELECT.

Third,Multi-table join query: queries the first and second tables based on the two-table join, and then performs join query using the query results and the third table, and so on, until all the tables are connected, an intermediate result table is formed. then, records of the intermediate table are filtered based on the WHERE condition, and query results are returned based on the columns specified by SELECT.
Understanding the SQL query process is the theoretical basis for SQL optimization.

7. differences between the ON condition and the WHERE condition after the ON condition:
ON condition: it is the constraint condition for filtering two chain tables to form an intermediate table by Cartesian product.
WHERE condition: SELECT statements with ON conditions are constraints for filtering the intermediate table. In a single table query without an ON clause, it is a restriction ON the return records of physical tables or intermediate query results. In a two-table or multi-table join, the join is restricted to the final result returned from the intermediate table.
It can be seen from this that it is inappropriate to move the WHERE condition to the end of the ON clause. The recommended practice is:
ON only performs join operations, WHERE only filters records in the intermediate table.

VIII. Summary
Connection query is the core of SQL query. the connection type of connection query depends on actual needs. Improper selection will not improve the query efficiency, but will lead to some logical errors or low performance. The following describes the basis for the two-table connection query selection method:
1. query the data with the same association columns in the two tables for internal join.
2. when Col_L is a child of Col_R, use outer right join.
3. Col_R is a child set of Col_L connected to the left outer.
4. Col_R and Col_L have an intersection with each other, but they are not a subset of each other.
5. perform the union query when performing the difference operation.
When querying multiple tables, these different connection types can be written into one piece. For example:
SELECT T1.C1, T2.CX, T3.CY
FROM 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 preceding SQL query is a demonstration of multi-table join.

BitsCN.com