SQL - Sub Queries
Subquery/ Inner query / Nested query
A Subquery or Inner query or a Nested query is a
query within another SQL query and embedded
within the WHERE clause.
A subquery is used to return data that will be
used in the main query as a condition to further
restrict the data to be retrieved.
Subqueries can be used with the SELECT, INSERT,
UPDATE, and DELETE statements along with the
operators like =, <, >, >=, <=, IN, BETWEEN, etc.
Rules
Subqueries must be enclosed within parentheses.
A subquery can have only one column in the
SELECT clause, unless multiple columns are in the
main query for the subquery to compare its
selected columns.
An ORDER BY command cannot be used in a
subquery, although the main query can use an
ORDER BY.
The GROUP BY command can be used to perform
the same function as the ORDER BY in a
subquery.
Rules
Subqueries that return more than one row can
only be used with multiple value operators such
as the IN operator.
The SELECT list cannot include any references to
values that evaluate to a BLOB, ARRAY, CLOB, or
NCLOB.
A subquery cannot be immediately enclosed in a
set function.
The BETWEEN operator cannot be used with a
subquery. However, the BETWEEN operator can
be used within the subquery.
Subqueries with the SELECT
Statement
Subqueries are most frequently used with the
SELECT statement.
The basic syntax is as follows −
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name
OPERATOR (SELECT column_name [,
column_name ]
FROM table1 [, table2 ] [WHERE])
Customer table
+----+---------------+----------+----------------+-------------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------------+----------+-----------------+------------+
| 1 | Ivan | 35 | Mumbai | 2000.00 |
| 2 | Mamta | 25 | Madras | 1500.00 |
| 3 | Chhaya | 23 | Mumbai | 2000.00 |
| 4 | Ashwini | 25 | Mumbai | 6500.00 |
| 5 | Hansel | 27 | Bangalore | 8500.00 |
| 6 | Deepak | 22 | Mangalore | 4500.00 |
| 7 | Vandana | 24 | Mumbai | 10000.00|
+-----+-----------------+------------+---------------+-------------+
SELECT * FROM CUSTOMERS
WHERE ID IN (SELECT ID FROM
CUSTOMERS WHERE SALARY > 4500) ;
+-----+----------+--------+-------------+----------+ |
| ID | NAME | AGE | ADDRESS | SALARY |
+------+----------+-------+-------------+----------+ |
| 4 | Ashwini | 25 | Mumbai | 6500.00 |
| 5 | Hansel | 27 | Bangalore | 8500.00 |
| 6 | Deepak | 22 | Mangalore | 4500.00 |
| 7 | Vandana | 24 | Mumbai | 10000.00|
+-----+-----------------+------------+---------------+-----|
Example
Find the non moving products,i.e.products not
being sold.
Select ProductNo,Description
From Product_Master
Where ProductNo NOT IN (select ProductNo
From Sales_order_details);
Example
Find the name and complete address for the
customer who has placed ordernumber
‘O19001’
Select
name,addrs1,adddrs2,city,State,Pincode
From Client_master
Where client_no IN(select client_no from
sales_order where orderno=‘O19001’
Example
Find the names of clients who have placed orders
worth 10000 and more.
Select name from client_master
where client_no IN(select client_no from
sales_order where orderno In
(select orderno from sales_order_details
where(Qtyordered*productRate)>=10000));
