Nested Queries in Oracle SQL Plus
Nested Queries are also known as Sub Queries or Inner Queries which are used to
answer multi-part questions. A Sub Query or Nested Query is a SELECT query that
is nested inside a SELECT, INSERT, UPDATE, DELETE statements or inside another
Sub Query. A Sub Query can return just a row or a set of rows to its parent
query.
The places in a query where a Nested Query may be used are as follows:
- In the SELECT list used for column projection
- In the FROM clause
- In the WHERE clause
- In the HAVING clause
For eg., we have following two tables
1. Customers
2. Orders
Suppose, we want to list the id, first name, last name, total price of all
orders for all customers, we will use the following query :
SELECT CUSTOMERS.ID, CUSTOMERS.FIRSTNAME, CUSTOMERS.LASTNAME, SUM(ORDERS.PRICE) AS TOTAL_PRICE
FROM CUSTOMERS INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.ID
GROUP BY CUSTOMERS.ID, CUSTOMERS.FIRSTNAME, CUSTOMERS.LASTNAME
ORDER BY CUSTOMERS.ID;
Now, From the result-set given above, we need to find id, first name, last
name and total_price of the customer who has maximum total_price. we'll use the
following SQL Query (containing Sub Query) as shown below:
SELECT * FROM
(
SELECT CUSTOMERS.ID, CUSTOMERS.FIRSTNAME, CUSTOMERS.LASTNAME, SUM(ORDERS.PRICE) AS TOTAL_PRICE
FROM CUSTOMERS INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.ID
GROUP BY CUSTOMERS.ID, CUSTOMERS.FIRSTNAME, CUSTOMERS.LASTNAME
ORDER BY TOTAL_PRICE DESC
)
WHERE
ROWNUM =1;
Output:
OR
SELECT * FROM
(
SELECT CUSTOMERS.ID, CUSTOMERS.FIRSTNAME, CUSTOMERS.LASTNAME, SUM(ORDERS.PRICE) AS TOTAL_PRICE
FROM CUSTOMERS INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.ID
GROUP BY CUSTOMERS.ID, CUSTOMERS.FIRSTNAME, CUSTOMERS.LASTNAME
)
WHERE
TOTAL_PRICE IN
(
SELECT MAX(TOTAL_PRICE) FROM
(
SELECT SUM(ORDERS.PRICE) AS TOTAL_PRICE
FROM ORDERS
GROUP BY ID
)
);
Output:
