Subquery vs JOIN: when should you use each, and what is a correlated subquery?
Subquery vs JOIN: when should you use each, and what is a correlated subquery?
A subquery is a query nested inside another. A join combines tables side by side. Many problems can be solved either way; the choice depends on what you need in the output and on how many rows the inner side can return.
-- scalar subquery: one value
SELECT name, price, price - (SELECT AVG(price) FROM products) AS diff FROM products;
-- table (derived) subquery in FROM
SELECT d.department, d.avg_salary
FROM (SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department) d
WHERE d.avg_salary > 5000;
-- subquery in WHERE (semi-join)
SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders);
It refers to a column of the outer query, so conceptually it runs once per outer row:
-- employees paid above their own department's average
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(x.salary) FROM employees x WHERE x.department = e.department);
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.