SQL Tutorials

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)>where emp.deptno = DEPT.DEPTNO )

A scalar subquery returns a single row and a single column value. SUBQUERIES can be single row, multiple row, 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);