The basic syntax of a subquery is:
SELECT columns
FROM table
WHERE column = (SELECT column FROM table WHERE condition);
SELECT: The main query.(SELECT ...): The subquery, nested inside the main query.WHERE: The condition for matching rows.Let’s assume we have two tables: employees and departments. We want to retrieve the names of employees who belong to the department with the highest salary. Here’s how you can use a subquery:
SELECT name
FROM employees
WHERE department_id = (
SELECT id
FROM departments
WHERE salary = (SELECT MAX(salary) FROM departments)
);
In this example:
SELECT MAX(salary) FROM departments) finds the highest salary in the departments table.SELECT id FROM departments WHERE salary = ...) retrieves the id of the department with the highest salary.department_id matches the department with the highest salary.SELECT, INSERT, UPDATE, and DELETE.Subqueries in MySQL provide a powerful way to handle complex queries by allowing you to use the results of one query within another. They simplify tasks like filtering, aggregating, and calculating values based on other data. Understanding how and when to use subqueries helps in writing efficient and effective SQL queries.