MYSQL Join Syntax Performance optimization

Syntax for joins:

Table1 inner|left|right join table2 on condition

The difference between internal and external connection: the inner junction will remove all non-conforming condition records, the outer coupling will retain some records that do not conform to condition;

The left junction retains records from the left table table1, and the right table Table2 returns only records that match condition.

1,join Overview

Table1 inner|left|right join table2 on condition

INNER join: Inner junction, equivalent junction, gets the record ID of the two tables that match condition.

Left join: To get all records of table1 (the field in table1 condition may be empty), and table2 to match condition Records,

Right join: To get all the records of table2 (the field in table2 condition may be empty), and table1 to match condition Records,

2,inner Join

Inner coupling

SELECT * FROM A inner joins B on a.mobile = B.mobile and andcondition;

will return a.id is not empty, b.id is not empty, and A.mobile = B.mobile and andcondition-compliant data

3,left Join

SELECT * from A LEFT join B on a.mobile = B.mobile and andcondition;

All records of A and b.mobile = A.mobile of all B are returned.

If you want to get data that does not meet condition in table A

SELECT * FROM A

Left Join B on a.mobile = B.mobile

where b.is is null

Get

Simulate inner join with left join

-select * from A LEFT join B on a.mobile = b.mobile where b.id are NOT null;

Find a collection of the respective data in a B table that does not meet the condition criteria

-select * from A LEFT join B on a.mobile = b.mobile where b.id is null

Union

SELECT * from A right join B in a.mobile = b.mobile where a.id is null

Get differential data (data for two tables that do not conform to condition)

　　

4,right Join

SELECT * from A right B on a.mobile = B.mobile;

Will get all the data from table B and the data in table A that meet condition

5,cross Join

Cross join, resulting in the product of two tables

In MySQL (MySQL only) Cross join and inner join behave the same. The Cartesian product is obtained when the on condition is not specified.

So the following three statements have the same effect

->...from A INNER JOIN B

->...from A Cross Join B

->...from A Join B

6,full Join

-select * from A LEFT join B on a.mobile = B.mobile;

Union

SELECT * from A right join B on a.mobile = B.mobile;

Get

7, performance optimization

(1) Show inner join and implicit inner join

Display--select * from A inner join B on a.mobile = B.mobile;

Implicit---select * from A inner join B where a.mobile = B.mobile;

100,000 the query time of the data is almost equal.

(2) left Join/right join and inner JOIN

Try to avoid outer joins and null with inner JOIN

When using an outer join, such as SELECT * from A LEFT join B on a.mobile = b.mobile where wherecondition;

If the on condition condition is not met in B, a row of all data that is listed as NULL is generated.

In the on condition matching phase, the Where condition is not used. At the end of on condition, where will be used and the Where condition will be retrieved again from the data that satisfies the on condition.

Therefore, in the use of Outer Junction City, we should try to give as many matching conditions as possible (that is, on condition), reduce the where sentence retrieval.

SQL, select * from A is not recommended

Left Join B on a.mobile = B.mobile

Left Join C on a.name = C.name

where A.status = 1 and c.status = 1

Recommended SQL-select * from A

Left Join B on a.mobile = b.mobile and a.status = 1

Left Join C on a.name = c.name and c.status = 1

Try to satisfy the on condition, and use the Where condition sparingly.

(3) difference between on condition and where condition

->select * from A left join B on a.mobile = b.mobile on a.name are NOT null;

All records of Table A and the records in table B that meet (a.mobile = B.mobile on a.name are NOT null) will be returned;

->select * from A left join B on a.mobile = b.mobile where a.name are NOT null;

All records in table A and the records in table B that meet (A.mobile = B.mobile) are returned, and the results are filtered by the Where condition (A.name is not null).

Number of result set bars returned by the first SQL statement >= second SQL

(4) Try to avoid subqueries, and use join

MYSQL Join Syntax Performance optimization