Nested select sql multiple subqueries

Today, We want to share with you nested select sql.In this post we will show you sql nested queries practice, hear for sql nested select join we will give you demo and example for implement.In this post, we will learn about nested select sql with an example.

Nested select statement in SQL Server

There are mainly two types of nested queries:

  • Independent Nested Queries
  • Co-related Nested Queries

What Is a Nested Query in SQL?

A nested SELECT is a query within a query for example when you have a simple SELECT statement within the main base SELECT statement.

To create the full concept clearer, let’s go through basic and Advance level an example together.

In this article, we’re going to work with data from a fictional high big compines. The database includes three tables: employees, owners, and Companies. You can see the Database tables below:

Employees

id name class_id RPT
1 Jagruti Bridhari 3 3.45
2 Daniel White 1 3.15
3 Kartik Dameliya 1 3.85
4 Hiren Mugra 2 3.10
5 Sanjay dabhi 2 2.40

Owners

id name Departments class_id monthly_salary
1 Mehuk Sarivya Devloper 3 2,500
2 Ravi Patodiya SEO [NULL] 2,000
3 Sejal Ramani Tester 1 2,350
4 Geet Pipariya Managements 2 3,000

Companies

id grade owner_id number_of_employees
1 10 3 21
2 11 4 25
3 12 1 28

Let’s say you want to find all employees that have above-average RPTs. However, you don’t know the average RPT score. Certainly, you can use a query to find out:

SELECT AVG(RPT)
FROM employees;

You’ll get a number (3.19) that you can use to solve the initial task – showing all information for employees with a RPT above this average:

SELECT *
FROM employees
WHERE RPT > 3.19;

But can you solve this task in one step? You can with a nested query. Here is how it looks:

SELECT *
FROM employees
WHERE RPT > (
    SELECT AVG(RPT)
    FROM employees);

Our subquery here returns a single value (i.e. a table with a single column and a single row). This is important for the comparison operator to work. With the average RPT score returned by the inner query, the outer query can select the employees who satisfy our filter condition (i.e. a RPT score above average).

Read Also:  PHP Laravel Get Last Inserted ID in MySQL Table

And here is the result:

id name class_id RPT
1 Jagruti Bridhari 3 3.45
3 Kartik Dameliya 1 3.85

SQL – Sub Queries

Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE All the Basic statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc. Subqueries with the DELETE Statement

SQL> DELETE FROM EMPLOYEES
   WHERE AGE IN (SELECT AGE FROM EMPLOYEES_BKP
      WHERE AGE >= 27 );

Subqueries with the UPDATE Statement

SQL> UPDATE EMPLOYEES
   SET SALARY = SALARY * 0.25
   WHERE AGE IN (SELECT AGE FROM EMPLOYEES_BKP
      WHERE AGE >= 27 );

Subqueries with the INSERT Statement

SQL> INSERT INTO EMPLOYEES_BKP
   SELECT * FROM EMPLOYEES 
   WHERE ID IN (SELECT ID 
   FROM EMPLOYEES) ;

Subqueries with the SELECT Statement

SQL> SELECT * 
   FROM EMPLOYEES 
   WHERE ID IN (SELECT ID 
         FROM EMPLOYEES 
         WHERE SALARY > 4500) ;

Nested select statement in SQL Server

alias the subquery.

SELECT name FROM (SELECT name FROM EMPLOYEES) a  

more explicit

SELECT a.name FROM (SELECT name FROM EMPLOYEES) a  

SQL Nested subqueries

Example -1 : Nested subqueries

SELECT dept_id,AVG(salary) 
FROM employees   
GROUP BY dept_id   
HAVING AVG(salary)<           
(SELECT MAX(AVG(min_salary))            
FROM depts             
WHERE dept_id IN                 
(SELECT dept_id FROM dept_history                  
WHERE department_id                   
BETWEEN 50 AND 100)             
GROUP BY dept_id);

Oracle 11g Express Edition.

SELECT dept_id,AVG(salary) 
SELECT dept_id,AVG(salary) 
FROM employees   
GROUP BY dept_id   
HAVING AVG(salary)<
(SELECT MAX(myavg) from (select dept_id,AVG(min_salary) as myavg
FROM depts             
WHERE dept_id IN                 
(SELECT dept_id FROM dept_history                  
WHERE department_id                   
BETWEEN 50 AND 100)            
GROUP BY dept_id) ss);

How to create a nested SELECT in SQL

CREATE TABLE Member (
    MemberID int,
    LastName varchar(255),
    FirstName varchar(255),
    City varchar(255) 
);

