Understanding Foreign Key Relationships and JOIN Operations in SQL

Introduction

In relational database management systems, establishing relationships between tables is crucial for maintaining data integrity and enabling complex queries. In this blog, we will create two tables with a foreign key relationship, insert data into both, and write a query that retrieves data using a JOIN operation.

Prerequisites

• Basic knowledge of SQL syntax.
• Access to a SQL database management system (like MySQL, PostgreSQL, etc.).

Step 1: Create Two Tables

For this example, we will create two tables: departments and employees. The departments table will contain information about each department in a company, while the employees table will store employee records linked to their respective departments.

Creating the departments Table

First, we will create the departments table with the following columns: department_id, department_name, and location.

CREATE TABLE departments (
    department_id INT PRIMARY KEY AUTO_INCREMENT,
    department_name VARCHAR(100) NOT NULL,
    location VARCHAR(100)
);

Creating the employees Table

Next, we will create the employees table, which will include a foreign key that references the department_id in the departments table.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    position VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Step 2: Insert Data into the Tables

Now that we have our tables set up, let’s insert some data.

Inserting Data into the departments Table

INSERT INTO departments (department_name, location)
VALUES
('Human Resources', 'New York'),
('Engineering', 'San Francisco'),
('Marketing', 'Chicago'),
('Sales', 'Los Angeles');

Inserting Data into the employees Table

Next, we’ll insert some employees, ensuring that their department_id corresponds to the relevant departments.

INSERT INTO employees (name, position, department_id, salary)
VALUES
('Alice Johnson', 'HR Manager', 1, 75000.00),
('Bob Smith', 'Software Engineer', 2, 90000.00),
('Charlie Evans', 'Marketing Specialist', 3, 65000.00),
('Diana Roberts', 'Sales Associate', 4, 55000.00),
('Ethan White', 'Data Engineer', 2, 85000.00);

Step 3: Retrieving Data with a JOIN Operation

Now that we have data in both tables, we can use a JOIN operation to retrieve information from both tables. In this case, we want to list employees along with their department names.

Writing the JOIN Query

We will use an INNER JOIN to combine rows from both the employees and departments tables where the department_id matches.

SELECT 
    e.name AS employee_name,
    e.position,
    d.department_name,
    d.location,
    e.salary
FROM 
    employees e
INNER JOIN 
    departments d ON e.department_id = d.department_id;

Explanation of the JOIN Query

• SELECT: Specifies the columns to be retrieved. We’re selecting the employee’s name, position, department name, location, and salary.
• FROM: Indicates the primary table from which to retrieve data (employees).
• INNER JOIN: Combines rows from the employees table with matching rows from the departments table based on the department_id.
• ON: Specifies the condition for the JOIN operation, linking the department_id from both tables.

Step 4: Execute the Query

When you execute the above JOIN query, you will get a result set that displays each employee’s details along with their respective department information.

Conclusion

In this blog post, we learned how to create two tables with a foreign key relationship, insert data into both tables, and perform a JOIN operation to retrieve related data. Understanding foreign key relationships and how to perform JOIN queries is essential for effective database management and querying.