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

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 joinThe 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 joinA 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 joinA 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 JoinA 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      id   job   parent_id                  1   Zhang 3                    1      23     1                  2   John Doe                   2     34     2                  3   Wang Wu                   3     34      4&NBsp;        a.id relationship with parent_id     ----------------------------- ---------------------   1) Internal connectionSelect a.*,b.* from a inner join B on a.id=b.parent_id result is 1 3 1 23 1 2 Lee 42 34 2
2) Left connectionSelect a.*,b.* from a LEFT join B on a.id=b.parent_id result is 1 3 1 2 3 1 2 Lee 42 34 2 3 Wang Wu null
3) Right connectionSelect a.*,b.* from a right join B on a.id=b.parent_id result is 1 3 1 23 1 2 Li 42 2 null 3 34 4
4) Fully connected     select   a.*,b.*   from   a   full   join    b     on   a.id=b.parent_id     Results        1   Zhang 3                   1     23     1     2   John Doe                   2      34     2     null                  3     34     4     3    Wang Wu                  Null--------------------------------------------------------------------------------------------One, cross joinCross 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 join (INNER join ) (INNER join): There are two, 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 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. "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 example illustrates: 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 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 OUTER joins CUSTOMERS C on C.id=o.custome r_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:
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.custome r_id UNION 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.custo mer_id; The query results for statement 9 and statement 10 are the same, as follows:

Iv. 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 OUTER joins CUSTOMERS C on C.id=o.custome r_id EXCEPT SELECT o.id,o.order_number,o.customer_id,c.id,c.name from ORDERS O INNER joins CUSTOMERS C on C.id=o.customer_i D
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 OUTER joins CUSTOMERS C on C.id=o.custome r_id minus SELECT o.id,o.order_number,o.customer_id,c.id,c.name from ORDERS O INNER joins 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: SELECT * from ORDERS O NATURAL INNER JOIN CUSTOMERS C;

statement: 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.

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 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. The following summarizes 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 joins 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.

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