Subqueries in MySQL: Types and Usage

Scalar Subquery

SELECT name 
FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

Inline Subquery

SELECT dept, avg_sal 
FROM (SELECT dept, AVG(salary) AS avg_sal FROM employees GROUP BY dept) AS stats;

Correlated Subquery

SELECT name 
FROM employees e 
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = e.department);