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.