SUBQUERIES
A Sub Query is a form of an SQL statement that appears inside another SQL statement. It is also termed as NESTED QUERY. The statement containing a sub query is called a PARENT statement. The parent statement uses the rows returned by the Sub query.
It can be used by the following commands: -
To insert records in the target table.
To create tables and insert records in the table created.
To update records in a target table.
To create views.
To provide values for conditions in WHERE, HAVING, IN etc. used with SELECT, UPDATE, and DELETE statement.
Types of Sub-queries
The types of sub-queries and their description are: -
(1) Single-row sub-queries
Queries that returns only one value from the inner SELECT statement.
(2) Multiple-row sub-queries
Queries that returns more than one value from the inner SELECT statement.
(3) Multiple-column sub-queries
Queries that returns more than one column from the inner SELECT statement
(1) SINGLE ROW SUB-QUERIES
A SINGLE-ROW SUBQUERY returns one row from the inner nested query. These types of sub-queries use single-row operators (>, <, >=, <=, <>, =).
EXAMPLE: -
SQL> SELECT * FROM client_order WHERE client_no =
SELECT client_no FROM sales_order
WHERE order_date='17-Mar-05');
(2) MULTIPLE – ROW QUERIES
Sub-Queries that return more than one row are called multiple row sub-queries. We use a multiple – row operator, instead of a single – row operator, with a multiple-row sub-query. The multiple-row operator accepts one or more values.
There are following multi-row operators:
Operator Meaning
IN Equal to any value in the list.
ANY Compare value to each value returned by the sub-query.
(a) Using IN Operator in multiple-row Sub-Queries
The IN operator returns equal to any value in the list.
EXAMPLE: -
SQL>SELECT order_no,order_date,client_no,sale_amount
FROM sales_order WHERE sale_amount IN
(SELECT MIN (sale_amount)
FROM sales_order GROUP BY client_no);
(b) Using ANY operator in multiple-row Sub-Queries
The ANY operator compares a value to each value returned by a sub-query.
EXAMPLE: -
SQL> SELECT client_no,name,bal_due
FROM client_order WHERE bal_due < client_no="'C0004')">'C0004';
(3) MULTIPLE-COLUMN SUB-QUERIES
Multiple-column sub-queries enable us to combine duplicate WHERE conditions into a single WHERE clause.
SYNTAX: -
SELECT column, column…FROM table WHERE (column, column…) IN (SELECT column, column…FROM table WHERE condition);
CORRELATED SUB-QUERIES
Oracle performs a correlated sub-query when the sub-query references a column from the table referred to in the parent statement. A correlated sub-query is evaluated once for each row processed by the parent statement. The parent statement can be a SELECT, UPDATE, or DELETE statement.
EXAMPLE: -
SQL> SELECT name, address, amount, id FROM banking1 b1 WHERE amount = (SELECT MAX (amount) FROMBanking1 WHERE name=b1.name) ORDER BY name;
A Sub Query is a form of an SQL statement that appears inside another SQL statement. It is also termed as NESTED QUERY. The statement containing a sub query is called a PARENT statement. The parent statement uses the rows returned by the Sub query.
It can be used by the following commands: -
To insert records in the target table.
To create tables and insert records in the table created.
To update records in a target table.
To create views.
To provide values for conditions in WHERE, HAVING, IN etc. used with SELECT, UPDATE, and DELETE statement.
Types of Sub-queries
The types of sub-queries and their description are: -
(1) Single-row sub-queries
Queries that returns only one value from the inner SELECT statement.
(2) Multiple-row sub-queries
Queries that returns more than one value from the inner SELECT statement.
(3) Multiple-column sub-queries
Queries that returns more than one column from the inner SELECT statement
(1) SINGLE ROW SUB-QUERIES
A SINGLE-ROW SUBQUERY returns one row from the inner nested query. These types of sub-queries use single-row operators (>, <, >=, <=, <>, =).
EXAMPLE: -
SQL> SELECT * FROM client_order WHERE client_no =
SELECT client_no FROM sales_order
WHERE order_date='17-Mar-05');
(2) MULTIPLE – ROW QUERIES
Sub-Queries that return more than one row are called multiple row sub-queries. We use a multiple – row operator, instead of a single – row operator, with a multiple-row sub-query. The multiple-row operator accepts one or more values.
There are following multi-row operators:
Operator Meaning
IN Equal to any value in the list.
ANY Compare value to each value returned by the sub-query.
(a) Using IN Operator in multiple-row Sub-Queries
The IN operator returns equal to any value in the list.
EXAMPLE: -
SQL>SELECT order_no,order_date,client_no,sale_amount
FROM sales_order WHERE sale_amount IN
(SELECT MIN (sale_amount)
FROM sales_order GROUP BY client_no);
(b) Using ANY operator in multiple-row Sub-Queries
The ANY operator compares a value to each value returned by a sub-query.
EXAMPLE: -
SQL> SELECT client_no,name,bal_due
FROM client_order WHERE bal_due < client_no="'C0004')">'C0004';
(3) MULTIPLE-COLUMN SUB-QUERIES
Multiple-column sub-queries enable us to combine duplicate WHERE conditions into a single WHERE clause.
SYNTAX: -
SELECT column, column…FROM table WHERE (column, column…) IN (SELECT column, column…FROM table WHERE condition);
CORRELATED SUB-QUERIES
Oracle performs a correlated sub-query when the sub-query references a column from the table referred to in the parent statement. A correlated sub-query is evaluated once for each row processed by the parent statement. The parent statement can be a SELECT, UPDATE, or DELETE statement.
EXAMPLE: -
SQL> SELECT name, address, amount, id FROM banking1 b1 WHERE amount = (SELECT MAX (amount) FROMBanking1 WHERE name=b1.name) ORDER BY name;