Sub Queries in SQL

A subquery is a query whose results are passed as the argument for another query. Sub queries enable us to bind several queries together.

We will understand and be able to do the following:

• Build a subquery

• Use the keywords EXISTS, ANY, and ALL with our subqueries

• Build and use correlated subqueries

Building a Subquery #

Simply put, a subquery let us tie the result set of one query to another. The general syntax is as follows:

Syntax:

SQL> SELECT * FROM TABLE1 WHERE TABLE1.SOMECOLUMN = (SELECT SOMEOTHERCOLUMN
FROM TABLE2 WHERE SOMEOTHERCOLUMN - SOMEVALUE)

Types of Subqueres #

Single-Row Subqueries #

Queries that return one row from the inner SELECT statement.

Ex: Display the employees whose job is same as that of 7788

SQL>select ename,job,sal from emp where job=(select job from emp where
empno=7788);

Ex: Display all employees who have the same job as blake

SQL> select ename,job from emp where job=(select job from emp where
ename='BLAKE');

Using group Functions in a Subquery You can display data from a main query by using a group function in a subquery to return a single row.

Ex: Display the employees who earns the minimum salary in the company

SQL> select * from emp where sal=(select min(sal) from emp);

Note: the inner query returns a value that is used by the outer query or the main query.

Using Having clause with subqueries You can use subqueries not only in the where clause, but also in the having clause. The Oracle server executes the subquery, and the results are returned into the having clause of the main query.

Ex: Find the job with the lowest average salary

SQL> select job,avg(sal) from emp group by job having avg(sal) = (select
min(avg(sal))from emp group by job);

Multiple-Row subqueries #

Subqueries that return more than one row are called multiple-row subqueries you use multiple row operator, instead of a single row operator, with a multiple-row subquery. The multiple row operator expects one or more values.

The multiple row comparision operators are IN,ANY,ALL

Ex: Display employees who earn the lowest salary in each department

SQL> select ename,sal,deptno from emp where sal in(select min(sal) from emp
group by deptno);

Ex: Display the employees who are not managers and whose salary is less than that of any Manager

SQL> select empno,ename,job,sal from emp where sal<any(select sal from emp
where job='MANAGER');

Note: Always enclose subquery within paranthesis Subquery will be evaluated first followed by the main query You can place the subquery in a number of SQL clauses including The Where clause,The Having clause ,The From clause.

Nested Subqueries #

Nesting is the act of embedding a subquery within another subquery.

For example:

SQL> SELECT * FROM SOMETHING WHERE (SUBQUERY(SUBQUERY(SUBQUERY)));

Subqueries can be nested as deeply as our implementation of SQL allows. For example, to send out special notices to customers who spend more than the average amount of money, we would combine the information in the table CUSTOMER.

Correlated Sub queries #

The sub queries we have written so far are self-contained. None of them have used a reference from outside the subquery. Correlated sub queries enable us to use an outside reference with some strange and wonderful results. Look at the following query:

SQL> SELECT * FROM ORDERS O WHERE 'ROAD BIKE' = (SELECT DESCRIPTION
FROM PART P WHERE P.PARTNUM = O.PARTNUM);

EXISTS, ANY, and ALL #

The usage of the keywords EXISTS, ANY, and ALL is not intuitively obvious to the casual observer. EXISTS takes a subquery as an argument and returns TRUE if the subquery returns anything and FALSE if the result set is empty. For example:

SQL> SELECT NAME, ORDEREDON FROM ORDERS WHERE EXISTS (SELECT * FROM ORDERS
WHERE NAME = 'TRUE WHEEL');