Post
Ask Question

SQL - Interesting Queries (2)

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

Introduction

I got an interview question, and found the solution is either interesting and similar to what I have in SQL - Interesting Queries (1). Then, I have this article. The question is like this

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

 

Question

Which managers (Name) have no employees associated with?

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


Similar Articles