mysql optimize join statement

Optimize the join statement:
Mysql4.1 begins to support SQL subqueries. This technique allows you to use a SELECT statement to create a single column of query results, and then use this result as a filter condition in another query. Using subqueries can complete many SQL operations that logically require multiple steps to complete at one time. It can also avoid transaction or table locks, and it is also easy to write. However, in some cases, subqueries can be replaced by more efficient joins (JOIN)..
.
Suppose we want to extract all users who do not have order records, we can use the following query to complete this query:
SELECT * FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo)
If you use a connection (JOIN)... to complete this query , the speed will be much faster. Especially if 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
Connection (JOIN). . The reason why it is more efficient is that MySQL does not need to create a temporary table in memory to complete this logical two-step query.

The above is the content of mysql optimized join statement. For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!