SQL Strategy - A site to master SQL while executing it on the Web
Home >> SQL Strategy - Nested Inquiry

Nested Inquiry

This section explains Nested Inquiry, which is often used for afternoon questions.

1.Sub Inquiry

--Extract employees in the smallest department number
SELECT*
FROMEMP
WHEREEMP.DEPTNO = (
SELECT MIN(DEPT.DEPTNO)
FROM DEPT)

EMP.DEPTNO after WHERE and the query result of another SELECT statement. Such a query is called a subquery.

Compare MIN (DEPT.DEPTNO) and EMP.DEPTNO extracted by SELECT MIN (DEPT.DEPTNO) FROM DEPT) inside the brackets and extract equal records.

In this case, the item written after the inner SELECT statement (here MIN(DEPT.DEPTNO)) must be comparable to EMP.DEPTNO.

For example, if the inner query is SELECT DEPT.DEPTNO FROM DEPT, it will return multiple records, resulting in an error.


2.IN description

--Basic IN usage
SELECT*
FROMEMP
WHEREDEPTNO IN (10,30)

IN can be used to extract records that include those enclosed in parentheses ( ).
In the above example, DEPTNO = 10 OR DEPTNO = 30 would mean the same thing.
To negate, add NOT immediately before IN, as in NOT IN.

Now for the Practice, I will give you two questions.

--How to use IN in Nested Inquiry
SELECT*
FROMEMP
WHEREDEPTNO IN (SELECT DEPTNO FROM DEPT WHERE DNAME LIKE '%S%')

Nested Inquiry simply changes the inside of the parentheses to an SQL SELECT statement. The inner SQL is executed and (20,30,40) is extracted and IN (20,30,40) is executed.

Software Development Engineer AM 2003 Q73
Which new tables can be obtained from the "Employee's room" table and the "Room management department" table by the following SQL statement?
Employee_room
IDNameDeptRoomNo
001TanakaA1110
002SuzukiA1111
003SatoA1203
004FukudaA2201
005WatanabeA2202

Room_Management_Dept
RoomNoDept
110A1
111A1
201A2
202A2
203A2

SELECTName
FROMEmployee_room
WHERERoomNo NOT IN (
SELECTRoomNo
FROMRoom_Management_Dept
WHEREDept = 'A1'
)

A
Name
Tanaka
Suzuki
B
Name
Fukuda
Watanabe
C
Name
Tanaka
Suzuki
Sato
D
Name
Sato
Fukuda
Watanabe
Answer

D

Explanation

1. check the result of the subquery execution
WHERE Dept = 'A1' so 110 and 111 are returned.

2. Find the result of NOT IN (110,111).
From the above, D is the correct answer.

3.EXISTS Description

--Extracting the highest paid employees
SELECTENAME,SAL
FROMEMP EA
WHERENOT EXISTS(
SELECT * FROM EMP EB
WHRE EB.SAL > EA.SAL
)

This SQL uses the EXISTS description to extract the employees with the highest salaries.

The EXISTS statement returns true if there are records to be extracted in the SQL written in parentheses ( ), and false if there are no records. Only if true, the outer WHERE condition is satisfied and the record is extracted.

The actual operation is taken row by row from the outer EMP table (EA) and compared in the inner SQL.

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 1980-12-17 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 2850 30
7782 CLARK MANAGER 7839 1981-06-09 2450 10
7839 KING PRESIDENT 1981-11-17 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
7900 JAMES CLERK 7698 1981-12-03 950 30
7902 FORD ANALYST 7566 1981-12-03 3000 20
7934 MILLER CLERK 7782 1982-01-23 1300 10

A row on the EA side of the EMP table (800 of SMITH's salary) is retrieved. Using this, the inner SQL compares EB.SAL > EA.SAL; EA.SAL is 800, so the inner SQL returns a record and EXISTS is true; NOT negates, so the answer is false and the SMITH record is not extracted.

Data is extracted from the top, and when you get to the KING row, the SAL of the KING is 5000, so the inner SQL does not extract the record, so EXISTS is false, and NOT is true, so the KING record is extracted.

This concludes the [ Nested Inquiry ].