mysql multi-table joint query

INNER JOIN INNER JOIN is called an equijoin in MySQL, which means you need to specify the equijoin conditions. CROSS and INNER JOIN are divided together in MySQL.

Multiple table connection types

1. Cartesian product (cross join) can be CROSS JOIN or omit CROSS in MySQL That is, JOIN, or use ',' such as:

SELECT * FROM table1 CROSS JOIN table2 SELECT * FROM table1 JOIN table2 SELECT * FROM table1,table2

Since the result returned is the product of the two data tables being connected, it is generally not recommended to use it when there are WHERE, ON or USING conditions, because When there are too many data table items, it will be very slow. Generally use LEFT [OUTER] JOIN or RIGHT [OUTER] JOIN

2. INNER JOIN INNER JOIN is called an equijoin in MySQL, that is, you need to specify the equijoin conditions. CROSS and INNER JOIN in MySQL divided together. join_table: table_reference [INNER | CROSS] JOIN table_factor [join_condition]

3. Outer joins in MySQL are divided into left outer joins and right joins, that is, in addition to returning results that meet the join conditions, For results that do not meet the join conditions in the left table (left join) or right table (right join), NULL is used accordingly.

Example:

user表:
id | name
———
1 | libk
2 | zyfon
3 | daodao
user_action表:
user_id | action
—————
1 | jump
1 | kick
1 | jump
2 | run
4 | swim

sql statement:

select id, name, action from user as uleft join user_action a on u.id = a.user_id

Result:

id | name   | action
——————————–
1  | libk  | jump     结果1      
1  | libk  | kick     结果2        
1  | libk  | jump     结果3       
2  | zyfon  | run      结果4          
3  | daodao | null     结果5         
---------------------

Analysis:

Notice that there are also errors in user_action There is a record of user_id=4, action=swim, but it does not appear in the results.

The user with id=3, name=daodao in the user table does not have a corresponding record in user_action, but it does appear. In the result set

Because it is a left join now, all work is based on left.

Results 1, 2, 3, and 4 are all records in both the left table and the right table ,5 is a record that is only in the left table but not in the right table

Working principle:

Read a record from the left table and select all the records in the right table that match on Records (n) are connected to form n records (including duplicate rows, such as result 1 and result 3). If there is no table on the right that matches the on condition, the connected fields are all null. Then continue reading the next one .

Extension:

We can use the rule that if there is no on match in the right table, null will be displayed to find all the records that are in the left table but not in the right table. Pay attention to the judgment used That column must be declared not null.

For example:

select id, name, action from user as uleft join user_action a on u.id = a.user_idwhere a.user_id is NULL

(Note: 1. If the column value is null, you should use is null instead of =NULL

2. Here a.user_id column must be declared as NOT NULL.)

The result of the above sql:

id | name | action
————————–
3 | daodao | NULL

The above is the detailed content of mysql multi-table joint query. For more information, please follow other related articles on the PHP Chinese website!