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
![Query SubQuery]()
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
![Student Table]()
SUBJECT 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')
Result
![Subquery using Where Clause]()
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
Result
![Subquery using From Clause]()
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
Result
![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
SELECT * FROM employees
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
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:
![Single Row Subquery with Having]()
Next article: Sub Query in Oracle: Part 2