How to use subqueries in MySQL queries?

In MySQL, subqueries can be used in queries to achieve more complex query logic. A subquery is a query statement nested within the main query, typically used to retrieve results based on certain conditions in the main query, which are then used to filter or perform other operations in the main query.

Here is a simple example showing how to use a subquery in MySQL.

Assume we have two tables: students and grades, where the students table contains information about the students and the grades table contains the students’ grades. We want to retrieve the information of all students whose grades are greater than 80.

We can achieve this query using a subquery.

SELECT * 
FROM students 
WHERE id IN (
    SELECT student_id 
    FROM grades 
    WHERE score > 80
);

In the above query, first, all student_ids of students with scores greater than 80 are retrieved in the subquery, and then in the main query, the corresponding student information is obtained based on these student_ids.

In addition to being used in the WHERE clause, a subquery can also be used in the SELECT, FROM, and HAVING clauses. This allows for more flexibility in queries and enables more complex logic operations.