SQL Subqueries
 

Oracle SQL 

SQL Introduction    SQL Introduction 

SQL Insert    SQL Insert

SQL Update    SQL Update

SQL Delete    SQL Delete

SQL Joins   SQL Joins 

SQL Subqueries    SQL Subqueries

SQL Views    SQL Views

Oracle PL/SQL

PL/SQL Introduction    PL/SQL Introduction

PL/SQL Cursors    PL/SQL Cursors

PL/SQL Triggers    PL/SQL Triggers

PL/SQL Interview Questions    PL/SQL Questions

SQL – Subqueries

Subqueries are used in the WHERE clause of the SQL statement. When you nest many subqueries, the innermost query is evaluated first.

When you use subquery in the from clause of the select statement it is called inline view. A subquery which is enclosed in parenthesis in the FROM clause may be given an alias name. The columns selected in the subquery can be referenced in the parent query, just as you would select from any normal table or view.

You can nest any number of such queries; Oracle does not have a limit.

There is also another term used NESTED SUBQUERIES. when you use subqueries in the WHERE clause of the SELECT statement it is called nested subquery. There are only 255 levels of subqueries.

Correlated subquery:

When you reference a column from the table in the parent query in the subquery, it is known as a correlated subquery. For each row processed in the parent query, the correlated subquery is evaluated once.

Example

SELECT * from dept where exists ( select null) from emp
where emp.deptno = DEPT.DEPTNO )

A scalar subquery returns a single row and a single column value.

SUBQUERIES can be single row subquery, multiple row subquery, single column and multiple column.

Single Row Subqueries:

Example:

Find the highest salary :

SELECT last_name, salary
FROM employees
WHERE salary = (Select MAX(salary) FROM employees);

Multiple Row Subqueries:

Example:

Find the list of all employees who work in the same dept as williams does:

SELECT last_name, department_id
FROM employees
WHERE department_id IN (SELECT department_id
FROM employees
WHERE last_name = 'williams');

Example:

Find the highest paid employee in each department:

SELECT department_id, last_name, salary
FROM employees e1
WHERE salary = (SELECT MAX(salary)
FROM employees e2
WHERE e1.department_id = e2.department_id);