MySQL optimization uses join instead of subquery and mysqljoin

MySQL optimization uses join instead of subquery and mysqljoin

Use 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 you want to delete a customer who has no orders in the basic customer information table, you can use the subquery to retrieve the customer IDs of all orders from the sales information table, then pass the result to the primary query, as shown below:

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

Subqueries can be used to complete SQL operations that require multiple logical steps at a time. At the same time, transactions or tables can be prevented from being locked and can be easily written. However, in some cases, subqueries can be replaced by more efficient JOIN. For example, if we want to retrieve all users without order records, we can use the following query:

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

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

Summary

This article describes how to use join to replace subqueries for MySQL optimization. If there are any deficiencies, please point out. You are welcome to discuss it. Thank you for your support.