SQL connection query syntax and usage

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:

Third, outer joins (outer join):

The outer join not only returns rows of data that meet the criteria of the connection and query, 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 ';

From the results of statements 7 and 8 queries, it is obviously not the same, and the results shown in statement 8 are 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 the Orders O full outer join customers C on c.id=o.customer_id;

Note: MySQL does not support all external connections, and the wording given here is suitable for Oracle and DB2. However, you can get the query result of the whole outer join 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, and the query result is actually the same as statement 9.

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

Union

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;

The query results for statement 9 and statement 10 are the same, as follows:

Iv. Joint joins (union join):

This is a very 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 the Orders O Union join customers C on c.id=o.customer_id

Statement 12: The equivalent implementation of 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 the 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 the 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 the 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 the orders O inner JOIN customers C on c.id=o.customer_id;

The results of the query are as follows:

V. Natural connection (natural inner join):

To be honest, this kind of connection query does not have the value of existence, 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). For each type of connection (except for cross joins), you can specify natural. Here are a few examples.

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: Filters the records in a table according to where conditions, forming an intermediate table (this intermediate table is not visible to the user), and then selecting the appropriate 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) is filtered using on condition and connection type to form an intermediate table, then the records of the intermediate table are filtered according to the Where condition, and the query results are returned according to the columns specified by the SELECT.

Third?? Multi-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.

Viii. Summary

The connection query is the core of the SQL query, and the connection type of the connection query is chosen according to the actual requirement. 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 basis:

1, check the two Table association columns equal data with the internal connection.

2, col_l is a subset of col_r with the right outer connection.

3, Col_r is a subset of col_l with left outer connection.

4, Col_r and col_l have the intersection of each other but each other is not a subset of time with the whole.

5, when the difference operation with the joint query.

Multiple table queries, 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 join TAB4 on (T2.C2=T3.C3);

where t1.x >t3.y;