Subqueries allow us to combine data from multiple tables from a database into a single query.
Breaking it down to its simplest definition, a subquery is “a query inside a query”. There are many variants of subqueries.
While subqueries are generally used with a ‘where’ clause, they can be used with other SQL clauses like – IN, FROM, HAVING.

To ease the understanding of what the subquery is, below is a simplified structure of a query.

QUERY – A Clause – SUBQUERY

SYNTAX:
Query – select * from TableName_x
A Clause – WHERE or IN or HAVING FieldName_x
Subquery – select FieldName_x from TableName_y

Consider below Tables – Table 1 for Salesman and Table 2 for Orders

Scope: To find all the orders issued by the salesman ‘Paul Adam’. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.

Solution:
SELECT * FROM Orders
WHERE salesman_id = (SELECT salesman_id FROM salesman WHERE name=’Paul Adam’)

Query: Select * from Orders
A Clause: WHERE salesman_id =
Subquery: SELECT salesman_id FROM Salesman WHERE name=’Paul Adam’

In this case the output for the SUBQUERY is 5007. This output is used to satisfy the conditional WHERE clause. And the * in the main query requires all fields from the Orders tables to be returned where the salesman_id is 5007 i.e. Paul Adam

So here it is, a brief explanation on subquerying to return values from a table based on conditions from another table.

 

 

 

 

 

Shashanka Rao
Author: Shashanka Rao