Using Join of join to Replace Subqueries in MySQL Optimization

Use join (JOIN) instead of subquery (Sub-Queries)

MySQL supports SQL subqueries from 4.1. This technique can use the SELECT statement to create a single-column query result, and then use this result as a filter condition in another query. For example, if we want to delete customers without any orders from the customer base information table, we can use the subquery to first fetch all customers ID who placed orders from the sales information table, and then pass the results to the main query, as follows:

DELETE FROM customerinfo 
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo ) 

Using subqueries, many SQL operations that logically require multiple steps can be completed at one time, and transaction or table locking can be avoided, and it is easy to write. However, in some cases, subqueries can be replaced by more efficient joins (JOIN).. For example, suppose we want to take out all users without order records, which can be done with the following query:

SELECT * FROM customerinfo 
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo ) 

If you use join (JOIN).. to complete this query, it will be much faster. Especially when the salesinfo table is indexed to CustomerID, the performance will be better. The query is as follows:

SELECT * FROM customerinfo 
LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo. 
CustomerID 
WHERE salesinfo.CustomerID IS NULL 

Join (JOIN).. is more efficient because MySQL does not need to create a temporary table in memory to complete this logically two-step query

Summarize

This article about MySQL optimization using join (join) instead of subquery content here, for reference only. If there are any deficiencies, please point out them. Welcome to exchange and discuss. Thank you for your support to this site.