Interesting Query (2) --- Whtch Manager has no Employee with

Extend the article: SQL - Interesting Queries as a series of articles:

• Interesting Query (1) --- Find Second Highest Salary
• Interesting Query (2) --- Whtch Manager has no Employee with --- this article
• Interesting Query (3) --- Get Rolling Average

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
  1. by SubQuery
  2. 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