Aman kant Mahto's Blog
Establishing Table Relationships and Using JOINs in SQL

Establishing Table Relationships and Using JOINs in SQL

Aman kant Mahto's photo
Aman kant Mahto
·

3 min read

Overview

This documentation covers the process of creating related tables in a relational database management system (RDBMS) using foreign keys. Additionally, it demonstrates how to use JOIN operations to retrieve related data across multiple tables. This approach is essential for maintaining data integrity and enabling complex queries that pull data from related tables.

Table of Contents

  1. Introduction

  2. Prerequisites

  3. Step 1: Create Two Tables with Foreign Key Relationship

  4. Step 2: Insert Data into the Tables

  5. Step 3: Retrieve Data Using a JOIN Operation

  6. Conclusion

1. Introduction

In relational databases, establishing relationships between tables is fundamental for maintaining organized, consistent data. Foreign keys link records in one table to related records in another, while JOIN operations allow us to query and retrieve data across these tables effectively. This guide demonstrates creating two tables with a foreign key relationship, inserting data, and retrieving information using JOINs.

2. Prerequisites

To follow this documentation, you will need:

  • Basic understanding of SQL syntax.

  • Access to a SQL database management system, such as MySQL or PostgreSQL.

3. Step 1: Create Two Tables with Foreign Key Relationship

We will create two tables for this example: departments and employees. The departments table will store department information, while the employees table will include a foreign key referencing the department_id in departments.

Creating the departments Table

The departments table will include department_id (as the primary key), department_name, and location columns.

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

Creating the employees Table

The employees table includes an employee_id as the primary key, along with columns for name, position, department_id, and salary. The department_id column acts as a foreign key that references 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)
);

4. Step 2: Insert Data into the Tables

Once the tables are set up, we can insert sample data.

Inserting Data into the departments Table

Add records to the departments table using:

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

Insert employee records with a valid department_id that corresponds to existing department entries:

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);

5. Step 3: Retrieve Data Using a JOIN Operation

With data in both tables, we can now write a query that uses a JOIN to retrieve related information across employees and departments.

Writing the JOIN Query

The following INNER JOIN query combines data from employees and departments where 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, including employee name, position, department name, location, and salary.

  • FROM: Identifies the primary table (employees) from which data is pulled.

  • INNER JOIN: Combines rows in employees with matching rows in departments based on department_id.

  • ON: Defines the condition for the JOIN, linking department_id from both tables.

6. Conclusion

This documentation demonstrated creating tables with a foreign key relationship, inserting data into these tables, and using JOIN operations to retrieve related data. Understanding foreign keys and JOINs is fundamental for effective data management and querying in relational databases, helping you pull complex, related data efficiently.