Mysql Join Syntax Parsing and Performance Analysis

1. Join syntax Overview

Join is used to associate fields in multiple tables. The syntax is as follows:

... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona

Table1: Left table; table2: Right table.

JOIN is divided into the following three categories by function:

Inner join (internal JOIN, or equivalent JOIN): Records of the two tables with connection matching relationships are obtained.

Left join: The full record of the LEFT table (table1) is obtained, that is, the right table (table2) does not have a matched record.

Right join (right join): opposite to left join, the full record of the RIGHT table (table2) is obtained, that is, the LEFT table (table1) does not match the corresponding record.

Note:Mysql does not support Full join.However, you can use the UNION keyword to merge left join and right join to simulate FULL join.

Next we will give a column to explain the following types. The following two tables (A, B)

mysql> select A.id,A.name,B.name from A,B where A.id=B.id;+----+-----------+-------------+| id | name       | name             |+----+-----------+-------------+|  1 | Pirate       | Rutabaga      ||  2 | Monkey    | Pirate            ||  3 | Ninja         | Darth Vader ||  4 | Spaghetti  | Ninja             |+----+-----------+-------------+4 rows in set (0.00 sec)

Ii. Inner join

An internal join is also called an equivalent join. inner join generates A group of data that meets both A and B.

mysql> select * from A inner join B on A.name = B.name;+----+--------+----+--------+| id | name   | id | name   |+----+--------+----+--------+|  1 | Pirate |  2 | Pirate ||  3 | Ninja  |  4 | Ninja  |+----+--------+----+--------+

3. Left join

Mysql> select * from A left join B on. name = B. name; # Or: select * from A left outer join B on. name = B. name; + ---- + ----------- + ------ + -------- + | id | name | + ---- + ----------- + ------ + -------- + | 1 | Pirate | 2 | Pirate | 2 | Monkey | NULL | 3 | Ninja | 4 | Ninja | 4 | Spaghetti | NULL | + ---- + ----------- + ------ + -------- + 4 rows in set (0.00 sec)

