Using Subqueries - 1

[debasisdas]

Using Subqueries
==================

The sub query is often referred to as a nested SELECT, Sub - SELECT, or inner SELECT statement.

The sub query executes once before the main query.
The result of the sub query is used by the main query (outer Query).

You can place the sub query in a number of SQL clauses.
WHERE clause
HAVING clause
FROM clause (Inline sub query/inline view)

1.THE NESTED QUERY MUST RETURN A SINGLE COLUMN.
2.THE BETWEEN OPERATOR CAN'T BE USED IN SUBQUERIES.
3.THE RESULT CAN ONLY CONTAIN COLUMNS FROM THE TABLES REFERENCED IN THE OUTER MOST QUERIES.

Types of sub query
---------------------------
Single -row sub queries: -
Queries that return only one row from the inner SELECT statement.

Multiple -row sub queries: -
Return more than one row from the inner SELECT statement.

Note:
---------
Comparison operators fall into two classes. :
Single - row operators (>, >=, =, <>, <, <=)
Multiple - row operators (IN, ANY, SOME, ALL)

Syntax
---------
SELECT select_list FROM table
WHERE expr operator (SELECT select_list FROM table);

**Do not add an ORDER BY clause to a sub query.
*Use single-row operators with single-row sub queries.
*Use multiple-row operators with multiple-row sub queries.

Sample example of Single row sub query: -
=================================

Example #1
Who earn more than empno 7566

SELECT *  FROM emp WHERE sal >(SELECT sal FROM emp WHERE empno=7566);
 

Example #2
Display details of employee whose job is equal to 7369

SELECT * FROM emp
WHERE job= (SELECT job FROM emp WHERE empno=7369);
 

Example #3

Displays employees whose job title is the same as that of employee 7369 and whose salary is greater than that of employee 7876.

SELECT * FROM emp WHERE job= (SELECT job FROM emp
WHERE empno=7369)  AND Sal > ( SELECT Sal FROM emp WHERE  empno=7876);
 

USING GROUP FUNCTION in sub-queries
=================================
Example #1
--------------------
Display details of all employees whose salary is equal to the minimum salary.

 SELECT * FROM emp
WHERE sal = (SELECT MIN(sal) FROM emp);
 

Exp:-The MIN group function returns a single value(800) to the outer query.
display data from a main query by using a group function in a subquery to return a single row.

Using HAVING clause with subqueries***************
The Oracle Server returns results into the main query's HAVING clause.

Example #2
------------------------
Display all the departments that have a minimum salary greater than that of department 20's MIN(Sal);

SELECT deptno, MIN(sal) FROM emp GROUP BY deptno HAVING MIN(sal) >
( SELECT MIN(sal) FROM emp WHERE deptno=20);
 

deptno=20's MIN sal is 800.
i.e. subquery is 800
main query compares MIN(sal) > 800;

Example #3
----------------------
Find the job with the lowest avg salary.

SELECT job, AVG(sal) FROM emp GROUP BY job
HAVING AVG(sal) = (SELECT  MIN(AVG(sal)) FROM emp GROUP BY job);
 

Also Check Using Subqueries - 2