MySQL Join essentials

Basic concepts:

The difference between inner and outer joins is that the inner join will remove all non-conforming records, while the outer joins retain some of them. The difference between a left and right junction is that if you use a left junction B then all the records in a will remain in the result, and at this point B only records that match the junction condition, and the right junction is the opposite, it will not be confused.

Subtraction

Selecttable1.* fromtable1 Left Jointable2 using (ID)wheretable2.id is NULL

Intersection:

Selecttable1.* fromtable1 Left Jointable2 using (ID)

Performance:

1: Display (Explicit) inner join VS implicit (implicit) inner JOIN

SELECT * FROM Table a inner joins table B on a.id = b.ID;

Vs

Select A.*, b.* from table A, table B where 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.

2:left join/right Join VS INNER JOIN use inner join as much as possible. Avoid left joins and NULL.

3: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. On will retrieve the filter from the data generated during the match 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

The code is 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

The code is 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 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.

4: Note the difference between the ON clause and the WHERE clause

The code is as follows:

SELECT * FROM product left JOIN Product_details

On (product.id = product_details.id)

and product_details.id=2;

SELECT * FROM product left JOIN Product_details

On (product.id = product_details.id)

WHERE product_details.id=2;

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.

5: Try to avoid subqueries, 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

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;

MySQL Join essentials