Deep understanding of SQL four kinds of connections-left outer, right outer, inner, full connected _mysql

1. INNER JOIN(A typical join operation that uses a comparison operator such as = or <>). Includes equal joins and natural joins.
Inner joins use comparison operators to match rows in two tables based on the values of the columns that are common to each table. For example, retrieve all lines that are the same as the student identification number for the students and courses tables.

2, outer joint. An outer join can be a left outer join, a right outer join, or a full outer join.
When you specify an outer join in the FROM clause, you can specify one of the following groups of keywords:
1) Left JOIN or left OUTER join
The result set of a left outer join consists of all the rows of the left table specified in the outer clause, not just the rows that the join columns match. If a row in the left table does not have a matching row in the right table, all picklist columns in the right table in the associated result set row are null values.
2 Right Join or right OUTER join
A right outer join is a reverse join of a left outer join. All rows from the right table will be returned. If a row in the right table does not have a matching row in the left table, a null value is returned for left table.
3 full Join or full OUTER join
A full outer join returns all rows in the left and right tables. When a row does not match rows in another table, the select list column for the other table contains null values. If there is a matching row between the tables, the entire result set row contains the data values for the base table.

3. Cross Join
A cross join returns all the rows in the left table, with each row in the left table combined with all the rows in the right table. Cross joins are also called Cartesian product.
A table or view in the FROM clause can be specified in any order by an inner join or a full outer join, but the order of the table or view is important when the table or view is specified with a left or right outer join. For more information about using a left or right outer join to arrange tables, see Using outer joins.

Example:
-------------------------------------------------
A table ID name B table ID job parent_id
1 Sheets 3 1 23 1
2 Lee 42 34 2
3 Wang Wu 3 34 4
The relationship between a.ID and parent_id
--------------------------------------------------
1) Inner connection
Select a.*,b.* from a inner join B on a.id=b.parent_id
The result is
1 Sheets 3 1 23 1
2 Lee 42 34 2

2) Left connection
Select a.*,b.* from a LEFT join B on a.id=b.parent_id
The result is
1 Sheets 3 1 23 1
2 Lee 42 34 2
3 Wang Wu Null

3) Right connection
Select a.*,b.* from right join B on a.id=b.parent_id
The result is
1 Sheets 3 1 23 1
2 Lee 42 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 Sheets 3 1 23 1
2 Lee 42 34 2
Null 3 34 4
3 Wang Wu Null
-------------------------------------------------------------------------------------------- One, cross connection (CROSS join)
Cross-Joins (CROSS join): There are two kinds, explicit and implicit, without an ON clause, which returns the product of two tables, also called Cartesian product.
For example, the following statement 1 and statement 2 results are the same.

Statement 1: An implicit cross connection with no 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 connection using the cross join.
SELECT O.id,o.order_number,c.id,
C.name
From ORDERS O CROSS JOIN CUSTOMERS C
WHERE o.id=1;
The results of statement 1 and statement 2 are the same, and the query results are as follows:

Two, the inner connection (INNER join)
Inner joins (INNER join): There are two kinds, explicit and implicit, that return rows of data in a join table that match the conditions of the join and the query. (The so-called link table is the database in the middle of the query form).
For example, the following statement 3 and statement 4 results are the same.

Statement 3: An implicit inner join with no inner JOIN, forming an intermediate table of two table Cartesian product.
SELECT O.id,o.order_number,c.id,c.name
From CUSTOMERS c,orders O
WHERE c.id=o.customer_id;

Statement 4: The display of the inner joins, commonly referred to as inner joins, have inner joins, forming an intermediate table for two tables filtered by the on condition of the Cartesian product.
SELECT O.id,o.order_number,c.id,c.name
From CUSTOMERS C INNER JOIN ORDERS O on c.id=o.customer_id;
Query results for statement 3 and statement 4:

outer joins (OUTER join): the outer join not only returns rows that match 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 joins (right OUTER join), right-hand outer joins (OUTER join), and full OUTER joins.
The common denominator for all three is to return a row of data that conforms to the join condition and query criteria (that is, an inner join). The different points are as follows:
The left OUTER join also returns rows of data in the left table that do not conform to the query criteria for the join condition list.
The right outer join also returns rows of data in the right table that do not conform to the criteria for the join condition.
A full outer join also returns rows of data in the left table that do not conform to the criteria for the join condition, and also returns rows of data in the right table that do not conform to the criteria for the query. All outer joins are actually the mathematical collection of the upper left outer join and the right outer join (remove repetition), namely "all outside = left outer UNION right outside".
Description: The left table is the table on the left side of the OUTER JOIN keyword. The right table is of course the right one. In three types of outer joins, the OUTER keyword is omitted.

The following examples illustrate:
Statement 5: Left OUTER join (OUTER join)
SELECT O.id,o.order_number,o.customer_id,c.id,c.name
From ORDERS O ' OUTER JOIN CUSTOMERS C on c.id=o.customer_id;

Statement 6: Right outer join (OUTER join)
SELECT O.id,o.order_number,o.customer_id,c.id,c.name
From the ORDERS O right OUTER JOIN CUSTOMERS C on c.id=o.customer_id;
Note: The result of the where condition is placed on the query is not the same. For example:

Statement 7:where conditions are independent.
SELECT O.id,o.order_number,o.customer_id,c.id,c.name
From ORDERS O ' OUTER JOIN CUSTOMERS C on c.id=o.customer_id
WHERE o.order_number<> ' mike_order001 ';

