Analysis of Mysql Join syntax and Performance Optimization

1. Join syntax Overview

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

Copy codeThe Code 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)

Copy codeThe Code is as follows:
Mysql> select A. id, A. name, B. name from A, B where A. id = B. id;
+ ---- + ----------- + ------------- +
| Id | 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.

Copy codeThe Code is as follows:
Mysql> select * from A inner join B on A. name = B. name;
+ ---- + -------- +
| Id | name |
+ ---- + -------- +
| 1 | Pirate | 2 | Pirate |
| 3 | Ninja | 4 | Ninja |
+ ---- + -------- +

3. Left join

Copy codeThe Code is as follows:
Mysql> select * from A left join B on A. name = B. name;
# Or: select * from A left outer join B on A. 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, (or left outer join: in Mysql, the two are equivalent. We recommend that you use left join .) left join generates A complete set of records from the left table (A) and matched 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:

Copy codeThe Code is 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 |
+ ---- + ----------- + ------ +
| 2 | Monkey | NULL |
| 4 | Spaghetti | NULL |
+ ---- + ----------- + ------ +
2 rows in set (0.00 sec)

Likewise, the inner join can be simulated as follows:

Copy codeThe Code is 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 |
+ ---- + -------- + ------ + -------- +
| 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:

Copy codeThe Code is as follows:
SELECT * from a left join B on a. name = B. name
Where B. id IS NULL
Union
SELECT * from a right join B on a. name = B. name
Where a. id is null;
# Result
+ ------ + ----------- + ------ + ------------- +
| Id | name |
+ ------ + ----------- + ------ + ------------- +
| 2 | Monkey | NULL |
| 4 | Spaghetti | NULL |
| NULL | 1 | Rutabaga |
| NULL | 3 | Darth Vader |
+ ------ + ----------- + ------ + ------------- +

Iv. Right join

Copy codeThe Code is as follows:
Mysql> select * from A right join B on A. name = B. name;
+ ------ + -------- + ---- + ------------- +
| Id | name |
+ ------ + -------- + ---- + ------------- +
| NULL | 1 | Rutabaga |
| 1 | Pirate | 2 | Pirate |
| 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.

Copy codeThe Code is as follows:
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 A. name = B. name;

In fact, in MySQL (only MySQL), cross join and inner join have the same performance. The results obtained without specifying the ON condition are cartesian products, otherwise, the system returns the exact matching results for the two tables. Inner join and cross join can omit the INNER or CROSS keyword. Therefore, the following SQL statements have the same effect:

Copy codeThe Code is as follows:
... FROM table1 inner join table2
... FROM table1 cross join table2
... FROM table1 JOIN table2

6. Full join

Copy codeThe Code is as follows:
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 |
+ ------ + ----------- + ------ + ------------- +
| 1 | Pirate | 2 | Pirate |
| 2 | Monkey | NULL |
| 3 | Ninja | 4 | Ninja |
| 4 | Spaghetti | NULL |
| NULL | 1 | Rutabaga |
| 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. Show (explicit) inner join VS implicit (implicit) inner join

For example:

Copy codeThe Code is as follows:
Select * from
Table a inner join table B
On a. id = B. id;

VS

Copy codeThe Code is as follows:
Select a. *, B .*
From table a, table B
Where 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.

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, all columns in the column of NULL will be generated, and the WHERE clause condition in the matching stage will not 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, when using Left (right) join, we must first provide as many matching conditions as possible to reduce Where execution. For example:

PASS

Copy codeThe Code is as follows:
Select * from
Inner join B on B. name = A. name
Left join C on C. name = B. name
Left join D on D. id = C. id
Where C. status> 1 and D. status = 1;

Great

Copy codeThe Code is as follows:
Select * from
Inner join B on B. name = A. name
Left join C on C. name = B. name and C. status> 1
Left 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:

Copy codeThe Code is as follows:
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 | 1 | 100 | NULL |
| 1 | 200 | 2 | 22 | 0 |
| 4 | 300 | NULL |
| 1 | 400 | 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 |
+ ---- + -------- + ------- +
| 1 | 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

Copy codeThe Code is as follows:
Insert into t1 (a1) select b1 from t2 where not exists (select 1 from t1 where t1.id = t2.r _ id );

Great

Copy codeThe Code is as follows:
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;