Deep understanding of SQL four connections-left outer connection, right outer connection, inner connection, full connection

deep understanding of SQL four connections-left outer connection, right outer connection, inner connection, full connection2016-06-06 11:27 263 people read Comments (0) favorite reports Classification:Database (6)

1. Inner Connection(A typical join operation, using a comparison operator like = or <>). Includes equality 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 of the students and courses table with the same student identification number.

2, outer joins. 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 it 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 of the left table specified in the outer clause, not just the rows that match the joined columns. If a row in the left table does not have a matching row in the right table, all select list 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 of 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 will be returned for left table.
3) Full join or full OUTER join
A full outer join returns all rows from the left and right tables. When a row does not have a matching row in another table, the selection list column for the other table contains a null value. If there are matching rows between the tables, the entire result set row contains the data values of 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.
The 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 you specify a table or view with a left or right outer join. For more information about using left or right outward joins 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
Relationship between a.ID and parent_id
--------------------------------------------------
1) Internal 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 a 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) Fully connected
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 join
Cross join: 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 result of the following statement 1 and statement 2 is the same.

Statement 1: An implicit cross join, there are no crosses joins.
SELECT o.id, O.order_number, C.id, C.name
From ORDERS O, CUSTOMERS C
WHERE o.id=1;

Statement 2: Explicit cross-joins, using crosses 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, inner connection (INNER join)
INNER JOIN (INNER join): There are two types, 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 result of the following statement 3 and statement 4 is the same.

Statement 3: Implicit inner join, without inner join, forms a Cartesian product of two tables.
SELECT O.id,o.order_number,c.id,c.name
From CUSTOMERS c,orders O
WHERE c.id=o.customer_id;

Statement 4: The displayed inner joins, commonly called Inner joins, have inner joins, and the resulting intermediate table is a Cartesian product of two tables after the on condition is filtered.
SELECT O.id,o.order_number,c.id,c.name
From CUSTOMERS C INNER joins ORDERS O on c.id=o.customer_id;
Query results for statement 3 and statement 4:

Three, outer joins (OUTER join): The outer join returns not only the data rows that meet the connection and query criteria, but also 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 different points are as follows:
The left OUTER join also returns data rows in the left table that do not meet the criteria for a join condition.
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. "all outside = 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 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 left OUTER joins 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 ORDERS O right OUTER joins 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:

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

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

The

Results from statement 7 and statement 8 queries are clearly different, and the results shown in statement 8 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.

Statement 9: Full outer join (OUTER join).
SELECT o.id,o.order_number,o.customer_id,c.id,c.name
from ORDERS O full OUTER joins CUSTOMERS C on C.id=o . customer_id;
Note: MySQL does not support an all-out connection, and the wording given here is suitable 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:

Statement 10: Out-of-left and right-side collections, in fact the query results and statement 9 are the same.
SELECT o.id,o.order_number,o.customer_id,c.id,c.name
from ORDERS O OUTER joins 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, union join: This is a very 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 11: Union query (Union JOIN) example, no SQL environment can be found for execution.
SELECT o.id,o.order_number,o.customer_id,c.id,c.name
from ORDERS O UNION joins CUSTOMERS C on C.id=o. CUSTOMER_ID

Statement 12: The equivalent implementation of statement 11 under DB2. It is not yet known if DB2 supports statement 11!
SELECT o.id,o.order_number,o.customer_id,c.id,c.name
from ORDERS O full OUTER joins 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: The equivalent implementation of statement 11 under Oracle.
SELECT o.id,o.order_number,o.customer_id,c.id,c.name
from ORDERS O full OUTER joins 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 have the value, 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, and the display column can only be represented by * (tested in the Oracle environment). You can specify natural for each type of connection (except for cross-connections). Here are a few examples.
Statement 14:
SELECT *
From ORDERS O NATURAL INNER JOIN CUSTOMERS C;

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

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

Statement 17:
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.

The difference between the condition (on condition) and the Where condition after the on:
On condition: is to filter two link table Cartesian product form the constraint condition of the intermediate table.
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:
On only the connection operation, where only the records of the intermediate table are filtered.

Viii. Summary
The connection query is the core of the SQL query, and the connection type of the connection query is selected according to actual requirements. 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 with the internal connection.
2, col_l is a subset of the Col_r when using the right outer connection.
3, Col_r is a subset of col_l with left outer connection.
4, Col_r and col_l each other has the intersection but each other is not a subset of time with the whole outside.
5, the 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.cy
From TAB1 T1
INNER JOIN TAB2 T2 on (T1. C1=t2. C2)
INNER JOIN TAB3 T3 on (T1. C1=t2. C3)
Left OUTER joins TAB4 on (T2. C2=t3. C3);
WHERE T1. X >t3. Y
The above SQL query is a demonstration of multi-table joins.

Deep understanding of SQL four connections-left outer connection, right outer connection, inner connection, full connection