Internal and external connections and cross-connections of the DB2 database

This article introduces you to the DB2 database, cross join), inner join), 3, outer join for your reference.

1. cross join of DB2): There are two types of explicit and implicit statements without the ON clause. The returned result is the product of two tables, also called Cartesian product, the number of returned records should be the sum of records in Table a and table B.

Explicit: select [cols_list] from a cross join B where [condition]

Implicit: select [cols_list] from a, B where [condition]

2. DB2 inner join): There are two types of explicit and implicit data rows that meet the connection conditions and query conditions in the connection table, it is the same as writing common SQL statements.

Explicit: select [cols_list] from a inner join B on [condition] where [condition]

Implicit: select [cols_list] from a, B where [condition]

3. DB2 outer join): The outer join 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 (right outer join), and full outer join (full outer join ).

All three return data rows that meet the connection condition and query condition, 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 collection of the upper left Outer Join and the right Outer Join.) that is, "Total outer = left outer UNION right Outer Join ".

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.

For example, select [cols_list] from a left join B on [condition] where [condition]

Multiple tables can be used:

Select [cols_list] from a left join B on [condition] inner join c on [condition] where [condition]