Extend the article: SQL - Interesting Queries as a series of articles:
Introduction
SQL, the Structural Query Language, seems simple, but often with some tricks in them. This article will discuss a issue that is similar to what I have in SQL - Interesting Queries (1).
The content of this article:
- Introduction
- Question
- Setup Table and Initial Data
- Answers
- by SubQuery
- by Left Join
Question:
Two tables:
![]()
where, the ManagerId in Table Employees is a foreign key and point to the primary key, Id, in Table Managers.
Question:
which managers in Table Managers do not have any Employees associated with.
Set up Table and initial Data
Table Employees:
USE [TestDB]
GO
/****** Object: Table [dbo].[Employees] Script Date: 12/22/2022 6:07:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employees](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
[Salary] [int] NOT NULL,
[ManagerId] [int] NOT NULL,
CONSTRAINT [PK_dbo.Employees] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Employees] WITH NOCHECK ADD CONSTRAINT [FK_Employees_Employees] FOREIGN KEY([ManagerId])
REFERENCES [dbo].[Manager] ([Id])
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_Employees]
GO
Table Managers:
USE [TestDB]
GO
/****** Object: Table [dbo].[Managers] Script Date: 12/22/2022 6:11:50 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Managers](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nchar](10) NULL,
CONSTRAINT [PK_Manager] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
For consistency, we add the data into Table Manaagers first:
Insert initial Data for Managers:
insert dbo.Managers
Values ('Greg'),
('Tom'),
('David'),
('Sam'),
('Mary')
The result:
![]()
Insert initial Data for Employees:
insert dbo.Employees
Values ('Greg', 100000, 1),
('George', 150000, 1),
('Helen', 130000, 1),
('Tom', 120000, 2),
('Kevin', 110000, 2),
('David', 120000, 3),
('Geek', 110000,3),
('Tesla', 120000,3),
('David', 120000,3),
('Kevin', 110000,2)
The result will be
![]()
Answers:
Answer 1, by subquery
We can divide Table Managers as two groups,
- One group, the managers have employees associated with,
- Another group, the managers have no employees associated with.
The second group is what we want, while the first group can be got from Table Employees:
SELECT ManagerId
FROM Employees
Therefore, we can get the manager names of the second group from Table Managers by excluding the first group, through a subquery:
SELECT Name
FROM Managers
WHERE Id NOT IN (SELECT ManagerID
FROM Employees)
Result
![]()
Answer 2, by Left Join
Considering the first group, the manager names can be got from an inner join:
SELECT DISTINCT m.Name
FROM Managers m
INNER JOIN Employees e
ON m.Id = e.ManagerId
that is equivalent to a Left Outer Join as
SELECT DISTINCT m.Name
FROM Managers m
LEFT OUTER JOIN Employees e
ON m.Id = e.ManagerId
WHERE e.ManagerId IS NOT NULL
Then, we can get the second group as
SELECT DISTINCT m.Name
FROM Managers m
LEFT OUTER JOIN Employees e
ON m.Id = e.ManagerId
WHERE e.ManagerId IS NULL
Result is the same as
![]()