Sub Query Basics

Oracle Subquery Tutorial

A concise, copy‑paste friendly guide for Blogger. Includes single‑row, multi‑row, correlated, inline view, and scalar subqueries with ready SQL snippets.

What is a Subquery?

A subquery (inner or nested query) is a SQL query used inside another SQL statement. Use it when one query’s result is needed by another. Subqueries can appear in SELECT, FROM, WHERE/HAVING, and in INSERT/UPDATE/DELETE.

Basic Syntax

SELECT column1, column2
FROM table1
WHERE column3 = (
  SELECT columnX
  FROM table2
  WHERE condition
);

Types of Subqueries

1) Single‑row Subquery

Returns exactly one row. Use comparison operators like =, <, >, <=, >=.

SELECT employee_id, first_name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
);

2) Multi‑row Subquery

Returns multiple rows. Use IN, ANY, or ALL.

SELECT employee_id, first_name, department_id
FROM employees
WHERE department_id IN (
  SELECT department_id
  FROM departments
  WHERE location_id = (
    SELECT location_id
    FROM locations
    WHERE city = 'NEW YORK'
  )
);

3) Correlated Subquery

The inner query references a column from the outer query. It runs once per row of the outer query.

SELECT e.employee_id, e.first_name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department_id = e.department_id
);

4) Inline View (Subquery in FROM)

Use a subquery as a temporary result set. Handy for ROWNUM or FETCH pagination.

SELECT *
FROM (
  SELECT employee_id, first_name, salary
  FROM employees
  ORDER BY salary DESC
)
WHERE ROWNUM <= 3;

5) Scalar Subquery

Returns a single value (one row, one column). Often used directly in the SELECT list.

SELECT first_name, salary,
       (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;

Rules for Subqueries

  • Must be enclosed in parentheses.
  • Can return a single value, a single row, multiple rows, or a table (inline view).
  • ORDER BY is not allowed directly inside subqueries except in inline views with ROWNUM/FETCH.
  • Correlated subqueries run per row, so watch performance.

কোন মন্তব্য নেই:

একটি মন্তব্য পোস্ট করুন

এতে সদস্যতা: পোস্টগুলি (Atom)