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

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

1.Sub Inquiry

--Extract employees in the smallest department number

SELECT	*
FROM	EMP
WHERE	EMP.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	*
FROM	EMP
WHERE	DEPTNO 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.

Practice (1/2) Question

Extract all entries from the EMP table for records with a DEPTNO (department number) of 10 or 20 using IN.

Practice (1/2) Answer

SELECT	*
FROM	EMP
WHERE	DEPTNO IN (10,20)

Practice (2/2) Question

　Create a SQL that uses NOT to negate the IN query created in the previous Practice.

Practice (2/2) Answer

SELECT	*
FROM	EMP
WHERE	DEPTNO NOT IN (10,20)

NOT is listed before IN.

--How to use IN in Nested Inquiry

SELECT	*
FROM	EMP
WHERE	DEPTNO 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

Question
Answer and Explanation

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

ID	Name	Dept	RoomNo
001	Tanaka	A1	110
002	Suzuki	A1	111
003	Sato	A1	203
004	Fukuda	A2	201
005	Watanabe	A2	202

Room_Management_Dept

RoomNo	Dept
110	A1
111	A1
201	A2
202	A2
203	A2

SELECT

Name

FROM

Employee_room

WHERE

RoomNo NOT IN (

SELECT	RoomNo
FROM	Room_Management_Dept
WHERE	Dept = 'A1'

)

Name

Tanaka

Suzuki

Name

Fukuda

Watanabe

Name

Tanaka

Suzuki

Sato

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

SELECT	ENAME,SAL
FROM	EMP EA
WHERE	NOT 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 ].