Left join, (orLeft outer join: the two are equivalent in Mysql, and left join is recommended.) Left join generates A complete set of records from the left table (A) and matches the records (right table (B). If no match exists, the right side will contain null.

If you want to generate A set of records from the left table (A) but do not contain records from the right table (B), you can set the where statement to execute the statement as follows:

mysql> select * from A left join B on A.name=B.name where A.id is null or B.id is null;+----+-----------+------+------+| id | name      | id   | name |+----+-----------+------+------+|  2 | Monkey    | NULL | NULL ||  4 | Spaghetti | NULL | NULL |+----+-----------+------+------+2 rows in set (0.00 sec)

Likewise, the inner join can be simulated as follows:

mysql> select * from A left join B on A.name=B.name where A.id is not null and B.id is not null;+----+--------+------+--------+| id | name   | id   | name   |+----+--------+------+--------+|  1 | Pirate |    2 | Pirate ||  3 | Ninja  |    4 | Ninja  |+----+--------+------+--------+2 rows in set (0.00 sec)

Difference set:

The following example shows how to calculate the difference set:

SELECT * from a left join B ON. name = B. nameWHERE B. id IS NULLunionSELECT * from a right join B ON. name = B. nameWHERE. id is null; # result + ------ + ----------- + | id | name | + ------ + ----------- + ------ + ------------- + | 2 | Monkey | NULL | 4 | Spaghetti | NULL | 1 | Rutabaga | NULL | 3 | Darth Vader | + ------ + ----------- +

Iv. Right join

mysql> select * from A right join B on A.name = B.name;+------+--------+----+-------------+| id   | name   | id | name        |+------+--------+----+-------------+| NULL | NULL   |  1 | Rutabaga    ||    1 | Pirate |  2 | Pirate      || NULL | NULL   |  3 | Darth Vader ||    3 | Ninja  |  4 | Ninja       |+------+--------+----+-------------+4 rows in set (0.00 sec)

Same as left join.

V. Cross join

Cross join: cross join. The result is the product of two tables, that is, Cartesian product.

The product of Descartes is also called the straight product. Assume that the Set A = {a, B}, and set B = {0, 1}, the Cartesian product of the Two sets is {(a, 0), (a, 1 ), (a, 2), (B, 0), (B, 1), (B, 2 )}. It can be expanded to multiple sets. In A similar example, if A represents A set of students in A school and B represents A set of all courses in the school, cartesian products of A and B represent all possible course selections.

Mysql> select * from A cross join B; + ---- + ----------- + ---- + ------------- + | id | name | + ---- + ----------- + ---- + ------------- + | 1 | Pirate | 1 | Rutabaga | 2 | Monkey | 1 | Rutabaga | 3 | Ninja | 1 | Rutabaga | 4 | Spaghetti | 1 | Rutabaga | 1 | Pirate | 2 | Pirate | 2 | Monkey | 2 | Pirate | 3 | Ninja | 2 | Pirate | 4 | Spaghetti | 2 | Pirate | 1 | Pirate | 3 | Darth Vader | 2 | Monkey | 3 | Darth Vader | 3 | Ninja | 3 | Darth Vader | 4 | Spaghetti | 3 | Darth Vader | 1 | Pirate | 4 | Ninja | 2 | Monkey | 4 | Ninja | 3 | ninja | 4 | Ninja | 4 | Spaghetti | 4 | Ninja | + ---- + ----------- + ---- + ------------- + 16 rows in set (0.00 sec) # Run mysql> select * from A inner join B; try again # execute mysql> select * from A cross join B on. name = B. name; try

More content source code search http://www.codesocang.com

Actually,In MySQL (only MySQL), cross join and inner join have the same performance., The results obtained without specifying the ON condition are cartesian products, and vice versa, the results of two completely matching tables are obtained. Inner join and cross join can omit the INNER or CROSS keyword. Therefore, the following SQL statements have the same effect:

... FROM table1 INNER JOIN table2... FROM table1 CROSS JOIN table2... FROM table1 JOIN table2

6. Full join

mysql> select * from A left join B on B.name = A.name     -> union     -> select * from A right join B on B.name = A.name;+------+-----------+------+-------------+| id   | name      | id   | name        |+------+-----------+------+-------------+|    1 | Pirate    |    2 | Pirate      ||    2 | Monkey    | NULL | NULL        ||    3 | Ninja     |    4 | Ninja       ||    4 | Spaghetti | NULL | NULL        || NULL | NULL      |    1 | Rutabaga    || NULL | NULL      |    3 | Darth Vader |+------+-----------+------+-------------+6 rows in set (0.00 sec)

All records generated by full join (matching records of both parties) are in tables A and B. If no match exists, the opposite side will contain null.

VII. Performance Optimization 1. explicit (explicit) inner join VS implicit (implicit) inner join

For example:

select * fromtable a inner join table bon a.id = b.id;

VS

select a.*, b.*from table a, table bwhere a.id = b.id;

I have compared (10 million data records) in the database. They are used almost the same. The first is the displayed inner join, and the last is the implicit inner join.

Reference: Explicit vs implicit SQL joins

2. left join/right join VS inner join

Try to use inner join. Avoid left join and NULL.

When using left join (or right join), you should be aware of the following points:

(1) execution sequence of. on and where

ON condition (ON in "a left join B on condition expression") is used to determine how to retrieve data rows from Table B. If no row of data in Table B matches the ON condition, an additional row of data with all columns as NULL is generated,None of the WHERE clause conditions in the matching phase will be used. The WHERE clause condition is used only after the matching stage is complete. It will retrieve and filter the data generated in the matching phase.

Therefore, pay attention to the following:When using Left (right) join, we must first provide as many matching conditions as possible to reduce Where execution.For example:

PASS

select * from Ainner join B on B.name = A.nameleft join C on C.name = B.nameleft join D on D.id = C.idwhere C.status>1 and D.status=1;

Great

select * from Ainner join B on B.name = A.nameleft join C on C.name = B.name and C.status>1left join D on D.id = C.id and D.status=1

From the above example, we can see that the ON condition is met as much as possible, but the Where condition is not used. From the perspective of execution performance, the second one is obviously more time-saving.

(2) Note the differences between the ON clause and the WHERE clause.

For example, the author raises a column:

mysql> SELECT * FROM product LEFT JOIN product_details       ON (product.id = product_details.id)       AND product_details.id=2;+----+--------+------+--------+-------+| id | amount | id   | weight | exist |+----+--------+------+--------+-------+|  1 |    100 | NULL |   NULL |  NULL ||  2 |    200 |    2 |     22 |     0 ||  3 |    300 | NULL |   NULL |  NULL ||  4 |    400 | NULL |   NULL |  NULL |+----+--------+------+--------+-------+4 rows in set (0.00 sec)mysql> SELECT * FROM product LEFT JOIN product_details       ON (product.id = product_details.id)       WHERE product_details.id=2;+----+--------+----+--------+-------+| id | amount | id | weight | exist |+----+--------+----+--------+-------+|  2 |    200 |  2 |     22 |     0 |+----+--------+----+--------+-------+1 row in set (0.01 sec)

The first query uses the ON condition to retrieve all data rows from the product_details table of left join. The second query performs a simple left join operation, and then uses the WHERE clause to filter out non-conforming data rows from the left join data.

(3) try to avoid subqueries and use join

Performance is often the case when the data volume is large. At this time, we should avoid complicated subqueries. As follows:

PASS

insert into t1(a1) select b1 from t2 where not exists(select 1 from t1 where t1.id = t2.r_id); 

Great

insert into t1(a1)  select b1 from t2  left join (select distinct t1.id from t1 ) t1 on t1.id = t2.r_id   where t1.id is null;  

For more information, see mysql's exists and inner join and not exists and left join performance differences.

Original article: http://www.codesocang.com/jiaocheng/mysql/8068.html