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

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 SQL (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 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 fiirst query becomes a subquery of the second query

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

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 has 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)