Section 5: Joins and Relationships

In this section, we'll explore the concept of table joins in SQL, which allows us to combine data from multiple tables. Additionally, we'll delve into creating relationships between tables using FOREIGN KEY constraints, ensuring data integrity within the database.

Understanding Table Joins

Introduction to INNER JOIN, LEFT JOIN, RIGHT JOIN

Table joins are essential for retrieving data that spans across multiple tables. There are different types of joins, each serving a specific purpose:

INNER JOIN: Returns only the rows where there is a match in both tables. It filters out unmatched rows.

SELECT employees.employee_id, employees.first_name, employees.last_name, departments.department_name

FROM employees

INNER JOIN departments ON employees.department_id = departments.department_id;

LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and the matched rows from the right table. If there's no match, NULL values are returned for columns from the right table.

SELECT employees.employee_id, employees.first_name, employees.last_name, departments.department_name

FROM employees

LEFT JOIN departments ON employees.department_id = departments.department_id;

RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and the matched rows from the left table. If there's no match, NULL values are returned for columns from the left table.

SELECT employees.employee_id, employees.first_name, employees.last_name, departments.department_name

FROM employees

RIGHT JOIN departments ON employees.department_id = departments.department_id;

Joining Tables Based on Common Columns

The ON clause is used to specify the condition for joining tables based on common columns. For example, joining the orders and customers tables:

SELECT orders.order_id, orders.order_date, customers.customer_name

FROM orders

INNER JOIN customers ON orders.customer_id = customers.customer_id;

This query retrieves order information along with the corresponding customer names.

Creating Relationships with FOREIGN KEY

Defining Relationships Between Tables

In a relational database, relationships between tables are established using foreign keys. A foreign key is a column in one table that refers to the primary key in another table.

For example, creating a foreign key relationship between the orders and customers tables:

-- Adding a foreign key constraint

ALTER TABLE orders

ADD CONSTRAINT fk_customer

FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

This ensures that the customer_id column in the orders table references the customer_id column in the customers table.

Ensuring Data Integrity with FOREIGN KEY Constraints

Foreign key constraints help maintain data integrity by preventing actions that would violate relationships between tables. For instance, trying to insert an order with a non-existent customer:

-- Attempting to insert an order with a non-existent customer_id

INSERT INTO orders (order_id, order_date, customer_id)

VALUES (1001, '2023-01-15', 9999);

This action would fail due to the foreign key constraint, as there's no matching customer_id in the customers table.

Understanding and effectively using table joins and establishing relationships through foreign keys are crucial skills for building robust and interconnected databases. They enable the retrieval of meaningful, consolidated information from various tables while maintaining data integrity.