Statement 8: Puts the WHERE condition in statement 7 on the back.
SELECT O.id,o.order_number,o.customer_id,c.id,c.name
From the ORDERS O ' OUTER JOIN CUSTOMERS C on c.id=o.customer_id and o.order_number<> ' mike_order001 ';

The results of the query from statement 7 and statement 8 are clearly different, and the result of statement 8 is incomprehensible. Therefore, it is recommended that when writing a connection query, on the following is only a join condition, while the conditions for the intermediate table restrictions are written to the WHERE clause.

Statement 9: All outer joins (full OUTER join).
SELECT o.id,o.order_number,o.customer_id,c.id,c.name
from to ORDERS O full OUTER JOIN CUSTOMERS C on C.id=o . customer_id;
Note: MySQL does not support all external connections, and this is written for Oracle and DB2. However, you can obtain the results of the full outer join query through the left and right outer collection. The following figure is the result of the above SQL execution under Oracle:

Statement 10: A collection of left and right outside, in which the query result and statement 9 are the same.
SELECT o.id,o.order_number,o.customer_id,c.id,c.name
from ORDERS O-left OUTER JOIN CUSTOMERS 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 CUSTOMERS C on c.id=o.customer_id; The query results for
Statement 9 and statement 10 are the same, as follows:

Four, Federated joins (union join): This is a rare way to connect. Oracle, MySQL is not supported, the role is to find all the differences between the outer and inner joins all the rows. This is more commonly used in the data analysis of errors. You can also use the collection operations of the database to implement this functionality.
Statement 11: A federated Query (union JOIN) example, no SQL environment can be found to execute.
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 the equivalent implementation of

statement 12: statement 11 under DB2. Do not know whether DB2 support statement 11 it!
SELECT o.id,o.order_number,o.customer_id,c.id,c.name
from to ORDERS O full OUTER JOIN CUSTOMERS 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 CUSTOMERS C On c.id=o.customer_id;

Statement 13: Statement 11 is an equivalent implementation under Oracle.
SELECT o.id,o.order_number,o.customer_id,c.id,c.name
from to ORDERS O full OUTER JOIN CUSTOMERS 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 CUSTOMERS C On c.id=o.customer_id; The
query results are as follows:

v. Natural connection (NATURAL INNER join): Seriously, this connection query does not exist value, since it is defined in the SQL2 standard, give an example to see. Natural connections without specifying a connection column, SQL checks for columns of the same name in two tables, assuming they are used in a join condition, and contains only one connection column in the join condition. The ON statement is not allowed, the display column is not allowed, and the display column can only be represented by * (tested in Oracle environments). Natural can be specified for each type of connection (except for cross joins). A few examples are given below.
Statement 14:
SELECT *
From ORDERS O NATURAL INNER JOIN CUSTOMERS C;

Statement 15:
SELECT *
From the ORDERS O NATURAL left OUTER JOIN CUSTOMERS C;

Statement 16:
SELECT *
From the ORDERS O NATURAL right OUTER JOIN CUSTOMERS C;

Statement 17:
SELECT *
From the ORDERS O NATURAL full OUTER JOIN CUSTOMERS C;

Six, the basic principle of SQL query: Two kinds of situation introduction.
first, single table query: According to the Where conditions filter the records in the table, forming an intermediate table (this intermediate table is not visible to the user); then select the column to return the final result based on the Select column of the SELECT.

Second, Two-table connection query: The two-table quadrature (Cartesian product) and the on condition and connection type filtering to form an intermediate table, and then filter the records of the intermediate table according to the Where condition, and return the query result according to the column specified by select.

Third, multiple table connection query: the first and second tables are queried by a two-table connection, then the query results are connected to the third table, and so on, until all the tables are connected, eventually forming an intermediate result table, and then filtering the records of the intermediate table according to the Where condition. The query results are returned based on the columns specified by the SELECT.
The process of understanding SQL queries is the theoretical basis for SQL optimization.

Seven, on the following condition (on condition) and Where condition difference:
On condition: is to filter two linked table Cartesian product to form the constraint condition of the intermediate table.
Where Condition: A constraint that filters an intermediate table in a SELECT statement that has an on condition. In a single table query without on, it is a constraint that restricts the return of records to physical tables or intermediate query results. In two or more table joins, a constraint that restricts the return of a connection to the resulting intermediate table.
It can be seen from here that it is not appropriate to move the where condition back into on. The recommended practices are:
On is only a connection operation where only the records of the intermediate tables are filtered.

The

VIII, summary
Connection query is the core of the SQL query, and the connection type of the connection query is selected based on actual requirements. If improper choice, not only can not improve query efficiency, but will bring some logic error or low performance. The following summarizes the two-table connection query selection method based on:
1, check the two Table association columns equal data with the internal 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 left outer joins. The
4, Col_r, and col_l intersect with each other but are not subsets of each other.
5, when the difference operation with the joint query.
When multiple table queries are available, these different connection types can be written to one piece. For example, the
SELECT T1. C1,t2. Cx,t3.cy
from TAB1 T1
       INNER JOIN TAB2 on (T2)
T1.c1=t2.c2 nbsp;    INNER Join TAB3 T3 on (t1.c1=t2.c3)
       left OUTER JOIN TAB4 On (T2. C2=t3. C3);
WHERE T1. X >t3. Y
This SQL query above is a demonstration of multiple table joins.