NESTED QUERIES

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 Restrict the data to be retrieved
Subqueries can be used with the
SELECT, INSERT, UPDATE, DELETE
statements along with the operators like
=, <, >, >=, <=, IN, BETWEEN, etc.
 RULES
 Subqueries must be enclosed within
paentheses.
 A subquery can have only one column in the

SELECT CLAUSE
 Multiple columns can be there in the
subquery.
But when?
 Multiple columns are in the main query for

the subquery to compare its selected

columns.
 RULES CONTD
 ORDER BY CLAUSE CANNOT BE USED IN
SUBQUERY
 EG:

WHICH EMPLOYEES SALARY ARE GREATER

THAN BLAKE SALARY
2 QUERIES
I. WHAT IS BLAKE SALARY?
II. COMPARE THIS AND FIND WHICH
EMPLOYEES SAL ARE > THAN BLAKE
SALARY
WHICH EMPLOYEES SALARY ARE
GREATER THAN BLAKE SALARY?
SQL> SELECT ENAME, SAL FROM EMP WHERE SAL >
(SELECT SAL FROM EMP WHERE ENAME = 'BLAKE');

ENAME SAL
---------- ----------
JONES 2975
SCOTT 3000
KING 5000
FORD 3000
TYPES
◦ SINGLE ROW

 SUB QUERY RETURNS ONLY ONE ROW

MULTIPLE ROW

SUB QUERY RETURNS MULTIPLE

ROWS
 SQL> SELECT ENAME, JOB FROM EMP WHERE
JOB = (SELECT JOB FROM EMP WHERE EMPNO
= 7900);

ENAME JOB
---------- ---------
SMITH CLERK
ADAMS CLERK
JAMES CLERK
MILLER CLERK

SQL>
SQL> SELECT EMPNO, JOB, ENAME, SAL FROM EMP;

EMPNO JOB ENAME SAL

---------- --------- ---------- ----------
7369 CLERK SMITH 800
7499 SALESMAN ALLEN 1600
7521 SALESMAN WARD 1250
7566 MANAGER JONES 2975
7654 SALESMAN MARTIN 1250
7698 MANAGER BLAKE 2850
7782 MANAGER CLARK 2450
7788 ANALYST SCOTT 3000
7839 PRESIDENT KING 5000
7844 SALESMAN TURNER 1500
7876 CLERK ADAMS 1100
7900 CLERK JAMES 950
7902 ANALYST FORD 3000
7934 CLERK MILLER 1300
 SQL> SELECT ENAME, JOB, SAL FROM EMP
WHERE JOB = ( SELECT JOB FROM EMP WHERE
EMPNO = 7900) AND SAL >
(SELECT SAL FROM EMP WHERE EMPNO =
7412);

no rows selected
SQL> SELECT SAL FROM EMP WHERE JOB = (SELECT JOB FROM
EMP WHERE ENAME = 'SCOTT') AND SAL > (SELECT SAL FROM
EMP WHERE EMPNO = 7844);
SAL
----------
EMPNO JOB ENAME SAL
3000 ---------- -------- ---------- ---------
3000 7369 CLERK SMITH 800
7499 SALESMAN ALLEN 1600
7521 SALESMAN WARD 1250
7566 MANAGER JONES 2975
7654 SALESMAN MARTIN 1250
7698 MANAGER BLAKE 2850
7782 MANAGER CLARK 2450
ANALYST 7788 ANALYST SCOTT 3000
7839 PRESIDENT KING 5000
7844 SALESMAN TURNER 1500
1500
7876 CLERK ADAMS 1100
7900 CLERK JAMES 950
7902 ANALYST FORD 3000
7934 CLERK MILLER 1300
SQL> SELECT ENAME, JOB, SAL FROM EMP WHERE SAL =
(SELECT MIN(SAL) FROM EMP);

EMPNO JOB ENAME SAL

------- --------- ---------- -------
7500 ASSISTANT HARIKUMAR 750 750
7369 CLERK SMITH 800
7499 SALESMAN ALLEN 1600
7521 SALESMAN WARD 1250
7566 MANAGER JONES 2975
7654 SALESMAN MARTIN 1250
7698 MANAGER BLAKE 2850
7782 MANAGER CLARK 2450
7788 ANALYST SCOTT 3000
7839 PRESIDENT KING 5000
7844 SALESMAN TURNER 1500
7876 CLERK ADAMS 1100
7900 CLERK JAMES 950
7902 ANALYST FORD 3000
7934 CLERK MILLER 1300
SQL> select ename, job, sal from emp where
sal = (select min(sal) from emp);

ENAME JOB SAL

---------- --------- ----------
HARIKUMAR ASSISTANT 750
SQL> SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 20;
MIN(SAL)
----------
750

SQL> SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 30;

MIN(SAL)
----------
950

SQL> SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 10;

MIN(SAL)
----------
1300
SQL> SELECT DEPTNO, MIN(SAL) FROM EMP GROUP
BY DEPTNO;
DEPTNO MIN(SAL)
--------- ----------
30 950
20 750
10 1300

