How to Combine Data Using MySQL Joins

In relational databases, information is often stored across multiple tables rather than in one large table. MySQL joins allow you to combine related records from two or more tables based on a common column.

Learning Objectives

• Understand why joins are used

• Combine related tables using INNER JOIN

• Retrieve all records from the left table using LEFT JOIN

• Retrieve all records from the right table using RIGHT JOIN

• Understand LEFT OUTER JOIN and RIGHT OUTER JOIN

• Join a table to itself using Self Join

• Generate combinations using CROSS JOIN

• Use table aliases to make join queries easier to read

Understanding MySQL Joins

Consider a database with two tables:

Customers

Orders

The common column is:

customer_id

This allows MySQL to associate each order with the corresponding customer.

Creating the Practice Tables

To follow the examples without affecting your existing tables, create two new tables:

USE trainingdbc;

CREATE TABLE JoinCustomers (

    customer_id INT PRIMARY KEY,

    customer_name VARCHAR(100)

);

CREATE TABLE JoinOrders (

    order_id INT PRIMARY KEY,

    customer_id INT,

    product_name VARCHAR(100)

);

Insert sample data:

INSERT INTO JoinCustomers

VALUES

(1, 'John'),

(2, 'Priya'),

(3, 'David'),

(4, 'Sara');

INSERT INTO JoinOrders

VALUES

(101, 1, 'Laptop'),

(102, 2, 'Keyboard'),

(103, 1, 'Mouse'),

(104, 3, 'Monitor');

You can verify the records using:

SELECT * FROM JoinCustomers;

SELECT * FROM JoinOrders;

INNER JOIN

INNER JOIN returns records where the join condition matches in both tables.

SELECT

    c.customer_id,

    c.customer_name,

    o.order_id,

    o.product_name

FROM JoinCustomers AS c

INNER JOIN JoinOrders AS o

    ON c.customer_id = o.customer_id;

The result will contain:

Sara is not returned because she doesn't have a matching order.

LEFT JOIN

LEFT JOIN returns all records from the left table and matching records from the right table.

SELECT

    c.customer_id,

    c.customer_name,

    o.order_id,

    o.product_name

FROM JoinCustomers AS c

LEFT JOIN JoinOrders AS o

    ON c.customer_id = o.customer_id;

The result includes all customers, including Sara.

Because Sara has no matching order, the columns from JoinOrders contain NULL.

LEFT OUTER JOIN

LEFT OUTER JOIN is equivalent to LEFT JOIN.

You can write:

SELECT

    c.customer_name,

    o.product_name

FROM JoinCustomers AS c

LEFT OUTER JOIN JoinOrders AS o

    ON c.customer_id = o.customer_id;

Both forms perform the same type of join.

RIGHT JOIN

RIGHT JOIN returns all records from the right table and matching records from the left table.

SELECT

    c.customer_name,

    o.order_id,

    o.product_name

FROM JoinCustomers AS c

RIGHT JOIN JoinOrders AS o

    ON c.customer_id = o.customer_id;

Since every order in our sample data has a corresponding customer, each order will have customer information.

If the right table contained a record without a matching row on the left, the left-side columns would return NULL.

RIGHT OUTER JOIN

RIGHT OUTER JOIN is equivalent to RIGHT JOIN.

For example:

SELECT

    c.customer_name,

    o.order_id,

    o.product_name

FROM JoinCustomers AS c

RIGHT OUTER JOIN JoinOrders AS o

    ON c.customer_id = o.customer_id;

Self Join

A Self Join joins a table to itself.

This is useful when records within the same table have relationships with other records in that table.

For example, an employee can have another employee as their manager.

Create a table:

CREATE TABLE JoinEmployees (

    employee_id INT PRIMARY KEY,

    employee_name VARCHAR(100),

    manager_id INT

);

Insert sample data:

INSERT INTO JoinEmployees

VALUES

(1, 'Robert', NULL),

(2, 'John', 1),

(3, 'Priya', 1),

(4, 'David', 2);

Now join the table to itself:

SELECT

    e.employee_name AS Employee,

    m.employee_name AS Manager

FROM JoinEmployees AS e

LEFT JOIN JoinEmployees AS m

    ON e.manager_id = m.employee_id;

Example result:

Here, JoinEmployees is used twice:

• e represents the employee.

• m represents the manager.

CROSS JOIN

CROSS JOIN returns every possible combination of rows between two tables.

Consider:

CREATE TABLE Sizes (

    size_name VARCHAR(10)

);

CREATE TABLE Colors (

    color_name VARCHAR(20)

);

Insert:

INSERT INTO Sizes

VALUES ('Small'), ('Medium');

INSERT INTO Colors

VALUES ('Black'), ('White');

Run:

SELECT

    s.size_name,

    c.color_name

FROM Sizes AS s

CROSS JOIN Colors AS c;

The result will contain:

Two sizes × two colors produce four combinations.

CROSS JOIN should be used carefully with large tables because the number of returned rows can increase quickly.

INNER JOIN vs LEFT JOIN vs RIGHT JOIN

Using Aliases with Joins

Aliases make queries involving multiple tables shorter and easier to read.

Instead of repeatedly writing:

JoinCustomers.customer_name

you can define:

JoinCustomers AS c

and then use:

c.customer_name

For example:

SELECT

    c.customer_name,

    o.product_name

FROM JoinCustomers AS c

INNER JOIN JoinOrders AS o

    ON c.customer_id = o.customer_id;

Aliases are particularly useful when multiple tables contain columns with similar names.

Common Mistakes

When working with joins, avoid:

• Joining tables using unrelated columns

• Forgetting the ON condition for joins that require one

• Using ambiguous column names without table aliases

• Using INNER JOIN when unmatched records also need to be displayed

• Confusing which table is the left or right table

• Forgetting that unmatched outer-join columns can contain NULL

• Using CROSS JOIN on large tables without considering the number of combinations