Oracle SQL Course
Week 3 - Lesson 2
Subqueries (Single Row, Multi Row, Correlated, EXISTS)
📌 What is Subquery?
Subquery is a query inside another query. It is used to return data that will be used in the main query condition.
📌 Types of Subqueries
- Single Row Subquery (returns one value)
- Multiple Row Subquery (returns multiple values)
- Correlated Subquery (depends on outer query)
🔥 Single Row Subquery
Returns only one value and uses operators like (=, >, <)
-- Get employees who earn more than Fox
SELECT *
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Fox'
);
🔥 Multiple Row Subquery
Returns multiple values and uses IN, ANY, ALL
-- Employees with same job as Grant
SELECT *
FROM employees
WHERE job_id IN (
SELECT job_id
FROM employees
WHERE last_name = 'Grant'
);
-- Salary greater than ANY salary of Grant
SELECT *
FROM employees
WHERE salary > ANY (
SELECT salary
FROM employees
WHERE last_name = 'Grant'
);
-- Salary greater than ALL salaries of Grant
SELECT *
FROM employees
WHERE salary > ALL (
SELECT salary
FROM employees
WHERE last_name = 'Grant'
);
🔥 Correlated Subquery
This subquery depends on the outer query and executes once for each row.
-- Get lowest salary in each department
SELECT *
FROM employees e
WHERE salary = (
SELECT MIN(salary)
FROM employees
WHERE department_id = e.department_id
);
🔥 EXISTS
Checks if subquery returns any result
-- Employees who have colleague with same salary
SELECT *
FROM employees e
WHERE EXISTS (
SELECT 1
FROM employees
WHERE department_id = e.department_id
AND salary = e.salary
AND employee_id != e.employee_id
);
🔥 NOT EXISTS
-- Employees who are unique in salary
SELECT *
FROM employees e
WHERE NOT EXISTS (
SELECT 1
FROM employees
WHERE department_id = e.department_id
AND salary = e.salary
AND employee_id != e.employee_id
);
🔥 Advanced Example
-- Employee who is alone in department
SELECT *
FROM employees e
WHERE 1 = (
SELECT COUNT(*)
FROM employees
WHERE department_id = e.department_id
);
🔥 More Examples
-- Employees hired after Davies
SELECT *
FROM employees
WHERE hire_date > (
SELECT hire_date
FROM employees
WHERE last_name = 'Davies'
);
-- Employees with highest salary per job
SELECT *
FROM employees e
WHERE salary = (
SELECT MAX(salary)
FROM employees
WHERE job_id = e.job_id
);
💪 Tasks
- Find employees who earn more than average salary
- Find employees who have same salary as someone else
- Find employees who are the only one in their department
- Find employees who earn more than ALL employees in same job
-- Task Example
SELECT *
FROM employees e
WHERE salary > ALL (
SELECT salary
FROM employees
WHERE job_id = e.job_id
);
🎯 Interview Notes
- EXISTS is faster than IN in large datasets
- Correlated Subquery is slower but powerful
- Use ALL when comparing with all values
- Use ANY when comparing with any value