SQL> SELECT DEPTNO, MIN(SAL) FROM EMP GROUP

BY DEPTNO HAVING MIN(SAL) > (SELECT MIN(SAL)
FROM EMP WHERE DEPTNO = 20);

DEPTNO MIN(SAL)
---------- ----------
30 950 20 750
 SQL> SELECT DEPTNO, MIN(SAL) FROM EMP
GROUP BY DEPTNO HAVING MIN(SAL) >
(SELECT MIN(SAL) FROM EMP WHERE DEPTNO
= 20);

DEPTNO MIN(SAL)
---------- ----------
30 950
10 1300
Multiple-Row Subqueries
 Return more than one row
 Use multiple-row comparison operators
SQL> SELECT ENAME, SAL, DEPTNO FROM EMP
WHERE SAL IN (950,750,1300);

ENAME SAL DEPTNO

---------- ---------- ----------
JAMES 950 30
RAMAN 950 30
HARIKUMAR 750 20
MILLER 1300 10
SQL> SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE SAL <
ANY
2 (SELECT SAL FROM EMP WHERE JOB = 'ANALYST');

EMPNO ENAME JOB SAL

---------- ---------- --------- ----------
7500 HARIKUMAR ASSISTANT 750
7369 SMITH CLERK 800
7510 RAMAN TECH_AST 950
7900 JAMES CLERK 950
7876 ADAMS CLERK 1100
7521 WARD SALESMAN 1250
7654 MARTIN SALESMAN 1250
7934 MILLER CLERK 1300
7844 TURNER SALESMAN 1500
7499 ALLEN SALESMAN 1600
7782 CLARK MANAGER 2450
7698 BLAKE MANAGER 2850
SQL> SELECT EMPNO, ENAME, JOB, SAL FROM
EMP WHERE SAL < ALL ( SELECT SAL FROM
EMP WHERE JOB = 'CLERK');

EMPNO ENAME JOB SAL

---------- ---------- --------- ---------
7500 HARIKUMAR ASSISTANT 750
 SQL> SELECT EMPNO, ENAME, JOB, SAL FROM EMP
WHERE SAL > ALL (SELECT SAL 1300
FROM EMP WHERE
JOB = 'CLERK');
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7499 ALLEN SALESMAN 1600
7566 JONES MANAGER 2975
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7788 SCOTT ANALYST 3000
7839 KING PRESIDENT 5000
7844 TURNER SALESMAN 1500
7902 FORD ANALYST 3000

8 rows selected.
Correlated Nestedquery
Nested query:
1. Outer query uses the result of the inner query
2. Outer query depends on the value returned by
the inner query
3. But inner query does not depend on the outer
query
4. That is why it is called as plain subquery or plain
Nested query
Correlated Nested Query
 Both are inter related with each other
 Uses the values from the outer query
 Executes each time for each row selected by

the outer query.

 That is why a query that uses correlated

nested query may be slow.

 A correlated nested query is otherwise

called as repeating nested query or

synchronized nested query
SELECT employee_id, first_name, last_name,
salary FROM employees WHERE salary >
(SELECT AVG(salary) FROM employees);
SELECT AVG(salary) FROM employees;
SELECT employee_id, first_name,
last_name, salary, department_id
FROM employees e
WHERE
salary >(SELECT AVG(salary)
FROM
employees
WHERE
department_id = e.department_id)
ORDER BY
department_id ,
first_name , last_name;
SQL> SELECT JOB, SAL FROM EMP WHERE JOB = 'CLERK';

JOB SAL
--------- ----------
CLERK 800
CLERK 1100
CLERK 950
CLERK 1300

SQL> SELECT MAX(SAL) FROM EMP WHERE JOB = 'CLERK';

MAX(SAL)
----------
1300
 The output shows the employee
details those who are not clerks, but,
whose salary is less than the salary of
all clerks.
 >ALL means more than the maximum,

and
 <ALL means less than the minimum
EXISTS Operator
• The EXISTS operator is used to test for the
existence of any record in a subquery.
• The EXISTS operator returns TRUE if the
subquery returns one or more records.

EXISTS Syntax

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE
condition);
Example
SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Pro
ducts WHERE Products.SupplierID =
Suppliers.supplierID AND Price < 20);

SELECT SupplierName
FROM Suppliers
WHERE
NOT EXISTS (SELECT ProductName FROM Produ
cts WHERE Products.SupplierID =
Suppliers.supplierID AND Price = 22);
SQL SELECT INTO Examples
 SELECT * INTO CustomersBackup2017
FROM Customers;

SELECT * INTO CustomersBackup2017 IN

'Backup.mdb‘ FROM Customers;

SELECTCustomerName,
ContactName INTO CustomersBackup2017
FROM Customers;
Example contd.,
 SELECT * INTO CustomersGermany
FROM Customers
WHERE Country = 'Germany';

Create new table from more than one tables:

SELECT Customers.CustomerName,
Orders.OrderID
INTO CustomersOrderBackup2017
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID =
Orders.CustomerID;
Example contd.,
 SELECT * INTO newtable
FROM oldtable
WHERE 1 = 0;