Database link Query

(Source http://blog.sina.com.cn/s/blog_b4540d020101dgpz.html)

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.namefrom  orders O, customers cwhereo.id=1;

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

Select o.id,o.order_number,c.id,c.namefrom orders o cross join  customers cwhere 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.namefrom  customers C, orders Owhere 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.namefrom customers c INNER join orders O on c.id=o.customer_id;

Query results for statement 3 and statement 4:

Third, outer join (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.namefrom 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.namefrom 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.namefrom orders O left outer joins customers C on c.id=  O.customer_idwhere 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.namefrom orders o outer join customers C on c.id=o.customer_id a nd o.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 o.id,o.order_number,o.customer_id,c.id,c.namefrom orders o full outer join customers C 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.

Select o.id,o.order_number,o.customer_id,c.id,c.namefrom orders O left outer joins customers C on c.id=  O.customer_idunionselect  o.id,o.order_number,o.customer_id,c.id,c.namefrom 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 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 o.id,o.order_number,o.customer_id,c.id,c.namefrom orders o Union join 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.namefrom orders o full outer join customers C on c.id=  O.customer_idexceptselect  o.id,o.order_number,o.customer_id,c.id,c.namefrom 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.namefrom orders o full outer join customers C on c.id=  O.customer_idminusselect  o.id,o.order_number,o.customer_id,c.id,c.namefrom 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 exist 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 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:
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.cyfrom  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;

Database link Query