Analysis of Mysql join syntax and performance optimization _mysql

A Join Syntax Overview

Join is used for the relationships between fields in multiple tables, as follows:

Copy Code code as follows:

... From table1 inner| Left| Right JOIN table2 on Conditiona

Table1: Left table; table2: Right table.

The JOIN is roughly divided into the following three categories by function:

INNER join (inner JOIN, or equivalent connection): Gets a record of a connection matching relationship in two tables.

Left join: Gets the left table (table1) full record, that is, the right table (table2) does not have a corresponding matching record.

Right join: On the contrary to the left join, get the right table (table2) full record, that is, the left-hand table (table1) does not match the corresponding record.

Note: MySQL does not support full joins, but you can use the Union keyword to combine the left join with the right join to simulate a full join.

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

Copy Code code as follows:

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)

Two. Inner Join

An inner join, also called an equivalent connection, produces a set of data that conforms to both A and B. INNER JOIN.

Copy Code code as follows:

Mysql> SELECT * from A inner join B on a.name = B.name;
+----+--------+----+--------+
| ID | name | ID | name |
+----+--------+----+--------+
| 1 |  Pirate | 2 | Pirate |
| 3 |  Ninja | 4 | Ninja |
+----+--------+----+--------+

Three. Left Join

Copy Code code as follows:

Mysql> SELECT * from A LEFT join B on a.name = B.name;
#或者: SELECT * from A LEFT outer join B on a.name = B.name;

+----+-----------+------+--------+
| ID | name | ID | name |
+----+-----------+------+--------+
| 1 |    Pirate | 2 | Pirate |
| 2 | Monkey | NULL | NULL |
| 3 |    Ninja | 4 | Ninja |
| 4 | Spaghetti | NULL | NULL |
+----+-----------+------+--------+
4 rows in Set (0.00 sec)

Left join, (or outer join: in MySQL, the two are equivalent, recommended to use the right join.) The left-hand connection produces a complete set of records from the left table (A), with a matching record (B). If there is no match, the right side will contain null.

If you want to produce only one set of records from the left table (A), but do not contain records for the right table (B), you can do so by setting the where statement, as follows:

Copy Code code as follows:

Mysql> select * from "A left join B" 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)

Similarly, you can simulate inner join. As follows:

Copy Code code as follows:

Mysql> select * from "A left join B" A.name=b.name where a.id is isn't 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)

To find the difference set:

Depending on the example above, you can find the difference set, as follows:

Copy Code code 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;
# results
+------+-----------+------+-------------+
| ID | name | ID | name |
+------+-----------+------+-------------+
| 2 | Monkey | NULL | NULL |
| 4 | Spaghetti | NULL | NULL |
| NULL |    NULL | 1 | Rutabaga |
| NULL |    NULL | 3 | Darth Vader |
+------+-----------+------+-------------+

Four. Right Join

Copy Code code as follows:

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)

Join with left.

Five. Cross Join

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

The product of Descartes (Descartes) is also called direct product. Suppose set a={a,b}, set b={0,1,2}, then the Cartesian product of two sets is {(a,0), (a,1), (a,2), (b,0), (b,1), (b,2)}. A condition that can be extended to multiple collections. Similarly, if a represents a set of students in a school, and b represents a collection of all courses in the school, the Cartesian product of A and B represents all possible elective courses.

Copy Code code as follows:

Mysql> SELECT * from A cross join B;
+----+-----------+----+-------------+
| ID | name | 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 |
+----+-----------+----+-------------+
Rows in Set (0.00 sec)

#再执行:mysql> SELECT * from A inner join B; Give it a try.

#在执行mysql > select * from A Cross join B on a.name = B.name; Give it a try.

In fact, in MySQL (MySQL only) the CROSS join behaves the same as the INNER join, and the result of not specifying on conditions is Cartesian product, which results in an exact match of two tables. The INNER join and the CROSS join can omit the INNER or CROSS keyword, so the following SQL effect is the same:

Copy Code code as follows:

... From table1 INNER JOIN table2
... From table1 CROSS JOIN table2
... From table1 JOIN table2

Six. Full Join

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

Full connection produces all records (both sides match records) in table A and table B. If there is no match, the opposite will contain null.

Seven. Performance optimization
1. Display (Explicit) inner join VS implicit (implicit) inner JOIN

Such as:

Copy Code code as follows:

SELECT * FROM
Table A INNER JOIN table B
on a.id = b.ID;

Vs

Copy Code code as follows:

Select A.*, b.*
From table A, table B
where a.id = b.ID;

I compare (10w data) in a database, they are almost the same, the first is the inner join that is displayed, and the latter is an implicit inner join.

2.left join/right join VS INNER JOIN

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

When you use a LEFT join (or right join), you should be aware of the following points:
(1). On and where in the order of execution

The on condition (on in a left JOIN B in condition expression) is used to determine how to retrieve rows of data from table B. If no row of data in table B matches the condition on, a row of all columns of NULL is generated, and the condition of the WHERE clause in the match phase is not used. The WHERE clause condition is used only after the match phase completes. It retrieves the filter from the data generated during the match phase.

So we should note that when using the left (right) join, we must first give as many matches as possible to meet the conditions and reduce the execution of the Where. Such as:

Pass

Copy Code code as follows:

SELECT * FROM A
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 Code code as follows:

SELECT * FROM A
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

As can be seen from the example above, the condition of On is met as much as possible, and the condition of where is less used. In terms of performance, the second is obviously more time-saving.

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

As the author cited an example:

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

From the above, the first query uses the on condition to determine that all rows of data from the Product_details table in the left join are retrieved. The second query makes a simple left join, and then uses the WHERE clause to filter out data rows that do not meet the criteria from the data in the left join.

(3). Try to avoid subqueries while using join

Often the performance of this thing, more often reflected in the amount of data is relatively large, at this time, we should avoid complex subqueries. As follows:

Pass

Copy Code code as follows:

Insert into T1 (A1) Select B1 to T2 where NOT exists (select 1 from t1 where t1.id = t2.r_id);

Great

Copy Code code as follows:

Insert into T1 (A1)
Select B1 from T2
Left join (SELECT distinct t1.id to T1) t1 on t1.id = t2.r_id
where t1.id is null;