MySQL: Connection Query

Per entity, one table

When a business requires data from more than one entity, it is obvious that multiple tables are used together, and the records of multiple tables are connected.

1. General Ideas :

First, all the data is connected to a certain condition, and then the filter is processed.

2. Classification Method :

Depending on the conditions of the connection, there are several categories:

① Internal Connection (Inner): Multiple data that requires a connection must exist for the connection to be made.

"Give a chestnut" the following three tables, respectively, recorded the teacher's information, class information, as well as the teachers of the substitute situation.

At this time, if you want to get all the teacher's substitute information, ' Han Xin ' in table 3 has a matching record (internal connection)

But found that Sun Wu did not take a class, that is, in table 1, "Sun WU" in table 3 does not match the record (external connection).

table 1. Information table for all Teachers 2. Information for all classes

Table 3. The teachers ' substitute situation

"Syntax" Tbl_left INNER join tbl_right on join condition;

"Give a chestnut" for all the teachers in table 1, the start of the time, substitute days.

tip: An inner connection can omit the join condition, which means that all data on the left table is connected to the record of the right table ( cross Join or Cartesian product connection ).

You can now use the cross join instead of the inner join.

In fact, you can use multi-table Query method, not practical where, do Cartesian product.

In MySQL, inner join is the default way to connect, and you can omit inner.

"notation" 1) on (as in the previous example).

2) where (simply replace on to where).

The result is the same, but the logic is different: where it first forms a Cartesian product that contains all the connections, and then filters the data;

On will first filter the data that satisfies the condition, and then connect the filtered data.

"Using" when you have the same name field, you can use the using (name)to conditionally constrain it.

"as" to the table, the field alias , you can change the longer table name to a shorter alias, to ensure concise, clear.

② External Connection (outer): The data that is responsible for the connection has one or more non-existent

The "syntax" Tbl_name the LEFT outer join Tbl_name the on join condition; (You can also use using without using where!! )

Tip: There must be a connection condition!! (inner connection no condition = Cartesian product, but external connection must be available)

1) left outer connection ; (Ieft outer join)

If the left table's data is not connected to the right table, the data in the left table is retained in the final result .

(For example, ' Sun Wu ' in the left table does not have data connected to the right table, but ' Sun Wu ' is retained in the final data);

If the right table data is not connected to the left table, the final result, the right table data is discarded (for example, the exchange of two tables in the left and right order, found that the "Sun WU" table is discarded).

2) right outer connection ;

3) Full external connection (MySQL not supported);

(left connection) union (right connection);

③ Natural Connection (natural): Complete the connection process with MySQL's own judgment, without specifying the connection conditions.

MySQL uses the same field in multiple tables as the connection condition .

"Syntax" (table 1) Natural join (table 2);----

Natural LEFT JOIN-----outer

Natural RIGHT Join---off

3. Multi-table connection

MySQL: Connection Query