Day 9 | Notion

MySQL - Sub Queries (Nested Queries)

Query with Query or Select within Select

EMP Table

EMPNO	ENAME	SAL	DEPTNO	JOB	MGR
1	ARUN	8000	1	M	4
2	AKASH	7000	1	C	1
3	KIRAN	3000	1	C	1
4	JACK	9000	2	M	NULL
5	THOMAS	8000	2	C	4

Display the ename who receiving sal = min(sal)?

Select ename from emp -- main query(parent query)
where sal = (select min(sal) from emp); -- sub-query(child query) 

-- where clause is used for searching

note: subquery execution first then above select statement

The more the number of levels for sub-queries, the slower it will be.
Max UPTO 255 levels for sub-queries.
This limit of SQL can be exceeded with the help of views.

Select ename from emp
where sal = ( select min(sal) from emp
where deptno = 
(select ...
where job = ( select ... )));

Join is faster the subquery because when you write a JOIN you solve the problem using
- Select statement whereas when you write subquery you solve the problem using two or more select statements.
- The more the number of select statements the slower it will be.

Display the 2nd largest SAL.

Select max(sal) from emp
where 
(Select max(sal) from emp);

Display all the rows who belong to the same DeptNo as “Thomas”

Select * from emp
where deptno = (Select deptno from emp where ename = "Thomas");

Display all the rows who are doing the same Job as “Kiran”