Oracle SQL 9i Introduction and OCA guide...CLICK HERE !!!

Subqueries

A Subquery is a query within a query. It is also known as inner query.

SELECT * FROM TABLE_1
WHERE NAME IN (SELECT NAME FROM TABLE_2 WHERE ID = 007007);

Look at the following statements –

SELECT MAX (SALARY) FROM EMP_DETAILS
WHERE SALARY < (SELECT MAX (SALARY) FROM EMP_DETAILS);

In this example we are trying to find out the second highest salary from the table named EMP.  Instead of specifying any value in the WHERE clause, we are using another query. This is the inner query. This query will be executed first and its value will be compared with in outer query.

Points to note –

  • A subquery can be written in the WHERE clause, FROM clause or in HAVING clause.
  • A subquery is also known as inner query. The main query is known as the outer query
  • A subquery is written within parenthesis.
  • There are two types of subqueries – Single Row Subquery & Multiple Row Subquery.

A single row subquery will return a single row. The example above is a single row subquery - SELECT MAX (SAL) FROM EMP will return only one row.  In single row subqueries, we use operators like =, >, <, <>, >=, <= etc.

Below are some examples of single row subqueries –

SELECT * FROM EMP_DETAILS
WHERE DEPT_ID = (SELECT DEPTNO FROM DEPT_DETAILS WHERE DNAME = 'HR');

SELECT * FROM STUDENTS
WHERE GRADE > (SELECT GRADE FROM STUDENT_MASTER WHERE STU_ID = 555);


A multiple row subquery returns more than one row. IN, ANY, ALL etc can be used in single row subqueries.

Here are some examples –

SELECT * FROM STUDENTS
WHERE DEPARTMENT_ID IN
(SELECT DEPT_ID FROM STUDENT_MASTER WHERE GRADE > 5);

SELECT EMP_NAME, EMP_ID, SALARY FROM EMP_DETAILS
WHERE SALARY > ANY
(SELECT SALARY FROM EMPLOYE_HISTORY WHERE DEPT_NAME = ‘SALES’)

SELECT EMP_NAME, EMP_ID, SALARY FROM EMP_DETAILS
WHERE SALARY > ALL
(SELECT SALARY FROM EMPLOYE_HISTORY WHERE DEPT_NAME = ‘IT’)

As discussed above, a subquery can be written in the FROM clause, WHERE clause or HAVING clause of a SQL statement.

Here are some very simple examples for better understanding –

SELECT MAX (SALARY) FROM EMP_DETAILS
WHERE SALARY < (SELECT MAX (SALARY) FROM EMP_DETAILS);


SELECT A.NAME, A.ID, A.JOB, B.LOCATION
FROM EMP_DETAILS A, (SELECT * FROM DEPT_DETAILS) B
WHERE A.DEPT_ID=B.DEPT_ID;


SELECT DEPT_NO, COUNT (*) FROM STUDENTS
GROUP BY DEPT_NO
HAVING COUNT (*) >
(SELECT COUNT (*) FROM STUDENT_HISTORY WHERE DEPT_ID = 200);


SQL Samples

Examples of SELECT
Examples of INSERT
Examples of UPDATE
Examples of  ALTER
Examples of MERGE INTO
Examples of CASE & DECODE
Examples of CREATE
Examples of CONSTRAINTS
Examples of JOINS
Examples of DELETE, DROP & TRUNCATE
Examples of SUBQUERIES
Examples of INDEX
Examples of SYNONYM
Examples of SEQUENCE
Examples of VIEWS

PLSQL Samples

Examples of PLSQL Block
Examples of PLSQL Iterative statement
Examples of PLSQL Conditional statement
Examples of PLSQL Records
Examples of PLSQL Cursors
Examples of PLSQL Triggers
Examples of PLSQL Procedures
Examples of PLSQL Functions
Examples of PLSQL Parameters (IN,OUT, IN OUT)
Examples PLSQL Varrays
Examples PLSQL Nested Table
Examples PLSQL Associative Array/Index by Table