A subquery is a query whose results are passed as the argument for another query.
Syntax:
select *
from table1
where table1.column1 =
(
select othercolumn1 from table2 where othercolumn1 = value
);
Table1 : Products
Table2 : Orders
Syntax:
select *from table1
where table1.column1 =
(
select othercolumn1 from table2 where othercolumn1 = value
);
Examples
The example tables are given below Table1 : Products
| PARTNUM | NAME | PRICE |
|---|---|---|
| 101 | Chain Saw | $80 |
| 102 | Circular Saw | $120 |
| 103 | Drill | $240 |
| 104 | Hammer | $100 |
Table2 : Orders
| ORDERED DATE | PARTNUM | QUANTITY | REMARKS |
|---|---|---|---|
| 2018-07-27 | 101 | 2 | PAID |
| 2018-07-28 | 102 | 1 | PAID |
| 2018-07-29 | 101 | 3 | PAID |
| 2018-07-30 | 103 | 4 | PAID |
Example 1:
The tables share a common field called PARTNUM. Suppose you didn't know the PARTNUM, but instead wanted to work the Name of the Product. Using a subquery, you could type this:
select *from Orders
where PARTNUM =
(
select PARTNUM from Products where Name like 'Drill%'
);
Result
Here we were able to provide a value for the condition in our WHERE clause. The value was provided for us by the subselect.
When we began, all we knew was that we wanted to see all rows from the Orders table WHERE the Name of the Product was something like Drill.
Example 2: Using Aggregate Functions With Subqueries
The aggregate functions SUM, COUNT, MIN, MAX, and AVG all return a single value. To find out which orders were above average, use the preceding SELECT statement for your subquery. The complete query and results are as follows:
select P.NAME,O.ORDERED_DATE,
O.QUANTITY * P.PRICE as TOTAL
from Orders O, Products P
where O.PARTNUM =P.PARTNUM
and O.QUANTITY * P.PRICE >
(
select AVG(O.QUANTITY * P.PRICE)
from Orders O, Products P
where O.PARTNUM=P.PARTNUM
);
Result
The preceding condition compares the total of each order with the average you computed in the subquery. Note that the join in the subquery is required for the same reasons as in the main SELECT statement. This join is also constructed exactly the same way.
Comments