What is the difference between a normal query and a subquery in SQL?

by neelima
(Hyderabad)

What is the difference between a normal query and a subquery?


The difference between "normal" queries and subqueries is just semantics. A subquery is just a query embedded in another SQL statement (often another query). You can nest queries as deeply as you like in Oracle (up to 255 levels), although that doesn't mean that the queries will still be readable or that they will work very well!

Subqueries embedded in another query are often used to answer multi-part questions and can be promoted to be a stand alone query just as a stand alone query can become a subquery.

Let's look at a few examples to demonstrate this. Let's suppose in our Oracle database we have a table called "emp" containing employee details such as id,name,salary,department etc. and with the following data:

emp

Id Name               Salary Dept_id
-- ----               ------ -------
1  Andy Hamilton       510    1
2  Mike Jones          809    3
3  Mike Smith          930    6
4  Raman Amruther      602    2
5  Andy MacDonald      550    1
6  Satish Patel        645    2
7  Christine Delacour  535    1
8  Sanjiv Deva         907    3
9  Tina Johannsen      918    6
10 Rushi Patil        1106    6
11 Fox Mulder          675    5
12 Lynrd Skinnered     489    5


Let's further suppose that we want to find out which employee has the highest salary. To do that we need to ask two questions - what is the highest salary and who is earning that salary?

In SQL the first question becomes

SELECT max(salary) FROM emp

and the second question becomes:

SELECT name FROM emp WHERE salary = the_highest_salary

Putting them together, the first query becomes a subquery of the second query

SELECT name FROM emp
WHERE salary = (SELECT max(salary) FROM emp)


Although we've not tested it, it looks as if in Oracle 12c you wouldn't need to use a subquery for this, instead you could use the FETCH FIRST SQL clause like this:
SELECT name FROM emp ORDER BY SALARY
FETCH FIRST 1 ROWS ONLY


Let's extend this example by assuming we also have a table called "dept" in our Oracle database, containing details of our company's various departments.

dept

id name
-- ----
1  HR
2  IT
3  Finance
4  FM
5  Sales
6  Marketing


Now we can change our question to ask "which department has the highest paid employee?". This turns it into a 3 part question: the first part determines the highest salary, the second part, which employee(s) has (have) the highest salary, and the third part, which department he or she works for. We can translate into SQL as follows:

SELECT name FROM dept WHERE id =
      (SELECT dept_id FROM emp WHERE salary =
            (SELECT max(salary) FROM emp)
      )


And as mentioned earlier we could extend this further and add more subqueries by asking more questions.

We are not restricted to using subqueries in the WHERE clause of queries in Oracle SQL, we can also use them in the FROM clause where they are known as in-line views. This can be useful for problems where you want to limit the number of rows returned and order the results. For example to determine the names of the 3 highest paid employees we could write a query in SQL like this:-

SELECT name,salary FROM
(SELECT name,salary FROM emp ORDER BY salary DESC)
WHERE rownum < 4


This produces the following results:
Name              Salary
Rushi Patil       1106
Mike Smith         930
Tina Johannsen     918


The reason why we can't do it as one query is because Oracle would apply the filter first and then sort the results. This would give us the first 3 employees selected at random from the database and then sorted in descending order of salary so to prevent that we first sort the results and then select the first 3 to give us what we want.

Up 'till now all the subqueries we've looked at have been what are called non-correlated subqueries because the subquery is run just once for the whole SQL statement.

There is another type of subquery called a correlated subquery in which the subquery refers to a column in the parent query and because of this are run once for each row in the parent query.

Just like non-correlated sub queries, correlated sub queries in SQL are used to answer multi-part questions, but they are usually used to check for the presence or absence of matching records in the parent query.

For example if we want to find out if any of our departments don't have any employees working for them we can use a correlated subquery in SQL to do this quite efficiently.

SELECT dept.name FROM dept
WHERE NOT EXISTS
    (SELECT dept_id
     FROM emp
     WHERE emp.dept_id = dept.id)


We can also write this as a non-correlated sub query to produce the same results:

SELECT dept.name FROM dept
WHERE id NOT IN
    (SELECT dept_id FROM emp)

Name
----
FM

Finally it should be noted that subqueries are not restricted to being used in queries. We can also use them in DML statements in SQL (INSERT, UPDATE, DELETE, CREATE TABLE AS ).

For example:

1. To double the salary of those employees whose salary equals that of the highest paid employee.

UPDATE emp SET salary=salary*2 WHERE salary=
(SELECT max(salary) FROM emp)


2. To copy into another table in our database the records of those employees whose salary equals that of the highest paid employee.

INSERT INTO emp2
SELECT * FROM emp WHERE salary =
(SELECT max(salary) FROM emp)


3. To DELETE the records of those employees whose salary equals that of the highest paid employee.

DELETE FROM emp WHERE salary =
(SELECT max(salary) FROM emp)


You might also be interested in our Oracle SQL tutorials. Also see our Oracle training page for information about formal training courses both on-line and in person.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.