MySQL optimization: use join instead of subquery

Use JOIN instead of sub-queries

MySQL supports SQL subqueries starting from version 4.1. This technique uses a SELECT statement to create a single-column query result, and then uses this result as a filter condition in another query. For example, if we want to delete customers who do not have any orders in the customer basic information table, we can use a subquery to first retrieve the customer IDs of all customers who have placed orders from the sales information table, and then pass the result to the main query, as shown below:

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

Using subqueries can complete many SQL operations that logically require multiple steps to complete at one time, while also avoiding transaction or table locks, and is also easy to write. However, in some cases, a subquery can be replaced by a more efficient JOIN.. For example, suppose we want to retrieve all users who have no order records, we can use the following query to complete:

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

If you use a JOIN to complete this query, the speed will be much faster. Especially when there is an index on CustomerID in the salesinfo table, 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.. is more efficient because MySQL does not need to create a temporary table in memory to complete this logical two-step query.

Summarize

This is the end of this article about MySQL optimization using joins instead of subqueries. It is for reference only. Please point out any deficiencies. Everyone is welcome to exchange ideas and discuss. Thank you friends for supporting this site.