"Connection query in SQL database"

1.1.1 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.name

From ORDERS O, CUSTOMERS C

WHERE o.id=1;

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

Selecto.id,o.order_number,c.id,

C.name

From ORDERS O Cross joincustomers C

WHERE o.id=1;
The results of statement 1 and statement 2 are the same, and the query results are as follows:

1.1.2 Inner Connection (INNER join)

INNER JOIN (Innerjoin): There are two kinds, 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.

Selecto.id,o.order_number,c.id,c.name

From CUSTOMERS C,orderso

wherec.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.

Selecto.id,o.order_number,c.id,c.name

From CUSTOMERS C innerjoin ORDERS O on c.id=o.customer_id;

Query results for statement 3 and statement 4:

1.1.3 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.

1.1.3.1 outer joins are divided into three categories: Left OUTER join (Leftouter join), right outer join (R OUTER join), and full-outer join (fully OUTER join). The common denominator of the 1.1.3.2 three people

is to return rows of data that meet the join criteria and query criteria (that is, inner joins).

1.1.3.3 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)

Selecto.id,o.order_number,o.customer_id,c.id,c.name

From ORDERS O left Outerjoin 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 Rightouter 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.

Selecto.id,o.order_number,o.customer_id,c.id,c.name

From ORDERS O left Outerjoin CUSTOMERS C on c.id=o.customer_id

Whereo. Order_number<> ' mike_order001 ';

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

Selecto.id,o.order_number,o.customer_id,c.id,c.name

From ORDERS O left Outerjoin CUSTOMERS C on c.id=o.customer_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).

Selecto.id,o.order_number,o.customer_id,c.id,c.name

From ORDERS O full outerjoin 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.

Selecto.id,o.order_number,o.customer_id,c.id,c.name

From ORDERS O left Outerjoin CUSTOMERS C on c.id=o.customer_id

UNION

Selecto.id,o.order_number,o.customer_id,c.id,c.name

From ORDERS O Rightouter joins CUSTOMERS C on c.id=o.customer_id;

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

"Connection query in SQL database"