Subquery in MySQL

Subquery in MySQL means Query inside another query on SELECT, INSERT, UPDATE or DELETE.

A subquery must be enclosed with parentheses and can be used anywhere in an expression.

The main advantages of subqueries is that it allow queries that are structured so that, it is possible to isolate each part of the statement and they provide an alternative ways to perform the operations that would otherwise require the complex joins and unions.

Most of the people find subqueries that are more readable than complex joins or unions.

Example of Subquery:

SELECT * from users WHERE user_country IN (SELECT country_id FROM countries);

Few things you need to remember:

• Never use IN with a subquery as this is slow sometimes.
• Only ever use IN with a fixed list of values.

Read Also: MySQL Query Functions

More tips:

• If you want to make your query more faster, don’t run a SELECT *. Only select the fields that you really need.
• Remember to make an index on relevant_field to speed up the equi join.
• Make sure to group by on a primary key.
• If you are uning InnoDB storage engine and you only select indexed fields then MySQL will resolve the query using only the indexes, speeding things way up.