Sub Query:
A sub query is a SELECT statement that is embeded in a clause of another SQL statement,called the parent statement.
The subquery (inner query )returns a value that is used by the parent statement. Using a nested sub query is equivalent to performing two sequential queries and using the result of inner query as search value in the outer query (main query).
Purpose of use of sub query:
To Provide values for conditions in WHERE ,HAVING and START WITH clause of SELECT statements.
- To define the set of rows to be inserted into the target table of an INSERT or CREATE TABLE statement.
- To define the set of rows to be included in a view or snapshot in a CREATE VIEW or CREATE SNAPSHOT statement.
- To definf one or more values to be assigned to existing rows in an update statement.
- To define a table to be operated on by a containing query .( You do this by placing the sub query in the FROM clause. This can be done in INSERT ,UPDATE and DELETE statements as well.)
A sub query is evaluated once for the entire parent statement.
Syntax:
SELECT select_list
FROM table
WHERE expr operator (SELECT select_list
FROM table);
The subquery (inner query) executes once before the main query.
The result of the sub query is used by the main query (outer query).
Ex-1: Display the last name of employees who earn more than the the employee with id 200;
SELECT last_name
FROM employees
WHERE salary >
(SELECT salary
FROM employees
WHERE employee_id=200);
Ex-1: Display the names of all who earn less than the average salary in the company.
SELECT last_name,job_id,salary
FROM employees
WHERE salary <
(SELECT AVG(salary)
FROM employees );
Single-row and multiple-row subqueries:
Subqueries that can return only one or zero rows to the outer statement are called single-row subqueries. Single-row subqueries are subqueries used with a comparison operator in a WHERE, or HAVING clause.
Subqueries that can return more than one row (but only one column) to the outer statement are called multiple-row subqueries. Multiple-row subqueries are subqueries used with an IN, ANY, or ALL clause.
Example 1: Single-row subquery
You store information particular to products in one table, Products, and information that pertains to sales orders in another table, SalesOrdersItems. The Products table contains the information about the various products. The SalesOrdersItems table contains information about customers' orders. If a company reorders products when there are fewer than 50 of them in stock, then it is possible to answer the question "Which products are nearly out of stock?" with this query:
SELECT ID, Name, Description, Quantity
FROM Products
WHERE Quantity < 50;
However, a more helpful result would take into consideration how frequently a product is ordered, since having few of a product that is frequently purchased is more of a concern than having few product that is rarely ordered.
You can use a subquery to determine the average number of items that a customer orders, and then use that average in the main query to find products that are nearly out of stock. The following query finds the names and descriptions of the products which number less than twice the average number of items of each type that a customer orders.
SELECT Name, Description
FROM Products WHERE Quantity < 2 * (
SELECT AVG( Quantity )
FROM SalesOrderItems
);
In the WHERE clause, subqueries help select the rows from the tables listed in the FROM clause that appear in the query results. In the HAVING clause, they help select the row groups, as specified by the main query's GROUP BY clause, that appear in the query results.
Example 2: Single-row subquery
The following example of a single-row subquery calculates the average price of the products in the Products table. The average is then passed to the WHERE clause of the outer query. The outer query returns the ID, Name, and UnitPrice of all products that are less expensive than the average:
SELECT ID, Name, UnitPrice
FROM Products
WHERE UnitPrice <
( SELECT AVG( UnitPrice ) FROM Products )
ORDER BY UnitPrice DESC;
ID
Name
UnitPrice
401
Baseball Cap
10.00
300
Tee Shirt
9.00
400
Baseball Cap
9.00
500
Visor
7.00
501
Visor
7.00
Example 3: Simple multiple-row subquery using IN
Suppose you want to identify items that are low in stock, while also identifying orders for those items. You could execute a SELECT statement containing a subquery in the WHERE clause, similar to the following:
SELECT *
FROM SalesOrderItems
WHERE ProductID IN
( SELECT ID
FROM Products
WHERE Quantity < 20 )
ORDER BY ShipDate DESC;
In this example, the subquery makes a list of all values in the ID column in the Products table, satisfying the WHERE clause search condition. The subquery then returns a set of rows, but only a single column. The IN keyword treats each value as a member of a set and tests whether each row in the main query is a member of the set.
Example 4: Multiple-row subqueries comparing use of IN, ANY, and ALL
Two tables in the SQL Anywhere sample database contain financial results data. The FinancialCodes table is a table holding the different codes for financial data and their meaning. To list the revenue items from the FinancialData table, execute the following query:
SELECT *
FROM FinancialData
WHERE Code IN
( SELECT Code
FROM FinancialCodes
WHERE type = 'revenue' );
Year
Quarter
Code
Amount
1999
Q1
r1
1023
1999
Q2
r1
2033
1999
Q3
r1
2998
1999
Q4
r1
3014
2000
Q1
r1
3114
...
...
...
...
The ANY and ALL keywords can be used in a similar manner. For example, the following query returns the same results as the previous query, but uses the ANY keyword:
SELECT *
FROM FinancialData
WHERE FinancialData.Code = ANY
( SELECT FinancialCodes.Code
FROM FinancialCodes
WHERE type = 'revenue' );
While the =ANY condition is identical to the IN condition, ANY can also be used with inequalities such as < or > to give more flexible use of subqueries.
The ALL keyword is similar to the word ANY. For example, the following query lists financial data that is not revenue:
SELECT *
FROM FinancialData
WHERE FinancialData.Code <> ALL
( SELECT FinancialCodes.Code
FROM FinancialCodes
WHERE type = 'revenue' );
This is equivalent to the following command using NOT IN:
SELECT *
FROM FinancialData
WHERE FinancialData.Code NOT IN
( SELECT FinancialCodes.Code
FROM FinancialCodes
WHERE type = 'revenue' );
Multiple Column Subqueries:
In the single row sub queries and multiple row sub queries only one column is returned by the inner SELECT statement and this is used to evaluate the expression in the parent SELECT statement.
If you want to compare two or more columns , you must write a compund WHERE clause using logical operators.
Using Multiple-Column subqueries, you can combine duplicate WHERE conditions into a single WHERE clause.
Syntax:
SELECT column, column, ...
FROM table
WHERE (column,column,....) IN --------------------------------- Main Query
(SELECT column, column ,......
FROM table
WHERE condition );
************************************************************************
Main Query
....WHERE (manager_id, department_id) IN
Subquery
100 90
102 60
124 40
Each row of the main query is compared to values from a multiple-row and multiple-column subquery.
In the above, the value of manager_id and department_id from the main query are being compared with the manager_id and department_id values retrieved by the subquery. Since the number of columns that are being compared are more than one , the example qualifies as a multiple coulmn subquery.
Column Comparisons:
Column comparisons in a multiple column subquery can be :
Pairwise Comparisons
Non-Pairwise Comparisions
Pairwise Comparisons:
Ex:
/* Display the details of employees who are managed by the same manager and work in the same department as the employees with EMPLOYEE_ID 178 and 174*/
SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id,department_id) IN
(SELECT manager_id,department_id
FROM employees
WHERE employee_id in (178,174))
AND employee_id NOT IN (178,174);
The example above is a multiple column subquery because the subquery returns more than one column. It compares the values in the manager_id column and department_id column of each row in the EMPLOYEES table with the values in the manager_id column and the department_id column for the employees with the employee_id 178 or 174.
First, the subquery to retrieve the manager_id and department_id values for the employees with the employee_id 178 or 174 is executed. These values are compared with the with the manager_id column and department_id column of each row in the EMPLOYEES table. If the value match, the row is displayed. In the output, the records of the employees with the EMPLOYEE_ID 178 or 174 will not be displayed .
EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID
----------- ---------- -------------
175 149 80
176 149 80
177 149 80
179 149 80
Non-Pairwise Comparisons:
/* Display the details of employees who are managed by the same manager and work in the same department as the employees with EMPLOYEE_ID 178 and 174*/
SELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id IN
(SELECT manager_id
FROM employees
WHERE employee_id in (178,174))
AND department_id IN
(SELECT department_id
FROM employees
WHERE employee_id in (178,174))
AND employee_id NOT IN (178,174);
The above example shows a non pairwise comparison of the columns. It displays the employee_id, manager_id and department_id of any employee whose manager_id matches any othe manager IDs of employees whose employee IDs are either 178 or 174 and department_id match any of the department IDs of employees whose employee IDs are either 178 or 174.
First, the subquery to retrieve the manager_id values of the employees with the employee_id 178 or 174 is executed. Similarly, the second subquery to retrieve the department_id values for the employees with the employee_id 178 or 174 is executed. The retrieved values values of manager_id and department_id columns are compared with the manager_id and department_id column for each row in the EMPLOYEES table . If the manager_id column of the row in the EMPLOYEES table matches with any of the values of the manager_id retrieved by the inner subquery and if the department_id column of the row in the EMPLOYEES table matches with any of the values of the department_id retrieved by the second subquery , the record is displayed.
EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID
----------- ---------- -------------
179 149 80
177 149 80
176 149 80
175 149 80
Both the output are same but in different order.
Pairwise :
/*Display the order_id, product_id and quantity of any item in which the product_id and quantity match both the product_id and quantity of an item in order_id 2366*/
Table available on OE Schema in oracle starter database like scott or hr:
SELECT order_id , product_id ,quantity
FROM order_items
WHERE (product_id , quantity) IN
(SELECT product_id, quantity
FROM order_items
WHERE order_id = 2366)
AND order_id = 2366;
ORDER_ID PRODUCT_ID QUANTITY
---------- ---------- ----------
2366 2359 8
2366 2373 7
2366 2382 10
2366 2394 11
2366 2395 12
2366 2400 16
2366 2406 20
2366 2409 22
2366 2415 24
2366 2419 24
10 rows selected.
Non-Pairwise:
SELECT order_id , product_id ,quantity
FROM order_items
WHERE product_id IN (SELECT product_id
FROM order_items
WHERE order_id = 2366)
AND quantity IN (SELECT quantity
FROM order_items
WHERE order_id = 2366)
AND order_id = 2366;
ORDER_ID PRODUCT_ID QUANTITY
---------- ---------- ----------
2366 2359 8
2366 2373 7
2366 2382 10
2366 2394 11
2366 2395 12
2366 2400 16
2366 2406 20
2366 2409 22
2366 2419 24
2366 2415 24
10 rows selected.
/*Write a query to display the name, department number, and salary of any employee whose department number and salary match the department number and salary of any employee who earns a commission*/
SELECT first_name , department_id, salary
FROM employees
WHERE (department_id, salary) IN (SELECT department_id, salary
FROM employees
WHERE commission_pct IS NOT NULL ) ;
/*Display the name, department name, and salary of any employee whose salary and commission match the salary and commission of any employee located in location id 1700.*/
SELECT e.first_name, d.department_name, e.salary
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND
d.location_id = 1700 ;
FIRST_NAME DEPARTMENT_NAME SALARY
-------------------- ------------------------------ ----------
Jennifer Administration 4400
Shelley Accounting 12000
William Accounting 8300
Steven Executive 24000
Neena Executive 17000
Lex Executive 17000
Nancy Finance 12000
Daniel Finance 9000
John Finance 8200
Ismael Finance 7700
Jose Manuel Finance 7800
Luis Finance 6900
Den Purchasing 11000
Alexander Purchasing 3100
Shelli Purchasing 2900
Sigal Purchasing 2800
Guy Purchasing 2600
Karen Purchasing 2500
18 rows selected.
/*Create a query to display the name, hiredate, and salary of any employee who have both the same salary and commission as Scott.*/
SELECT ename, hiredate, sal
FROM emp
WHERE
ename <> 'SCOTT'
AND
(sal, NVL(comm,0) ) IN
( SELECT sal, NVL(comm,0)
FROM emp
WHERE ename = 'SCOTT');
/*Create a query to display the employees that earn a salary that is higher than the salary of all of the clerks. Sort the results on salary from highest to lowest */
SELECT ename, job, sal
FROM emp
WHERE sal > ALL
( SELECT sal
FROM emp
WHERE job = 'CLERK')
ORDER BY SAL DESC;
In pair wise comparison we write like this:
"where (MGR_ID, DEPT_ID) IN (SUBQUERY)"
IN non pair wise comparison we write like this:
"where (MGR_ID) IN (SUBQUERY)
and (DEPT_ID) IN (SUBQUERY)"
In first one we are checking two columns simultaneously within a single query, whereas in the second one we are checking each column individually with two queries.
So the output will also differes, check it out by practical implementation.
