SQL: Subqueries and Types of Subqueries

******Sub Queries and Types of Sub Queries
Sub Query - Query within another query
Sub query is assciated with and Outer Query and Inner Query Sub query
Subquery executes only once and gives output to the outer query for final results

Types of Subqueries
Single Row Sub Query
Subquery is going to return a single row. = and Greater than
Find the highest paid employee
Select * From Employee where Salary =(Select Max(Salary) From Employee);

Multi Row Sub Query
Multiple rows will be given to the outer query as an input.
Get the highest paid employee details from the table within each department.
Select * From Employee
Where Salary IN(Select MAX(Salary) From Employee Group By DEPTID);

Single Column Sub Query
Deals with a single column.
Select * From Employee Where DEPTID=10 And Salary GreaterThanEqualto ALL(Select Salary From Employee Where DEPTID=10);

Multi Column Sub Query
Subqquery is going to compare multiple columns with the Outer query columns.

Select * From Employee
Where (JOB_ID,SALARY) IN(Select JOB_ID, SALARY From Employee Where ID=102);

Co-Related Sub Query
Subquery is going to get the input values from Outer Query.
Subquery is going to execute after Outer Query.

Select * From Department D Where EXISTS(Select 1 From Employee E Where E.DEPTID=D.DEPTID);
Select * From Department D Where NOT EXISTS(Select 1 From Employee E Where E.DEPTID=D.DEPTID);

Nested Sub Query
Another loop within the subquery.

Scalar Sub Query
Output of scalar query used as a column in our final query.
Select DEPTID, DEPTNAME, (Select Count(*) From Employee E Where E.DEPTID=D.DEPTID) As NumberOfEmployees From
DEPARTMENT D;

#GoLearningPoint

For Interview questions and other topics -

[knandi]

In almost all of your videos, there is tremendous sound issues...

[knandi]

Why don't you check video quality before uploading? Poor quality video irriates a lot....