SQL General Query detailed

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  from where o.id=1;

Statement 2: Explicit cross-joins, using crosses join.

Select Join 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  from 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 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 examples illustrate:

Statement 5: Left OUTER join (outer join)

Select On c.id=o.customer_id;

Statement 6: Right outer join (outer join)

Select 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 On c.id=  where O.order_number<>'mike_order001';

Statement 8: Place the WHERE condition in statement 7 behind on.

Select On c.id ando.order_number<>'mike_order001';

From the results of statement 7 and statement 8 queries, it is clear that the result of statement 8 is 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 On c.id=o.customer_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: A collection of left and right outside, in fact the query results and statement 9 are the same.

Selecto.id,o.order_number,o.customer_id,c.id,c.name from orders o  outer joins customers c on c.id< c7>=      o.customer_id Union 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;

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

Iv. Joint Connection (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 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!

Selecto.id,o.order_number,o.customer_id,c.id,c.name from orders o full outer joins customers c on c.id< c7>=      o.customer_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_id;

Statement 13: The equivalent implementation of statement 11 under Oracle.

Selecto.id,o.order_number,o.customer_id,c.id,c.name from orders o full outer joins customers c on c.id< c8>=      o.customer_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 14:

join customers C;

Statement 15:

join customers C;

Statement 16:

join customers C;

Statement 17:

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 the on condition and the connection type filter to form the intermediate table; then filter the records of the intermediate table based on the Where condition and return the query results 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. 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:

Selectt1.c1,t2.cx,t3.cy   fromtab1 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;