MySQL JOINS Overview

A JOIN in MySQL combines rows from two or more tables based on a related column between them. Instead of storing all information in one large table, databases split data into separate tables and use JOINs to bring related data together when needed.

Why Use Joins?

Storing everything in one table leads to repetition and inconsistency. For example, storing customer details repeatedly in every order row wastes space and makes updates difficult. Instead, customer data stays in a customers table, and orders stay in an orders table. A JOIN retrieves the combined view when required.

Simple Analogy

Think of two filing cabinets — one with customer details and one with order receipts. A JOIN is like a clerk who looks up a customer's name from the first cabinet and attaches it to the matching receipt from the second cabinet.

Types of JOINs in MySQL

Sample Tables Used in Join Examples

Table: customers

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

INSERT INTO customers VALUES (1, 'Anita Roy');
INSERT INTO customers VALUES (2, 'Suresh Mehta');
INSERT INTO customers VALUES (3, 'Priya Nair');
INSERT INTO customers VALUES (4, 'Vikram Rao');

Table: orders

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_item VARCHAR(100),
    customer_id INT
);

INSERT INTO orders VALUES (101, 'Laptop', 1);
INSERT INTO orders VALUES (102, 'Phone', 2);
INSERT INTO orders VALUES (103, 'Tablet', 1);
INSERT INTO orders VALUES (104, 'Headphones', 5);  -- customer 5 does not exist

How JOIN Works

The ON keyword specifies the condition that links the two tables — usually the matching of a foreign key with a primary key.

SELECT customers.customer_name, orders.order_item
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id;

MySQL looks at each row in both tables and matches rows where customers.customer_id equals orders.customer_id.

Visual Overview of Join Types

• INNER JOIN — Only the overlapping section (rows matched in both tables)
• LEFT JOIN — Everything from the left table + matching rows from right table
• RIGHT JOIN — Everything from the right table + matching rows from left table
• CROSS JOIN — Every possible row combination from both tables

JOIN with Table Aliases

Aliases simplify JOIN queries by shortening table names.

SELECT c.customer_name, o.order_item
FROM customers AS c
JOIN orders AS o ON c.customer_id = o.customer_id;

Key Points

• JOINs combine data from two or more related tables into a single result set.
• The ON clause defines the condition used to match rows between tables.
• INNER JOIN returns only matched rows; outer joins return unmatched rows as well.
• Table aliases keep JOIN queries readable and concise.
• Understanding JOINs is essential for working with normalized, multi-table databases.