SQL Server 2019 (Linux)

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    location_id INT,
    department_name VARCHAR(50)
);

INSERT INTO departments (dept_id, location_id, department_name)
VALUES
    (1, 100, 'HR'),
    (2, 200, 'Finance'),
    (3, 100, 'IT'),
    (4, 300, 'Marketing');

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    dept_id INT,
    location_id INT
);

INSERT INTO employees (employee_id, first_name, last_name, dept_id, location_id)
VALUES
    (101, 'John', 'Doe', 1, 100),
    (102, 'Jane', 'Smith', 2, 200),
    (103, 'Michael', 'Johnson', 1, 100),
    (104, 'Emily', 'Williams', 3, 100);

CREATE TABLE locations (
    location_id INT PRIMARY KEY,
    city VARCHAR(50),
    country VARCHAR(50)
);

INSERT INTO locations (location_id, city, country)
VALUES
    (100, 'New York', 'USA'),
    (200, 'London', 'UK'),
    (300, 'Paris', 'France');
11 rows affected

SELECT * FROM departments


dept_id
location_id
department_name


1
100
HR

2
200
Finance

3
100
IT

4
300
Marketing


SELECT * FROM employees


employee_id
first_name
last_name
dept_id
location_id


101
John
Doe
1
100

102
Jane
Smith
2
200

103
Michael
Johnson
1
100

104
Emily
Williams
3
100


SELECT * FROM locations


location_id
city
country


100
New York
USA

200
London
UK

300
Paris
France


SELECT d.dept_id
	,d.department_name
	,employee_id
	,first_name
	,last_name
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
	AND d.location_id = e.location_id


dept_id
department_name
employee_id
first_name
last_name


1
HR
101
John
Doe

1
HR
103
Michael
Johnson

2
Finance
102
Jane
Smith

3
IT
104
Emily
Williams

4
Marketing
null
null
null


SELECT d.dept_id
	,d.department_name
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
	AND d.location_id = e.location_id
WHERE e.employee_id IS NULL;


dept_id
department_name


4
Marketing

employee_id	first_name	last_name	dept_id	location_id
101	John	Doe	1	100
102	Jane	Smith	2	200
103	Michael	Johnson	1	100
104	Emily	Williams	3	100