SubQueries in SQL

Subquery 

                    The SQL Query statement that enclosed within another query statement is called Subquery. 

Types of Subqueries:

• Single Row Subquery - The subquery return zero or one value. The outer query will process that value and return only row.
• Multiple Row and Column Subquery- The subquery return zero or one value. The outer query will process that value and return one or multiple row.
• Correlated Subquery- The subquery return multiple value to outer query and outer query will process each value return from subquery.

SUBQueries in SELECT Statement.

Single Row Subquery

Employee Table

id	Emp_name	Age	Salary	Dept_id
1	Jebas	35	80,000	2
2	Thomas	30	40,000	1
3	Mark	25	40,000	2
4	Harry	42	50,000	2
5	Willam	45	100,000	1
6	James	32	35000	3
7	Mock	22	15000	3

SubQuery to display name of employee who get maximum salary.

SELECT 

      Emp_name 

  FROM Employee where salary=(SELECT 

      Max(salary)

  FROM Employee);

Employee

Emp_name

Willam

SubQuery to display name of employee who get Minimum salary.

SELECT 

      Emp_name 

  FROM Employee where salary=(SELECT 

      MIN(salary)

  FROM Employee);

Employee

Emp_name

Mock

Multiple Row SubQuery.

Now we add one extra table called Dept_table.

Dept_table

Dept_id	Dept_name
1	Director
2	Manager
3	Employee
4	HR
5	TeamLeader
6	Developer
7	Tester

To display the name of employee whose designation was manager. The subquery return the manager id as 2 and the outer query return name of employee whose dept_id=2.

SELECT 

      Emp_name 

  FROM Employee where dept_id=(SELECT 

      dept_id

  FROM dept_tablewhere dept_name='Manager');

Employee

Emp_name

Jebas

Mark

Harry

Correlated Subquery.

Employee Table

id	Emp_name	Age	Salary	Dept_id
1	Jebas	35	80,000	2
2	Thomas	30	40,000	1
3	Mark	25	40,000	2
4	Harry	42	50,000	2
5	Willam	45	100,000	1
6	James	32	35000	3
7	Mock	22	15000	3

SELECT 

      Emp_name,salary 

  FROM Employee outer where salary>(SELECT 

     Avg(salary)

  FROM Employee where dept_id=outer.dept_id );

This query return the employee name who get salary more than average salary in their respective department.

Emp_name	Salary
Thomas	80,000
Willam	100,000
James	35000

 

INSERT statement with Subquery 

If we want maintain separate table for Manager.

manager_table

id	Emp_name

We can insert multiple value to manager_table directly from Employee table.

INSERT into manager_table

 SELECT ID,Emp_name from Employee where dept_id IN (SELECT dept_id from dept_table where dept_name ='manager');

The query mark as red color return dept_id of manager and The query mark as green color return Emp_name and id of manager and insert value into manager table 

manager_table

id	Emp_name
2	Thomas
3	Mark
4	Harry

Update Statement with Subquery

UPDATE Employee SET salary = salary+0.25*salary

     where Age in (SELECT Age FROM Employee where Age>40).

This statement increment the salary of employee whose age above 40. The Output:

Employee

id	Emp_name	Age	Salary	Dept_id
1	Jebas	35	80000	2
2	Thomas	30	40000	1
3	Mark	25	40000	2
4	Harry	42	62500	2
5	Willam	45	125000	1
6	James	32	35000	3
7	Mock	22	15000	3

Delete Statement with Subquery

DELETE FROM Employee 

     where Age in (SELECT Age FROM Employee where Age>40).

The statement will remove the employee whose age greater than 40

Thank You. Like Share and Subscribe 👍 folowlect.com