By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
439,977 Members | 1,369 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Using Subqueries - 1

debasisdas
Expert 5K+
P: 8,127
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
Expand|Select|Wrap|Line Numbers
  1. SELECT *  FROM emp WHERE sal >(SELECT sal FROM emp WHERE empno=7566);
  2.  
Example #2
Display details of employee whose job is equal to 7369

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM emp
  2. WHERE job= (SELECT job FROM emp WHERE empno=7369);
  3.  
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM emp WHERE job= (SELECT job FROM emp
  2. WHERE empno=7369)  AND Sal > ( SELECT Sal FROM emp WHERE  empno=7876);
  3.  
USING GROUP FUNCTION in sub-queries
=================================
Example #1
--------------------
Display details of all employees whose salary is equal to the minimum salary.

Expand|Select|Wrap|Line Numbers
  1.  SELECT * FROM emp
  2. WHERE sal = (SELECT MIN(sal) FROM emp);
  3.  
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);
Expand|Select|Wrap|Line Numbers
  1. SELECT deptno, MIN(sal) FROM emp GROUP BY deptno HAVING MIN(sal) >
  2. ( SELECT MIN(sal) FROM emp WHERE deptno=20);
  3.  
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT job, AVG(sal) FROM emp GROUP BY job
  2. HAVING AVG(sal) = (SELECT  MIN(AVG(sal)) FROM emp GROUP BY job);
  3.  
Also Check Using Subqueries - 2
Sep 17 '07 #1
Share this Article
Share on Google+