Find top SQL resources from global universities, cool projects, and learning materials for data analytics.
Admin: @coderfun
Useful links: heylink.me/DataAnalytics
Promotions: @love_data
Post #2771
1K
Means: Return customers for whom no matching order exists.
๐ง 12. EXISTS vs IN
๐ 13. Correlated Subquery
Depends on the current row of the outer query.
ยซIs this employee's salary higher than the average salary of their own department?ยป
๐ข 14. Above-Department-Average Salary
This is a correlated subquery. Bob is compared against the Sales average, while David is compared against the IT average.
๐ฆ 15. Subquery in FROM
It can appear in the
This is often called a derived table.
๐ 16. Finding High-Value Customers
๐งฉ 17. Subquery in SELECT
โ ๏ธ 18. But Be Careful with Correlated Subqueries
Instead of a correlated subquery in
The better choice depends on the database optimizer, table size, indexes, and other factors.
๐งฑ 19. Nested Subqueries
Deeply nested queries can become difficult to read. Use CTEs instead.
๐ง 20. Subqueries vs JOINs
Subquery:
JOIN:
Choose based on readability, business logic, and performance.
๐ 21. Subqueries for Business Analysis
Find products above average:
Find customers with at least one order:
Find customers with more than 10 orders:
๐งฎ 22. Subquery for KPI Comparison
๐ง 12. EXISTS vs IN
IN โ Compare against a set of returned valuesEXISTS โ Check whether a matching row exists๐ 13. Correlated Subquery
Depends on the current row of the outer query.
SELECT
e.employee_name,
e.salary,
e.department_id
FROM employees e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department_id = e.department_id
);
ยซIs this employee's salary higher than the average salary of their own department?ยป
๐ข 14. Above-Department-Average Salary
This is a correlated subquery. Bob is compared against the Sales average, while David is compared against the IT average.
๐ฆ 15. Subquery in FROM
It can appear in the
FROM clause.SELECT
customer_id,
total_spending
FROM (
SELECT
customer_id,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id
) AS customer_sales;
This is often called a derived table.
๐ 16. Finding High-Value Customers
SELECT
customer_id,
total_spending
FROM (
SELECT
customer_id,
SUM(amount) AS total_spending
FROM orders
GROUP BY customer_id
) AS customer_sales
WHERE total_spending > 50000;
๐งฉ 17. Subquery in SELECT
SELECT
c.customer_name,
(
SELECT COUNT(*)
FROM orders o
WHERE o.customer_id = c.customer_id
) AS order_count
FROM customers c;
โ ๏ธ 18. But Be Careful with Correlated Subqueries
Instead of a correlated subquery in
SELECT, you could use:SELECT
c.customer_name,
COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY
c.customer_id,
c.customer_name;
The better choice depends on the database optimizer, table size, indexes, and other factors.
๐งฑ 19. Nested Subqueries
SELECT *
FROM products
WHERE price > (
SELECT AVG(price)
FROM products
WHERE category_id IN (
SELECT category_id
FROM categories
WHERE category_name = 'Electronics'
)
);
Deeply nested queries can become difficult to read. Use CTEs instead.
๐ง 20. Subqueries vs JOINs
Subquery:
SELECT *
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
);
JOIN:
SELECT DISTINCT
c.*
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id;
Choose based on readability, business logic, and performance.
๐ 21. Subqueries for Business Analysis
Find products above average:
SELECT product_name, price
FROM products
WHERE price > (
SELECT AVG(price)
FROM products
);
Find customers with at least one order:
SELECT customer_id, customer_name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
Find customers with more than 10 orders:
SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 10
);
๐งฎ 22. Subquery for KPI Comparison
SELECT order_id, customer_id, amount
FROM orders
WHERE amount > (
SELECT AVG(amount)
FROM orders
);
- โค 1