INSERT INTO Member (MemberID, LastName, FirstName, City)
VALUES (1 , 'Rakesh', 'Savaliya', 'Rajkot'), 
(2 , 'Dipak', 'Dhameliya', 'Kalavad'),
(3 , 'Vijay', 'Chovatiya', 'Surat');

-- Nested SELECT ------

SELECT A.MemberID FROM (
    SELECT MemberID, LastName, FirstName FROM Member
  ) AS A;

In nested queries, a query is written inside a query. The result of inner query is used in execution of outer query. We will use EMPLOYEE, COMPANY, EMPLOYEE_COMPANY tables for understanding nested queries.
EMPLOYEE

Read Also:  PHP Multi languageSupport To Website Management
EMP_ID EMP_NAME EMP_ADDRESS EMP_PHONE EMP_AGE
EMP1 KISHAN AHEMDABAD 963815547* 18
EMP2 DIVYESH GANDHINAGAR 989856854* 18
EMP3 YASH KALAVAD 985858758* 20
EMP4 MANOJ RAJKOT 989822507* 18

COMPANY

COMP_ID COMP_NAME
COMP1 ANG
C2 Programming
COMP3 Angular

EMPLOYEE_COMPANY

EMP_ID COMP_ID
EMP1 COMP1
EMP1 COMP3
EMP2 COMP1
EMP3 C2
EMP4 C2
EMP4 COMP3

Independent Nested Queries

IN: If we want to find out EMP_ID who are enrolled in COMP_NAME ‘ANG’ or ‘Angular’, we can write it with the help of independent nested query and IN operator. From COMPANY table, we can find out COMP_ID for COMP_NAME ‘ANG’ or Angular’ and we can use these COMP_IDs for finding EMP_IDs from EMPLOYEE_COMPANY TABLE.

STEP 1: Finding COMP_ID for COMP_NAME =’ANG’ or ‘Angular’

Select COMP_ID from COMPANY where COMP_NAME = ‘ANG’ or COMP_NAME = ‘Angular’

STEP 2: Using COMP_ID of step 1 for finding EMP_ID

Select EMP_ID from EMPLOYEE_COMPANY where COMP_ID IN

(SELECT COMP_ID from COMPANY where COMP_NAME = ‘ANG’ or COMP_NAME=’Angular’);

The inner query will return a set with members COMP1 and COMP3 and outer query will return those EMP_IDs for which COMP_ID is equal to any member of set (COMP1 and COMP3 in this case). So, it will return EMP1, EMP2 and EMP4.

Note: If we want to find out names of EMPLOYEEs who have either enrolled in ‘ANG’ or ‘Angular’, it can be done as:

Select EMP_NAME from EMPLOYEE where EMP_ID IN

(Select EMP_ID from EMPLOYEE_COMPANY where COMP_ID IN

(SELECT COMP_ID from COMPANY where COMP_NAME=’ANG’ or COMP_NAME=’Angular’));

NOT IN: If we want to find out EMP_IDs of EMPLOYEEs who have neither enrolled in ‘ANG’ nor in ‘Angular’, it can be done as:

Read Also:  Laravel Vue JS ajax file upload multipart form data

Select EMP_ID from EMPLOYEE where EMP_ID NOT IN

(Select EMP_ID from EMPLOYEE_COMPANY where COMP_ID IN

(SELECT COMP_ID from COMPANY where COMP_NAME=’ANG’ or COMP_NAME=’Angular’))

The innermost query will return a set with members COMP1 and COMP3. Second inner query will return those EMP_IDs for which COMP_ID is equal to any member of set (COMP1 and COMP3 in this case) which are EMP1, EMP2 and EMP4. The outermost query will return those EMP_IDs where EMP_ID is not a member of set (EMP1, EMP2 and EMP4). So it will return EMP3.

Co-related Nested Queries

In co-related nested queries, the output of inner query depends on the row which is being currently executed in outer query. e.g.; If we want to find out EMP_NAME of EMPLOYEEs who are enrolled in COMP_ID ‘COMP1’, it can be done with the help of co-related nested query as:

Select EMP_NAME from EMPLOYEE S where EXISTS

( select * from EMPLOYEE_COMPANY ECMY where S.EMP_ID=ECMY.EMP_ID and ECMY.COMP_ID=’COMP1’);

For each row of EMPLOYEE S, it will find the rows from EMPLOYEE_COMPANY where S.EMP_ID = ECMY.EMP_ID and ECMY.COMP_ID=’COMP1’. If for a EMP_ID from EMPLOYEE S, atleast a row exists in EMPLOYEE_COMPANY ECMY with COMP_ID=’COMP1’, then inner query will return true and corresponding EMP_ID will be returned as output.

I hope you get an idea about nested select sql.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.