Subqueries(nested queries) are queries embedded within another SQL query. They can be used in the SELECT, FROM, WHERE, and HAVING clauses, providing flexibility for sophisticated data manipulation. Subqueries execute first, and their results are then used by the outer query.

Single-Row Subqueries: Returning one row, one column (used with =, !=, >, <)

A single-row subquery returns a maximum of one row and one column. These are typically used with single-row comparison operators such as =, !=, >, <, >=, and <=. If a single-row subquery returns more than one row, an error will occur.

Example 1: Oracle 19c SQL Single-Row Subquery with EQUAL operator

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees); -- Finds the minimum salary and returns employees with that salary

Explanation This query finds employees whose salary matches the minimum salary in the employees table. The subquery determines the lowest salary, and the outer query filters based on this single value.

Example 2: Oracle 19c SQL Single-Row Subquery with GREATER THAN operator

SELECT product_id, product_name, list_price
FROM products
WHERE list_price > (SELECT AVG(list_price) FROM products); -- Retrieves products with a list price greater than the average list price

Explanation This example identifies products with a list_price higher than the average list_price of all products. The subquery calculates the average, which the outer query then uses for filtering.

Example 3: Oracle 19c SQL Single-Row Subquery with NOT EQUAL operator

SELECT department_id, department_name
FROM departments
WHERE department_id != (SELECT department_id FROM employees WHERE first_name = 'Steven' AND last_name = 'King'); -- Excludes the department where Steven King works

Explanation This query lists all departments, excluding the one where 'Steven King' works. The subquery identifies Steven King's department, which the outer query then excludes.

Multiple-Row Subqueries: Returning multiple rows, one column (used with IN, ANY, ALL)

Multiple-row subqueries return one or more rows, but only one column. These subqueries are typically used with multiple-row comparison operators such as IN, NOT IN, ANY, and ALL. Attempting to use a single-row operator with a multiple-row subquery will result in an error.

Example 1: Oracle 19c SQL Multiple-Row Subquery with IN operator

SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700); -- Selects employees from departments located at location_id 1700

Explanation This query retrieves employees from departments located at location_id 1700. The subquery returns a list of department IDs, and the IN operator checks for matches.

Example 2: Oracle 19c SQL Multiple-Row Subquery with ANY operator

SELECT product_id, product_name, list_price
FROM products
WHERE list_price > ANY (SELECT list_price FROM products WHERE category_id = 5); -- Finds products with a list price greater than at least one product in category_id 5

Explanation This example finds products whose list_price is greater than any list_price from products in category_id 5. The ANY operator ensures the condition is met if the price is greater than the smallest value returned by the subquery.

Example 3: Oracle 19c SQL Multiple-Row Subquery with ALL operator

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG'); -- Returns employees whose salary is greater than all salaries of IT programmers

Explanation This query identifies employees whose salary is strictly greater than all salaries of 'IT_PROG' employees. The ALL operator ensures the salary must exceed the maximum value returned by the subquery.

Multiple-Column Subqueries: Returning multiple columns in WHERE clause

Multiple-column subqueries return more than one column in the WHERE clause of the outer query. These are particularly useful when you need to compare multiple columns simultaneously.

Example 1: Oracle 19c SQL Multiple-Column Subquery for paired comparison

SELECT employee_id, first_name, last_name, job_id, department_id
FROM employees
WHERE (job_id, department_id) IN (SELECT job_id, department_id FROM employees WHERE employee_id = 100); -- Finds employees with the same job_id and department_id as employee 100

Explanation This query finds employees with the exact same job_id and department_id as employee 100. The IN operator uses a paired column comparison.

Example 2: Oracle 19c SQL Multiple-Column Subquery with MAX values

SELECT product_id, product_name, category_id, list_price
FROM products
WHERE (category_id, list_price) IN (SELECT category_id, MAX(list_price) FROM products GROUP BY category_id); -- Finds the most expensive product in each category

Explanation This example identifies the product(s) with the highest list_price within each category_id. The subquery returns each category's max price, which the outer query matches.

Example 3: Oracle 19c SQL Multiple-Column Subquery for non-matching pairs

SELECT employee_id, first_name, last_name, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) NOT IN (SELECT manager_id, department_id FROM employees WHERE salary > 15000); -- Finds employees whose manager_id and department_id pair do not exist for highly paid employees

Explanation This query retrieves employees whose manager_id and department_id combination is not found among highly paid employees. The NOT IN clause excludes specific manager-department pairs.

Correlated Subqueries: Subquery dependent on outer query

A correlated subquery is a subquery that depends on the outer query for its values. It executes once for each row processed by the outer query. This means the inner query cannot be executed independently of the outer query. Correlated subqueries are typically used for row-by-row processing.

Example 1: Oracle 19c SQL Correlated Subquery to find employees earning more than their department's average

SELECT e.employee_id, e.first_name, e.last_name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id); -- For each employee, compares their salary to the average salary of their own department

Explanation This query finds employees earning more than their own department's average salary. The subquery is correlated as it recalculates the average for each employee's department.

Example 2: Oracle 19c SQL Correlated Subquery to find departments with at least one employee earning above a certain salary

SELECT d.department_name
FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.department_id AND e.salary > 10000); -- Checks if there's any employee in the department with salary > 10000

Explanation This example lists departments with at least one employee earning over 10000. The EXISTS operator, with the correlated subquery, checks for such an employee in each department.

Example 3: Oracle 19c SQL Correlated Subquery for finding duplicate names within a department

SELECT e1.first_name, e1.last_name, e1.department_id
FROM employees e1
WHERE (SELECT COUNT(*) FROM employees e2 WHERE e2.first_name = e1.first_name AND e2.last_name = e1.last_name AND e2.department_id = e1.department_id) > 1; -- Finds employees with non-unique first name, last name, and department combinations

Explanation This query identifies employees with duplicate first_name, last_name, and department_id combinations. The correlated subquery counts occurrences, highlighting duplicates.

Scalar Subqueries: Subquery returning a single value, usable in SELECT or WHERE

A scalar subquery is a subquery that always returns at most one row and one column. Because it returns a single value, it can be used in almost any place where a single expression is valid, including the SELECT list, WHERE clause, GROUP BY clause, and HAVING clause.

Example 1: Oracle 19c SQL Scalar Subquery in SELECT clause

SELECT d.department_name,
       (SELECT COUNT(e.employee_id) FROM employees e WHERE e.department_id = d.department_id) AS total_employees -- Counts employees for each department
FROM departments d;

Explanation This query lists department names with their total employee count. The scalar subquery in the SELECT list calculates the count for each department.

Example 2: Oracle 19c SQL Scalar Subquery in WHERE clause for comparison

SELECT product_id, product_name, list_price
FROM products
WHERE list_price > (SELECT MAX(list_price) * 0.8 FROM products WHERE category_id = 1); -- Finds products with a list price greater than 80% of the maximum price in category 1

Explanation This example retrieves products whose list_price is greater than 80% of the maximum price in category_id 1. The scalar subquery calculates this single threshold value for filtering.

Example 3: Oracle 19c SQL Scalar Subquery for dynamic column values

SELECT employee_id, first_name, last_name,
       (SELECT department_name FROM departments WHERE department_id = e.department_id) AS employee_department -- Retrieves the department name for each employee
FROM employees e;

Explanation This query lists employee details along with their department names. The scalar subquery in the SELECT list fetches the department_name for each employee's department.