A subquery in MySQL is a query that is embedded inside another query. The subquery is used to retrieve data that will be used in the main query as a condition to filter the results.
Here’s an example to demonstrate how to use a subquery in MySQL:
Assume we have two tables, employees and departments. The employees table has columns id, name, age, salary, and department_id, while the departments table has columns id and name. The department_id column in the employees table is a foreign key that references the id column in the departments table.
Using a MySQL subquery in the WHERE clause
Now, suppose we want to find all employees who work in the ‘Sales’ department. We can achieve this using a subquery as follows:
SELECT id, name, age, salary FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales');
In this example, the subquery is (SELECT id FROM departments WHERE name = 'Sales'). This subquery is used to retrieve the id of the ‘Sales’ department from the departments table.
The main query then uses the subquery’s result as a condition to filter the results of the employees table. The WHERE clause in the main query compares the department_id column in the employees table with the result of the subquery. This ensures that only employees who work in the ‘Sales’ department are selected.
MySQL subquery with comparison operators
In MySQL, subqueries can also be used with comparison operators such as =, >, <, >=, and <= to compare the results of a subquery with a value or expression in the main query.
Here’s an example to demonstrate how to use a subquery with comparison operators:
Assume we have a table named employees with columns id, name, age, salary, and department_id. We want to retrieve the names of all employees who earn a higher salary than the average salary of all employees in the same department. We can achieve this using a subquery with a comparison operator as follows:
SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id);
In this example, the subquery (SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id) is used to retrieve the average salary of all employees in the same department as the employee in the main query.
The main query then uses the > operator to compare the salary of each employee with the average salary of all employees in the same department. Only employees with a higher salary than the average salary in their department are selected.
MySQL subquery with IN and NOT IN operators
In MySQL, the IN and NOT IN operators are used in subqueries to filter results based on a set of values.
Here’s an example to demonstrate how to use the IN and NOT IN operators in subqueries:
Assume we have a table named orders with columns id, customer_name, and order_amount. We want to retrieve the names of all customers who have placed orders with a total amount greater than $500. We can achieve this using a subquery with the IN operator as follows:
SELECT DISTINCT customer_name FROM orders WHERE id IN (SELECT id FROM orders GROUP BY id HAVING SUM(order_amount) > 500);
In this example, the subquery (SELECT id FROM orders GROUP BY id HAVING SUM(order_amount) > 500) is used to retrieve the id of all orders with a total amount greater than $500.
The main query then uses the IN operator to filter the results of the orders table based on the results of the subquery. The DISTINCT keyword is used to remove duplicate customer names from the results.
Now, suppose we want to retrieve the names of all customers who have not placed orders with a total amount greater than $500. We can achieve this using a subquery with the NOT IN operator as follows:
SELECT DISTINCT customer_name FROM orders WHERE id NOT IN (SELECT id FROM orders GROUP BY id HAVING SUM(order_amount) > 500);
In this example, the subquery (SELECT id FROM orders GROUP BY id HAVING SUM(order_amount) > 500) is used to retrieve the id of all orders with a total amount greater than $500.
The main query then uses the NOT IN operator to filter the results of the orders table based on the results of the subquery. The DISTINCT keyword is used to remove duplicate customer names from the results.
Note that subqueries with comparison operators can also be used in other parts of a SQL statement, such as the SELECT clause and WHERE clause. The key is to remember that the subquery must return a single value to be used in the main query for comparison.
