Home » SQL

How can we use Sub Queries in SQL?

In this article, we are going to learn about the sub queries and how they frequently used with the SELECT statement?
Submitted by Manu Jemini, on March 05, 2018

This is very useful. Think of a situation when we want result from a table having a field which needs to be matched to a field a record from a different table.

This can be very messy as first we will the first table and then we will need all the data from the second table then iterate through every record and check if the record matches and if the record matches store it in the result and do the same for second record.

This can be very easy if we use a sub query. All we would need to do is to select data from the first data and use a conditional where keyword for the field we need to check if matched with field in the second table.

For an example, think of two table categories and subcategories and we only want records from the category which have a subcategories, this can be done by the concept explained above.

Like this,

SELECT * FROM category 
WHERE cid in (SELECT * FROM subcategories);

Dummy data:

Syntax 1:

SELECT c, c1 FROM   t1, t2 WHERE  c OPERATOR
(SELECT c, c1
FROM  t1, t2 [WHERE]);

Syntax 2:

UPDATE  t
SET c = new_value
[ WHERE OPERATOR [ VALUE ]
(SELECT c FROM t)
[ WHERE) ]

Example 1:

SELECT * FROM EMPLOYEE WHERE ID IN 
(SELECT ID FROM EMPLOYEE WHERE SALARY > 20000) ;

Example 2:

UPDATE EMPLOYEE SET SALARY = SALARY * 2 WHERE AGE IN 
(SELECT AGE FROM EMPLOYEE WHERE AGE >= 27 );