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 the two tables have the same name.
Column, assuming they are used in the connection condition, and the connection condition contains only one connection column. The on statement is not allowed. You cannot specify a display column. The display column can only be represented *.
). 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.
1. Single Table query: filter the records in the 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.
2. join queries for two tables: product (Cartesian Product) of the two tables are filtered using the on condition and connection type to form an intermediate table. Then, records of the intermediate table are filtered Based on the where condition, return the query result based on the column specified by select.
3. Multi-Table connection query: perform queries on the first and second tables based on the two-Table connection, and then perform connection queries 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.
This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/liuzheng2684/archive/2010/11/05/5989385.aspx
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 the two tables have the same name.
Column, assuming they are used in the connection condition, and the connection condition contains only one connection column. The on statement is not allowed. You cannot specify a display column. The display column can only be represented *.
). 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.
1. Single Table query: filter the records in the 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.
2. join queries for two tables: product (Cartesian Product) of the two tables are filtered using the on condition and connection type to form an intermediate table. Then, records of the intermediate table are filtered Based on the where condition, return the query result based on the column specified by select.
3. Multi-Table connection query: perform queries on the first and second tables based on the two-Table connection, and then perform connection queries 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.
This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/liuzheng2684/archive/2010/11/05/5989385.aspx
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
