INTERSECT equivalent in MySQL - Mini Project: Build & Apply

Using INTERSECT Equivalent in MySQL

📖 Scenario: You work in a company database where you have two tables: employees_2023 and employees_2024. You want to find employees who worked in both years.

🎯 Goal: Create a query that finds the common employees between employees_2023 and employees_2024 using MySQL's equivalent of the INTERSECT operation.

📋 What You'll Learn

Create two tables named employees_2023 and employees_2024 with a single column employee_name.

Insert the exact employee names into each table as specified.

Create a variable or temporary table to hold the common employees.

Write a query using INNER JOIN to find employees present in both tables.

Select the common employee names from the join.

💡 Why This Matters

🌍 Real World

Finding common records between two datasets is common in business to identify overlapping customers, employees, or products across different time periods or departments.

💼 Career

Database developers and analysts often need to perform set operations like INTERSECT, and knowing how to do this in MySQL without native INTERSECT support is important.

1

Create the employees_2023 and employees_2024 tables with data

Create two tables called employees_2023 and employees_2024 each with one column employee_name of type VARCHAR(50). Insert these exact employee names into employees_2023: 'Alice', 'Bob', 'Charlie'. Insert these exact employee names into employees_2024: 'Bob', 'Charlie', 'David'.

MySQL

-- Create tables employees_2023 and employees_2024 and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to make tables and INSERT INTO to add the exact names.

2

Create a temporary table to hold common employees

Create a temporary table called common_employees with one column employee_name of type VARCHAR(50) to store employees who worked in both years.

MySQL

CREATE TABLE employees_2023 (employee_name VARCHAR(50));
CREATE TABLE employees_2024 (employee_name VARCHAR(50));

INSERT INTO employees_2023 (employee_name) VALUES ('Alice'), ('Bob'), ('Charlie');
INSERT INTO employees_2024 (employee_name) VALUES ('Bob'), ('Charlie'), ('David');

-- Create temporary table common_employees
-- Your code here

Need a hint?

Use CREATE TEMPORARY TABLE to create a temporary table for common employees.

3

Insert common employees using INNER JOIN

Insert into common_employees the employee_name values that appear in both employees_2023 and employees_2024 by using an INNER JOIN on employee_name.

MySQL

CREATE TABLE employees_2023 (employee_name VARCHAR(50));
CREATE TABLE employees_2024 (employee_name VARCHAR(50));

INSERT INTO employees_2023 (employee_name) VALUES ('Alice'), ('Bob'), ('Charlie');
INSERT INTO employees_2024 (employee_name) VALUES ('Bob'), ('Charlie'), ('David');

CREATE TEMPORARY TABLE common_employees (employee_name VARCHAR(50));

-- Insert common employees using INNER JOIN
-- Your code here

Need a hint?

Use INSERT INTO ... SELECT ... INNER JOIN to find common names.

4

Select the common employees from the temporary table

Write a SELECT query to retrieve all employee_name values from the common_employees temporary table.

MySQL

CREATE TABLE employees_2023 (employee_name VARCHAR(50));
CREATE TABLE employees_2024 (employee_name VARCHAR(50));

INSERT INTO employees_2023 (employee_name) VALUES ('Alice'), ('Bob'), ('Charlie');
INSERT INTO employees_2024 (employee_name) VALUES ('Bob'), ('Charlie'), ('David');

CREATE TEMPORARY TABLE common_employees (employee_name VARCHAR(50));

INSERT INTO common_employees (employee_name)
SELECT e23.employee_name
FROM employees_2023 e23
INNER JOIN employees_2024 e24 ON e23.employee_name = e24.employee_name;

-- Select all employee_name from common_employees
-- Your code here

Need a hint?

Use SELECT employee_name FROM common_employees to see the common employees.