Suppose we want to retrieve the records of students who are older than ‘Alex’. How we will perform this operation? We have to first search the age of Alex, and then his age has to be used in the query to fetch the students who are older than Alex. Hence there will be two queries to perform above retrieval. But we do not have any variable which will store the result of one query and used in other query, unless a PL/SQL block is written. But for such a simple query, we do not want to write a lengthy PL/SQL block with declare, begin, exception etc. We need to get it done easily. Hence DBMS provides a feature called sub query where a query is written in the WHERE clause.
In the above example, if we write a query to retrieve the age of Alex in the WHERE clause and compare it with other student’s age, then our task is easier.
SELECT *
FROM STUDENT
WHERE AGE>=
(SELECT AGE
FROM STUDENT
WHERE STD_NAME = ‘Alex’);If we observe the above query, we can see that the inner query to fetch the age of Alex is executed first to get his age and then it is used to retrieve the students with older age.
Sub query is inner query or nested query where the inner query is executed first and its result is used in the outer query. Sub queries will be executed only once. It has some of the following features:
- Sub queries can be used with SELECT, INSERT, UPDATE and DELETE statements.
We can use sub queries in the INSERT statement as below:
INSERT INTO STD_BKP
SELECT STD_ID, STD_NAME, ADDRESS, CLASS_ID
FROM STUDENT
WHERE CLASS_ID IN
(SELECT CLASS_ID
FROM STUDENT
WHERE CREATED_DATE<= SYSDATE-30);Above query insert one month older record into a backup of student table STD_BKP, by selecting the CLASS_IDs STUDENTS who are inserted into STUDENT one month ago.
We can use sub queries in the UPDATE statement as below:
UPDATE EMPLOYEE SET SALARY = SALARY + (SALARY * 0.1) WHERE DEPT_ID = (SELECT DEPT_ID FROM DEPT WHERE DEPT_NAME = ‘DESIGN’);
Above query is written to update the salary of DESIGN employees by 10%.
We can use sub queries in the DELETE statement as below:
DELETE EMPLOYEE WHERE DEPT_ID = (SELECT DEPT_ID FROM DEPT WHERE DEPT_NAME = ‘DESIGN’);
Above query is written to delete DESIGN employees from EMPLOYEE table.
- We can use sub queries to compare the results using =, <, >, >=, <=, IN, BETWEEN etc operators in the WHERE clause.
- Inner queries are enclosed inside a parenthesis.
- It should return only one row of data. If there is more than one row are returned by sub query then IN operator has to be used.
- In most of the cases only one column is used in inner SELECT statement. But we can have multiple columns provided we have multiple columns to compare in the outer query. When multiple columns are used the order of columns in outer and inner query should be same.
For example, if we need to find the students who are of Alex’s age and are from his place, then the inner / sub query can be written as below:
SELECT *
FROM STUDENT
WHERE (AGE, ADDRESS) =
(SELECT AGE, ADDRESS
FROM STUDENT
WHERE STD_NAME = ‘Alex’);- ORDER BY clause cannot be used inside sub query. Ideally it does not signify anything if used too because it should return only one row. Even if we have to sort the columns, then we can use GROUP BY clause.
- The sub query cannot return BLOB, ARRAY, CLOB, or NCLOB kind of data to outer query.
- In SQL, one can have up to 255 sub queries in the WHERE clause.
- Sub queries can be used to compare the values using SOME clause. SOME is used when we have to compare the list of values using OR clause. If any one of the list value is matching, the result will be displayed.
SELECT *
FROM STUDENT
WHERE (AGE) > SOME – here if there is any student whose age are above ANY students from 100 t0 105, then result will be displayed.
(SELECT AGE
FROM STUDENT
WHERE STD_ID BETWEEN 100 AND 105);Here SOME clause can be replaced by ANY clause. Both of these clauses do the same task.
- Sub queries can be used to compare the values using ALL clauses. ALL is used when we have to compare the list of values using AND clause. If all of the list values are matching, then result will be displayed.
SELECT *
FROM STUDENT
WHERE (AGE) > ALL – here if there is any student whose age is above students from 100 t0 105, then result will be displayed.
(SELECT AGE
FROM STUDENT
WHERE STD_ID BETWEEN 100 AND 105);- Sub queries are used along with UNIQUE clause to check if the sub query returns duplicate values.
SELECT *
FROM STUDENT
WHERE UNIQUE – verifies if any students from 100 to 105 have same age
(SELECT AGE
FROM STUDENT
WHERE STD_ID BETWEEN 100 AND 105);- Sub queries can be used in derived relations. That means we can use sub queries as a table. Consider a query to retrieve the students whose average mark is more than 80. A simple query for this would be:
SELECT S.STD_ID, AVG (SM.MARKS) AS avg_mark FROM STUDENT S, STD_MARKS SM WHERE S.STD_ID = SM.STD_ID GROUP BY S.STD_ID HAVING AVG (SM.MARKS) >80;
Here this query will find only the student IDs whose average is more than 80. But if other details of the students are required, then we need to write another query, which should query the above query as table, i.e.; above query acts as a table for the query to get student details.
SELECT S.STD_ID, S.STD_NAME, S.COURSE_ID FROM STUDENT, (SELECT S.STD_ID, AVG (SM.MARKS) AS avg_mark FROM STUDENT S, STD_MARKS SM WHERE S.STD_ID = SM.STD_ID GROUP BY S.STD_ID) AS SA WHERE S.STD_ID = SA.STD_ID AND SA.AVG_MARK >80;
Here one can note that average value is compared outside the sub query without using HAVING clause, as this sub query acts as a table. Hence comparison is done in the WHERE clause.
- WITH Clause: – Some queries will be complex and using multiple sub queries in the same query will increase the complexity of the query. In such lengthy queries, sometimes, we can pull part of sub query out from the query and use it as a view in the same query. That means, these views are temporary views and should exist while query is being executed. Such views are created using WITH clause and are compiled and executed when query is actually executed. These view will not exists when query is executed.
Consider above example of pulling the student details whose average mark is more than 80. The sub query above can be considered as view and query can be executed as below:
WITH STD_AVG AS (SELECT S.STD_ID, AVG (SM.MARKS) AS avg_mark FROM STUDENT S, STD_MARKS SM WHERE S.STD_ID = SM.STD_ID GROUP BY S.STD_ID) SELECT S.STD_ID, S.STD_NAME, S.COURSE_ID FROM STUDENT, STD_AVG SA WHERE S.STD_ID = SA.STD_ID AND SA.AVG_MARK >80;
Now the sub query has been moved to WITH clause and it acts as a temporary view for this query. It can be named and used in the query as any normal view is used in the query.