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:

CREATE TABLE Product
(
Prod_Id Number Not Null,
Prod_Name Varchar2(50),
Quantity Varchar2(15),
Price Number
);

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

Syntax

SELECT <column, ...>
FROM <table>
WHERE expression operator
  (
   SELECT <column, ...>
   FROM <table>
   WHERE <condition>
   );

Or

SELECT Col_name [, Col_name]
FROM table1 [,table2]
WHERE Col_name OPERATOR
  (
  SELECT Col_name [,Col_name]
  FROM table1 [,table2]
  [WHERE]
  );

Now let us explain the Sub Query using all the three clauses. For that we are assuming the following tables.

STUDENT TABLE



SUBJECT TABLE



1. Sub Query using WHERE Clause

SELECT * FROM student
WHERE course_id in (SELECT course_id
FROM subject
WHERE course_name = 'Oracle')

2. Sub Query using FROM Clause

SELECT a.course_name, b.Avg_Age
FROM subject a, (SELECT course_id, Avg(Age) as Avg_Age
FROM student GROUP BY course_id) b
WHERE b.course_id = a.course_id

 

3. Sub Query using SELECT Clause

SELECT course_id, course_name,
(
SELECT count (course_id) as num_of_student
FROM student a
WHERE a.course_id = b.course_id
GROUP BY course_id
)  No_of_Students
FROM subject b

Types of Sub Queries

 

 

 

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 with Data

 

 

 

DEPARTMENT TABLE with Column name

 

 

 

DEPARTMENT TABLE with Data

 

 

 

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

SELECT * FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees);

Execute the Query. The result will be as in the following:

 

 

 

Single Row Sub Query using HAVING Clause 

SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > ( SELECT MIN(salary)
FROM employees
WHERE department_id = 50);

Execute the Query, the result will be as in the following:

 

 

 

Next article - Sub Query in Oracle: Part 2