SIGN UP MEMBER LOGIN:
   
ARTICLE

Nested Query in Oracle SQL Plus

Posted by Jerry Gale Articles | Oracle March 28, 2011
A Nested Query is a SELECT query that is nested inside a SELECT, INSERT, UPDATE, DELETE statements or inside another Sub Query.
Reader Level:

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
SUBQUERY1.gif

2. Orders
SUBQUERY2.gif

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;

SUBQUERY3.gif
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:
SUBQUERY5.gif

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:
 SUBQUERY4.gif

Login to add your contents and source code to this article
This Feature is Sponsored By
post comment
COMMENT USING