Skip to main content

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

Comments