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).
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
| 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:
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.
