Sub Query in Oracle: Part 1

Today in this article I share the use of a Sub Query. But before explaining what a Sub Query is I want to through some light on the mechanism called Query that is used frequently by database administrators. I think that without queries DBAs would not exist. We all use this term but are we truly clear about what a Query is? Let's review what a Query is.
 
Query
 
While creating a database if we want to extract some information regarding the data in the database then we use a Query. In other words, if we want to retrieve some data from a table or some tables that we created earlier then we write/use a Query.
 
Example: If we write a simple Query to create a table:
  1. CREATE TABLE Product  
  2. (  
  3. Prod_Id Number Not Null,  
  4. Prod_Name Varchar2(50),  
  5. Quantity Varchar2(15),  
  6. Price Number  
  7. ); 
Then, the result will be as in the following.
 
Product Table
 
Prod_id Prod_Name Quantity Price
       

Sub Query
 
If a Query that contains another Query, then the Query inside the main Query is called a Sub Query and the main Query is known as the parent Query. In Oracle the Sub Query will executed on the prior basis and the result will be available to the parent Query and then the execution of the parent/main Query takes place. Sub Queries are very useful for selecting rows from a table having a condition that depends on the data of the table itself. A Sub Query can also be called a Nested/Inner Query. These Sub Queries can be used with:
  • WHERE Clause
  • SELECT Clause
  • FROM Clause
Query SubQuery

Syntax
  1. SELECT <column, ...>  
  2. FROM <table>  
  3. WHERE expression operator  
  4.   ( 
  5.    SELECT <column, ...>  
  6.    FROM <table>  
  7.    WHERE <condition>  
  8.    ); 
Or
  1. SELECT Col_name [, Col_name]  
  2. FROM table1 [,table2]  
  3. WHERE Col_name OPERATOR  
  4.   (  
  5.   SELECT Col_name [,Col_name]  
  6.   FROM table1 [,table2]  
  7.   [WHERE]  
  8.   );
Now let us explain the Sub Query using all the three clauses. For that we are assuming the following tables.

STUDENT TABLE

Student Table

SUBJECT TABLE

Subject Table

1. Sub Query using WHERE Clause
  1. SELECT * FROM student  
  2. WHERE course_id in (SELECT course_id  
  3. FROM subject  
  4. WHERE course_name = 'Oracle') 
Subquery using Where Clause

2. Sub Query using FROM Clause
  1. SELECT a.course_name, b.Avg_Age  
  2. FROM subject a, (SELECT course_id, Avg(Age) as Avg_Age  
  3. FROM student GROUP BY course_id) b  
  4. WHERE b.course_id = a.course_id
Subquery using From Clause
 
3. Sub Query using SELECT Clause
  1. SELECT course_id, course_name,  
  2. (  
  3. SELECT count (course_id) as num_of_student  
  4. FROM student a  
  5. WHERE a.course_id = b.course_id  
  6. GROUP BY course_id  
  7. )  No_of_Students  
  8. FROM subject b
Subquery using Select Clause


Types of Sub Queries
 
Types of SubQuery 
 
Here, for all the types of Sub Queries we will use the default Scott Schema. And the following are the default tables of the Scott Schema.
 
EMPLOYEE TABLE with Column Name
 
Employee Table 

EMPLOYEE TABLE with Data
 
Employee Table Schema 
 
DEPARTMENT TABLE with Column name
 
Department Table 
 
DEPARTMENT TABLE with Data
 
Department Table Schema 
 
Now, let me share all the types one by one.
 
1. Single Row Sub Query
 
In a Single Row Sub Query the queries return a single/one row of results to the parent/main Query. It can include any of the following operators:
  • = Equals to
  • > Greater than
  • < Less than
  • >= Greater than Equals to
  • <= Less than Equals to
  • <> Not Equals to
Example
  1. SELECT * FROM employees  
  2. WHERE salary = (SELECT MIN(salary) FROM employees); 
Execute the Query. The result will be as in the following:
 
Single Row Subquery 
 
Single Row Sub Query using HAVING Clause 
  1. SELECT department_id, MIN(salary)  
  2. FROM employees  
  3. GROUP BY department_id   
  4. HAVING MIN(salary) > ( SELECT MIN(salary)  
  5. FROM employees   
  6. WHERE department_id = 50); 
Execute the Query, the result will be as in the following:
 
Single Row Subquery with Having