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:
SQL> SELECT * FROM TABLE1 WHERE TABLE1.SOMECOLUMN = (SELECT SOMEOTHERCOLUMN FROM TABLE2 WHERE SOMEOTHERCOLUMN - SOMEVALUE)
Types of Subqueres
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);
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.
Nesting is the act of embedding a subquery within another subquery.
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 rdbms sub-queries
SQL> SELECT NAME, ORDEREDON FROM ORDERS WHERE EXISTS (SELECT * FROM ORDERS WHERE NAME = 'TRUE WHEEL');