Connection query for MySQL notes

Join query connects two or more tables according to a certain condition and selects the required data.

When different tables have fields of the same meaning, you can use these fields to connect these tables.

Reference Table: employee

Reference Table: department

 

We can see that both tables have the same field d_id.

When two tables contain fields of the same meaning (different names can be used), you can perform connection query.

Internal Connection Query
Copy codeThe Code is as follows:
Mysql> SELECT num, name, employee. d_id, sex, d_name, function
-> FROM employee, department
-> WHERE employee. d_id = department. d_id;
+ ------ + -------- + ------ + ----------- + -------------- +
| Num | name | d_id | sex | d_name | function |
+ ------ + -------- + ------ + ----------- + -------------- +
| 1 | Zhang San | 1001 | male | Ministry of Science and Technology | R & D product |
| 2 | Li Si | 1001 | female | Ministry of Science and Technology | R & D product |
| 3 | Wang Wu | 1002 | male | Production Department | production product |
+ ------ + -------- + ------ + ----------- + -------------- +
Rows in set (0.00 sec)

The internal connection query only queries the results of the exact match. The d_id field is used for connection.

The following table also uses this field

External Connection Query

You also need to specify fields for external queries. When the field value is equal, you can query the record.

In addition, records with different values of this field can also be queried.

External Connection query includes left connection query and right connection Query

Left join query
Copy codeThe Code is as follows:
Mysql> SELECT num, name, employee. d_id, d_name, function
-> FROM employee left join department
-> ON employee. d_id = department. d_id;
+ ------ + -------- + ------ + ----------- + -------------- +
| Num | name | d_id | d_name | function |
+ ------ + -------- + ------ + ----------- + -------------- +
| 1 | Zhang San | 1001 | Ministry of Science and Technology | R & D product |
| 2 | Li Si | 1001 | Ministry of Science and Technology | R & D product |
| 3 | Wang Wu | 1002 | Production Department | production product |
| 4 | Aric | 1004 | NULL |
+ ------ + -------- + ------ + ----------- + -------------- +
Rows in set (0.00 sec)

In addition, the information matching the d_id field in the two tables is found.

In addition, information about all specified fields in the employee table is queried through left join.

Because the Aric does not have the corresponding d_name and function information, null is displayed.

Right join query
Copy codeThe Code is as follows:
Mysql> SELECT num, name, employee. d_id, d_name, function
-> FROM employee right join department
-> ON employee. d_id = department. d_id;
+ ------ + -------- + ------ + ----------- + -------------- +
| Num | name | d_id | d_name | function |
+ ------ + -------- + ------ + ----------- + -------------- +
| 1 | Zhang San | 1001 | Ministry of Science and Technology | R & D product |
| 2 | Li Si | 1001 | Ministry of Science and Technology | R & D product |
| 3 | Wang Wu | 1002 | Production Department | production product |
| NULL | sales department | planned sales |
+ ------ + -------- + ------ + ----------- + -------------- +
Rows in set (0.00 sec)

In contrast to the above, the matching information and all the specified fields in the department table are queried.

However, because some fields in the employee table do not correspond, the last row of record displays NULL.

Composite condition connection Query
Copy codeThe Code is as follows:
Mysql> SELECT num, name, employee. d_id, sex, age, address
-> FROM employee, department
-> WHERE employee. d_id = department. d_id
-> AND age> = 25;
+ ------ + -------- + ------ + ------------- +
| Num | name | d_id | sex | age | address |
+ ------ + -------- + ------ + ------------- +
| 1 | zhangsan | 1001 | male | 26 | layer 5, building 3 |
| 3 | Wang Wu | 1002 | male | 25 | layer 1, Building 5 |
+ ------ + -------- + ------ + ------------- +
Rows in set (0.00 sec)

The composite condition connection query adds a limit when querying connections. Here, age> = 25 is

Generally, the more conditions, the more precise the query, the more available AND accumulated the conditions.

In addition, you can use composite conditions to sort orders.

Tips:The most frequently used connection queries are internal connection queries.