Shulou(Shulou.com)06/01 Report--

This article mainly introduces the query method of mysql multi-table union, the contents of the article are carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand the query method of mysql multi-table union.

Multi-table connection type

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

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

Because the result returned is the product of two joined tables, it is generally not recommended when there are WHERE, ON or USING conditions, because it is very slow when there are too many items in the table. Generally use LEFT [OUTER] JOIN or RIGHT [OUTER] JOIN

two。 Inner join INNER JOIN calls INNER JOIN an equivalent join in MySQL, that is, you need to specify an equivalent join condition in which CROSS and INNER JOIN are divided together in MySQL. Join_table: table_reference [INNER | CROSS] JOIN table_factor [join_condition]

3. The outer join in MySQL is divided into left outer join and right join, that is, in addition to returning the results that meet the connection conditions, you should also return the results in the left table (left join) or right table (right join) that do not meet the connection conditions, and the corresponding NULL is used.

Example:

User Table: id | name---1 | libk2 | zyfon3 | daodaouser_ Action Table: user_id | action-1 | jump1 | kick1 | jump2 | run4 | swim

Sql statement:

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

Results:

Id | name | action-1 | libk | jump result 1 1 | libk | kick result 2 1 | libk | jump result 3 2 | zyfon | run result 4 3 | daodao | null result 5-

Analysis:

Notice that there is another record of user_id=4 and action=swim in user_action, but it does not appear in the result.

The users of id=3 and name=daodao in the user table do not have a corresponding record in user_action, but appear in the result set.

Since it is now left join, all work shall be based on left.

Results 1 the records of both the left table and the right table are both on the left table and on the right table, and 5 is the record on the left table but not on the right table.

How it works:

Read an entry from the left table and select all the right table records (n entries) that match on to join to form n records (including duplicate rows, such as result 1 and result 3). If there is no table matching the on condition on the right, the joined fields are null. Then move on to the next one.

Extension:

We can use the rule of displaying null without on matching in the right table to find out all the records in the left table and not in the right table. Note that the column used to judge must be declared as not null.

Such as:

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

(note: 1. Column value is null should use is null instead of = NULL

two。 Here the a.user_id column must be declared as NOT NULL.)

The result of the above sql:

Id | name | action-3 | daodao | NULL

After reading the above query methods of mysql multi-table association, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to pay attention to our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.