Database Common SQL statements (ii)

Before the main introduction of a single table operation of the relevant query statements, the next introduction to the relationship between the multi-table, here is mainly a multi-table data record query, that is, how to display multiple tables in a query statement data, which is also called multi-table data record connection query.

When implementing a connection query, the first is to connect two or more tables in a relationship (forming a new relational table after the connection) and then querying the requested data records. Connection queries are divided into outer join queries and inner JOIN queries.

I. Relationships between tables and tables

and (UNION): and the action is to merge two or more tables with the same number of fields and field types together.

2. Cartesian product: Two tables after the Cartesian product formation of a new relationship in the field two tables will be merged together, the data records will be combined, such as 3 records in the first table, 5 records in the second table, two tables after the Cartesian product will be produced by a total of 3*5=15 Data records.

3. Connection operation (join): in the Cartesian product data record of the table relationship, a new relationship is generated by selecting the comparison criteria for the corresponding field values in the two tables. In fact, the data recorded after the Cartesian product is filtered to get the corresponding data, according to different screening methods, there is an inner join (INNER join), an outer join (OUTER join), Cross join. These connections are based on a Cartesian product.

Second, the query operation

1. Internal connection query: preserve all matching data records in a table relationship, discard unmatched records, and note that only records that match the matching criteria are retained , according to the matching criteria: natural Join (NATURAL join), equivalent connection, unequal connection.

Natural connection: In the data record of the Cartesian product, the first automatic record matching is based on the field of the same name in the table relationship (that is, only the same record with the same value in the two fields with the same names), then the repeating field is removed (duplicate fields remain one), using the keyword NATURAL Join to perform a self-connect query operation, the connection condition cannot be specified since the connection is auto-completed. Query Example:

Equivalent connection: An equivalent connection within a connection query, using INNER join ... On ... is achieved by using the relational operator "=" after the keyword on to specify the equivalent condition , as the name implies, is the value of two fields equal conditions, for example:

As a result, the equivalent connection is compared to a natural connection, except that duplicate field Deptno are not removed.

Unequal connections: Using INNER JOIN ... On ... The way to achieve, is the keyword on the back can be used >, <, >=, <=,! = These operators specify conditions that are not equal, so they are called unequal connections, for example:

2. Outer JOIN query (OUTER join): In the Cartesian data record of the table relationship, not only all matching data records are preserved, but also some mismatched data records are retained, according to the source of the data record of the reservation mismatch: Left outer connection (leave OUTER Join), right-OUTER join, and full-OUTER join, the outer JOIN query returns all data records for at least one table in the action table.

Left OUTER join: LEFT OUTER join in outer join query, use the key to the leftmost join .... On .... To implement, that is, when the matching condition is executed in the Cartesian product new relationship, in addition to preserving the record that satisfies the ON keyword, the table on the left side of the keyword LEFT join is the reference table, preserving all records that do not match in the table on the right. to illustrate:

Right outer join: RIGHT outer join in outer join query, use the keyword-join .... On .... To implement, that is, when the matching condition is executed in the Cartesian product new relationship, in addition to preserving the records that satisfy the ON keyword, the table on the right side of the key is a reference table, preserving all records that do not match in the right table. to illustrate:

Full join: In the Cartesian product of a table relationship, in addition to selecting matching data records, it also contains all data records that do not match in the left and right tables, for example:

3. Cross joins: Cross joins, which are actually all data records after the Cartesian product of a table relationship without a conditional filter, and do not require any matching criteria.

The above table for the connection query operation, mainly divided into internal and external connections, used for multi-table query.

Database Common SQL statements (ii)