What is Nested Select Statement in SQL Server

SQL Server is a powerful relational database management system. Sql Server is very good with its robustness and scalability. SQL Server operates as a client-server structure, imparting centralized management and scalability for huge-scale applications and enterprise-stage solutions. It offers advanced features which include excessive availability, protection, and extensive support for complex data operations.

In this article, we will go to cover the nested select statements in SQL Server, unraveling their functionality and demonstrating their versatility through complete examples and use cases.

Nested Select Statement in SQL Server

In SQL Server, a Nested Select statement is essentially a select statement contained within another select statement. This structure comprises a parent query and a subquery. Typically, the outcome of the parent query relies on the results of the subquery. Nested select statements are commonly utilized for filtering results, a fundamental requirement in various data analysis tasks. They allow us to refine and narrow down the data set based on specific criteria, contributing to more precise and insightful analyses.

Syntax:

SELECT column_name_1
FROM table_name_1
WHERE column_name_1 = (SELECT column_name_2 FROM table_name_2);

Create a Table and Insert data

First, we will have to create at least two tables in the database to understand the examples of nested select statements in SQL Server. To create the tables in the database we will have to perform the following quires:

Query to create first table.

Query:

CREATE TABLE employee (
    EmployeeID INTEGER,
    FirstName VARCHAR(100),
    LastName VARCHAR(100),
    DepartmentID INTEGER,
    Position VARCHAR(100),
    Salary DECIMAL(10, 2)
);

Query to create second table.

Query:

CREATE TABLE Department (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(100)
);

Now that our tables are created, we have to insert data into our tables. To insert data into our tables we will write and run the following quires.

Inserting data in 1st table

To insert data in first table write the following query:

Query:

INSERT INTO Employee (EmployeeID, FirstName, LastName, DepartmentID, Position, Salary)
VALUES
    (1, 'John', 'Doe', 1, 'Sales Representative', 50000.00),
    (2, 'Jane', 'Smith', 2, 'Marketing Manager', 75000.00),
    (3, 'Michael', 'Johnson', 1, 'Sales Manager', 80000.00),
    (4, 'Emily', 'Williams', 3, 'HR Specialist', 60000.00),
    (5, 'David', 'Brown', 4, 'Financial Analyst', 70000.00);

Output:

Inserted Data in Table1

Inserting data in 2st table

To insert data in second first table write the following query:

Query:

INSERT INTO Department (DepartmentID, DepartmentName)
VALUES
    (1, 'Sales'),
    (2, 'Marketing'),
    (3, 'Human Resources'),
    (4, 'Finance');

Output:

Inserted Data in Table2

Example of Nested select statement in SQL Server

The inner subquery (SELECT DepartmentID FROM Department WHERE DepartmentName = ‘Sales’) retrieves the DepartmentID for the ‘Sales’ department from the Department table. The outer query then selects the FirstName and LastName of employees whose DepartmentID matches the one obtained from the subquery, effectively fetching employees only from the Sales department.

Syntax:

SELECT columns
FROM table
WHERE column OPERATOR (SELECT columns FROM table WHERE condition);

Query:

SELECT FirstName, LastName
FROM Employee
WHERE DepartmentID = (SELECT DepartmentID FROM Department WHERE DepartmentName = 'Sales');

Output:

Output from Nested select statement

Explanation:

From the above Nested select query retrieves the FirstName and LastName of employees who work in the ‘Sales’ department, by filtering the Employee table based on the DepartmentID obtained from the Department table through the subquery.

Conclusion

In conclusion, nested SELECT statements in SQL Server offer an effective method to carry out complex queries through embedding one SELECT statement within another. Nested SELECT statements can be used to clear out, aggregate, or join tables, providing flexibility and efficiency in querying databases. By nesting SELECT statements, developers can write greater concise and structured SQL queries. Overall, getting to know the use of nested SELECT statements is vital for SQL developers in search of to extract meaningful insights from relational databases.

Article Tags :

Databases

SQL Server

SQL Server Query