MySQL Multi-table query

1. and (union)

"and" is to merge tables with the same number of fields and field types together

2. Cartesian product

Cartesian product is the result of a return without a join condition table relationship

3. Internal connection (inner join)

provides a specialized function for operation of database operations JOIN. The connection is in the Cartesian product data record of the table relationship, and a new relationship is generated according to the comparison criteria of the corresponding field values. Connections are also divided into inner joins (INNER joins), outer joins (OUTER joins), and cross joins .

An inner join is to preserve all matching data records in a table relationship, discarding unmatched data records. According to the matching conditions can be divided into natural connections, equivalent connections and unequal connections.

3.1 Natural Connection (NATURAL join)

A natural join is a Cartesian product of a table relationship, and first records match automatically based on fields of the same name in the table relationship, and then the duplicate fields are removed.

Its characteristics are as follows:

@ will automatically determine the same name of the field, and then to match the data values, remove the same field unequal records;

@ in a new relationship that performs a natural connection, although you can specify which fields are included, you cannot specify matching criteria during execution;

@ in a new relationship that performs a natural condition, the matching field name is only one, which removes the repeating field.
3.2 equivalent connection

The so-called equivalent connection is the Cartesian product of the table relationship, selecting the data record with the matching field value equal (= symbol), compared with the natural connection, the equivalent connection relational data operation needs to specify the matching condition with "sign =" during execution. Duplicate fields are not removed from the new relationship.

3.3 Unequal Connections

in a Cartesian product where the connection is not equal to a table relationship, select the data record for the matching field value inequality (! = symbol).

4. External connection (OUTER join)

An outer join is a Cartesian product of a table relationship that preserves all the matching data records in a table relationship and preserves some unmatched data records. The data record source according to the reservation mismatch criteria can be divided into a left outer join (OUTER join), a right outer join (OUTER join), and an all-out connection (full OUTER join)

4.1 left Outer connection

The left OUTER JOIN operation is the Cartesian product of the table relationship, in addition to selecting matching data records, and also containing unmatched data records from the table on the left.

4.2 Right Outer connection

The right outer join is a Cartesian product of a table relationship, and in addition to selecting a matching data record, it contains unmatched data records in the right table.

4.3 full external connection

The whole outer join is the Cartesian product of the table relationship, in addition to selecting the matching data records, it also contains the mismatched data records in both left and right tables.

MySQL Multi-table query