Advanced SQL Queries
SQL Basics Course
Topic 13: Advanced SQL Queries
Master powerful SQL techniques used in professional applications and reporting systems.
Learning Objectives
- Understand Subqueries
- Use EXISTS and NOT EXISTS
- Learn ANY and ALL
- Use CASE statements
- Combine results using UNION
- Understand basic query optimization
Subqueries
A subquery is a query inside another query.
SELECT name
FROM employees
WHERE salary >
(
SELECT AVG(salary)
FROM employees
);
This returns employees earning more than the average salary.
Nested Queries
SELECT *
FROM students
WHERE course_id IN
(
SELECT course_id
FROM courses
WHERE duration > 6
);
The inner query executes first and its result is used by the outer query.
EXISTS
SELECT *
FROM customers c
WHERE EXISTS
(
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
Returns customers who have at least one order.
NOT EXISTS
SELECT *
FROM customers c
WHERE NOT EXISTS
(
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
Returns customers without orders.
ANY Operator
SELECT *
FROM employees
WHERE salary > ANY
(
SELECT salary
FROM employees
WHERE department='HR'
);
Condition must be true for at least one returned value.
ALL Operator
SELECT *
FROM employees
WHERE salary > ALL
(
SELECT salary
FROM employees
WHERE department='HR'
);
Condition must be true for every returned value.
CASE Statement
SELECT name,
CASE
WHEN salary >= 70000 THEN 'Senior'
WHEN salary >= 40000 THEN 'Mid Level'
ELSE 'Junior'
END AS employee_level
FROM employees;
CASE adds conditional logic to SQL queries.
UNION
SELECT city FROM customers
UNION
SELECT city FROM suppliers;
Combines results and removes duplicates.
UNION ALL
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;
Combines results and keeps duplicates.
UNION vs UNION ALL
| Feature | UNION | UNION ALL |
|---|---|---|
| Duplicates | Removed | Kept |
| Performance | Slower | Faster |
Basic Query Optimization
- Select only required columns.
- Avoid unnecessary joins.
- Use indexes.
- Limit returned rows.
- Optimize filtering conditions.
Real Business Example
SELECT department,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;
Find departments whose average salary exceeds 50,000.
Practice Exercises
- Create a subquery using AVG().
- Use EXISTS to find customers with orders.
- Use NOT EXISTS to find customers without orders.
- Create a CASE statement.
- Compare UNION and UNION ALL results.
Quiz
1. What is a subquery?
2. What does EXISTS check?
3. Difference between ANY and ALL?
4. What does CASE do?
5. Difference between UNION and UNION ALL?
Summary
- Subqueries allow nested logic.
- EXISTS checks for matching records.
- ANY and ALL compare against multiple values.
- CASE adds decision-making capability.
- UNION combines result sets.
- Optimization improves query performance.