Mysql join syntax parsing and performance analysis

Original: Mysql join syntax parsing and performance analysis

A Join Syntax Overview

Joins are used for the connection between fields in multiple tables, with the following syntax:

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

Table1: Left table; table2: Right table.

JOIN is broadly divided into three categories as follows:

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

Left join: Gets the left table (table1) full record, that is, the right table (table2) has no corresponding matching record.

Right join: In contrast to the left join, get the full record of the right table (table2), which is the Table1 with no matching records.

Note:MySQL does not support full join, but you can use the Union keyword to merge the LEFT join with the right join to simulate full join.

Next, a column is given to explain the following categories. 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)

Two. Inner Join

Inner joins, also known as equivalent connections, inner join produces a set 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  |+----+--------+----+--------+

Three. Left Join

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 left outer join: equivalent in MySQL, we recommend using the left join. A left connection from the left table (A) produces a complete set of records, with matching records (right table (B)). If there is no match, the right side will contain null.

If you want to generate only a set of records from the left table (A), but not the records of the right table (B), you can do so by setting the where 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)

Similarly, you can simulate inner join. 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)

Differential set:

According to the above example, the difference set can be obtained as follows:

SELECT * FROM A LEFT JOIN B ON A.name = B.nameWHERE B.id IS NULLunionSELECT * FROM A right JOIN B ON A.name = B.nameWHERE A.id IS NULL;# 结果    +------+-----------+------+-------------+| id   | name      | id   | name        |+------+-----------+------+-------------+|    2 | Monkey    | NULL | NULL        ||    4 | Spaghetti | NULL | NULL        || NULL | NULL      |    1 | Rutabaga    || NULL | NULL      |    3 | Darth Vader |+------+-----------+------+-------------+

Four. 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.

Five. Cross Join

Cross join: The resulting result is a product of two tables, the Cartesian product

The product of Descartes (Descartes) is also called direct product. Assuming collection a={a,b}, set b={0,1,2}, the Cartesian product of two sets is {(a,0), (a,1), (a,2), (b,0), (b,1), (b,2)}. Can be extended to multiple collections of cases. Similarly, if a represents a collection 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.

  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 |+----+-----------+----+-------------+16 rows in Set (0.00 sec) #再执行:mysql> SELECT * from A inner join B; Try # In the execution 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 is the same as the INNER join , and the result of not specifying an on condition is a Cartesian product, which in turn results in an exact match of two tables. INNER Join and Cross join can omit the INNER or cross keyword, so the following SQL effect is the same:

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

Six. 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 the full connection (two match records) are 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:

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 compare (10w data) in the database, they are almost the same time, the first is to display the inner join, the latter is an implicit inner join.

Reference: Explicit vs implicit SQL joins

2.left join/right join VS INNER JOIN

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

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

(1). On and where execution order

The On condition ("a left JOIN B in conditional expression" on) is used to determine how data rows are retrieved from table B. If there are no rows in table B that match the on condition, an additional row will be generated for all columns of NULL, and the conditions of the WHERE clause in the match phase are not used. The WHERE clause condition is used only after the match phase is complete. It retrieves the filter from the data that is produced during the matching phase.

So we should note that when using the left (right) join, it is important to give as many matches as possible to satisfy the condition and reduce the where execution. such as:

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

As can be seen from the above example, the conditions of the on are satisfied as much as possible, and the Where condition is less. From the performance point of view the second is obviously more time-saving.

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

As the author gave an example:

  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_det Ails 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 that are compliant are retrieved from the Product_details table of the left join. The second query makes a simple left join, and then uses the WHERE clause to filter out non-qualifying rows of data from the data in the left join.

(3). Avoid subqueries as much as possible, and use join

Often performance this thing, more time is reflected in the data volume is larger, at this time, we should avoid complex sub-query. As follows:

PASS

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;  

This can refer to MySQL exists and inner join and not exists with the left JOIN performance difference amazing

Eight. Reference:

A Visual explanation of SQL Joins

Five ways to improve SQL performance

Three points you may need to know about MySQL left JOIN