Advanced Queries
## Learning Objectives
- Master UNION and INTERSECT
- Use CASE expressions
- Work with window functions
- Understand Common Table Expressions
## UNION
### Combine Results
```sql
SELECT first_name, last_name
FROM employees
WHERE department = 'Sales'
UNION
SELECT first_name, last_name
FROM contractors
WHERE department = 'Sales';
```
### UNION ALL
```sql
-- Includes duplicates
SELECT department FROM employees
UNION ALL
SELECT department FROM contractors;
```
### UNION vs UNION ALL
| Type | Duplicates | Performance |
|------|------------|-------------|
| UNION | Removed | Slower |
| UNION ALL | Kept | Faster |
## INTERSECT
### Common Rows Only
```sql
SELECT email FROM employees
INTERSECT
SELECT email FROM contractors;
```
## EXCEPT / MINUS
### Difference
```sql
-- Rows in first, not in second
SELECT email FROM employees
EXCEPT
SELECT email FROM contractors;
```
## CASE Expression
### Simple CASE
```sql
SELECT
first_name,
salary,
CASE department
WHEN 'Sales' THEN salary * 1.10
WHEN 'Engineering' THEN salary * 1.15
ELSE salary
END AS adjusted_salary
FROM employees;
```
### Searched CASE
```sql
SELECT
first_name,
salary,
CASE
WHEN salary < 50000 THEN 'Low'
WHEN salary >= 50000 AND salary < 80000 THEN 'Medium'
WHEN salary >= 80000 THEN 'High'
ELSE 'Unknown'
END AS salary_bracket
FROM employees;
```
### CASE in WHERE
```sql
SELECT *
FROM employees
WHERE CASE
WHEN department = 'Sales' THEN salary > 50000
WHEN department = 'Engineering' THEN salary > 70000
ELSE TRUE
END;
```
### CASE for Pivot
```sql
SELECT
SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active,
SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive
FROM accounts;
```
## Window Functions
### OVER Clause
```sql
SELECT
first_name,
salary,
AVG(salary) OVER () AS avg_salary
FROM employees;
```
### PARTITION BY
```sql
SELECT
department,
first_name,
salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;
```
### ROW_NUMBER
```sql
SELECT
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank,
first_name,
salary
FROM employees;
```
### RANK and DENSE_RANK
```sql
SELECT
RANK() OVER (ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank,
first_name,
salary
FROM employees;
```
### LAG and LEAD
```sql
SELECT
first_name,
hire_date,
LAG(hire_date) OVER (ORDER BY hire_date) AS prev_hire,
LEAD(hire_date) OVER (ORDER BY hire_date) AS next_hire
FROM employees;
```
### FIRST_VALUE and LAST_VALUE
```sql
SELECT
department,
first_name,
salary,
FIRST_VALUE(salary) OVER (
PARTITION BY department
ORDER BY hire_date
) AS first_salary_in_dept
FROM employees;
```
## Common Table Expressions (CTE)
### Basic CTE
```sql
WITH dept_avg AS (
SELECT
department,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department
)
SELECT
e.first_name,
e.salary,
d.avg_salary
FROM employees e
JOIN dept_avg d ON e.department = d.department
WHERE e.salary > d.avg_salary;
```
### Multiple CTEs
```sql
WITH
high_earners AS (
SELECT *
FROM employees
WHERE salary > 80000
),
recent_hires AS (
SELECT *
FROM employees
WHERE hire_date > '2023-01-01'
)
SELECT *
FROM high_earners
INTERSECT
SELECT *
FROM recent_hires;
```
### Recursive CTE
```sql
-- Organizational hierarchy
WITH RECURSIVE org_chart AS (
SELECT
employee_id,
first_name,
manager_id,
1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT
e.employee_id,
e.first_name,
e.manager_id,
oc.level + 1
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart;
```
## Conditional Aggregation
### SUM/CASE Pattern
```sql
SELECT
COUNT(*) AS total,
SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending
FROM orders;
```
### AVG with Condition
```sql
SELECT
AVG(CASE WHEN department = 'Sales' THEN salary END) AS avg_sales_salary,
AVG(CASE WHEN department = 'Engineering' THEN salary END) AS avg_eng_salary
FROM employees;
```
## PIVOT / Cross Tab
### MySQL PIVOT
```sql
SELECT
SUM(CASE WHEN quarter = 'Q1' THEN sales END) AS Q1,
SUM(CASE WHEN quarter = 'Q2' THEN sales END) AS Q2,
SUM(CASE WHEN quarter = 'Q3' THEN sales END) AS Q3,
SUM(CASE WHEN quarter = 'Q4' THEN sales END) AS Q4
FROM quarterly_sales;
```
### SQL Server PIVOT
```sql
SELECT *
FROM (SELECT year, quarter, sales) AS src
PIVOT (SUM(sales) FOR quarter IN ([Q1], [Q2], [Q3], [Q4])) AS pvt;
```
## Summary
- **UNION/UNION ALL**: Combine result sets
- **INTERSECT/EXCEPT**: Set operations
- **CASE**: Conditional logic in queries
- **Window functions**: LAG, LEAD, ROW_NUMBER, RANK, SUM/AVG over partitions
- **CTE**: Named subqueries with WITH clause
- **Recursive CTE**: For hierarchical data
Comments
Comments powered by Giscus
To enable comments, add your Giscus embed code here.
Learn more about Giscus →