How to write Subqueries and Nested SELECT statements in MySQL?
Uses scalar subqueries, IN subqueries, and EXISTS correlated subqueries.
SQL Query Statement
-- Find employees earning above the company average salary
SELECT employee_id, first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- Correlated subquery using EXISTS
SELECT e.first_name, e.salary
FROM employees e
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.employee_id = e.employee_id
);Expected Result / Output
+-------------+------------+----------+ | employee_id | first_name | salary | +-------------+------------+----------+ | 4 | Carol | 75000.00 | +-------------+------------+----------+ 1 row in set (0.00 sec)