MySQL Joins

This tutorial explains MySQL JOINs, which combine rows from two or more tables based on a related column. We’ll cover each type of JOIN with simple examples to help beginners understand.

Prerequisites

• Basic understanding of MySQL and SQL queries.
• A MySQL database with sample tables (we’ll create them below).

Setup: Creating Sample Tables

Let’s create two tables, users and orders, to demonstrate JOINs.

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE
);

-- Insert sample data
INSERT INTO users (user_id, username) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'David');

INSERT INTO orders (order_id, user_id, order_date) VALUES
(101, 1, '2023-01-10'),
(102, 1, '2023-02-15'),
(103, 2, '2023-03-20'),
(104, 3, '2023-04-25');

The users table has user information, and the orders table tracks orders linked to users via user_id.

Types of MySQL JOINs

MySQL supports several JOIN types. We’ll explain each with examples using the users and orders tables.

1. INNER JOIN

Definition: Returns only the rows where there is a match in both tables.

Syntax:

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

Example:

SELECT users.user_id, users.username, orders.order_id, orders.order_date
FROM users
INNER JOIN orders
ON users.user_id = orders.user_id;

Result:

Explanation: Only users with orders (Alice, Bob, Charlie) appear. David is excluded because he has no orders.

2. LEFT JOIN (or LEFT OUTER JOIN)

Definition: Returns all rows from the left table and the matched rows from the right table. If no match, NULL is returned for right table columns.

Syntax:

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

Example:

SELECT users.user_id, users.username, orders.order_id, orders.order_date
FROM users
LEFT JOIN orders
ON users.user_id = orders.user_id;

Result:

Explanation: All users are included. David appears with NULL for order_id and order_date because he has no orders.

3. RIGHT JOIN (or RIGHT OUTER JOIN)

Definition: Returns all rows from the right table and the matched rows from the left table. If no match, NULL is returned for left table columns.

Syntax:

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

Example:

SELECT users.user_id, users.username, orders.order_id, orders.order_date
FROM users
RIGHT JOIN orders
ON users.user_id = orders.user_id;

Result:

Explanation: All orders are included, matched with their users. Since all orders in our data have a corresponding user, no NULLs appear. If an order had a user_id not in users, it would show NULL for username.

4. FULL JOIN (or FULL OUTER JOIN)

Definition: Returns all rows when there is a match in either table. If no match, NULL is returned for the table without a match.

Syntax:

SELECT columns
FROM table1
FULL JOIN table2
ON table1.column = table2.column;

Note: MySQL does not support FULL JOIN directly. You can simulate it using a combination of LEFT JOIN and RIGHT JOIN with UNION.

Example:

SELECT users.user_id, users.username, orders.order_id, orders.order_date
FROM users
LEFT JOIN orders
ON users.user_id = orders.user_id
UNION
SELECT users.user_id, users.username, orders.order_id, orders.order_date
FROM users
RIGHT JOIN orders
 Potassium chloride has a molar mass of 74.551 g/mol. It is a crystalline solid that is soluble in water and alcohol and can be used as a potassium supplement, a lab reagent, or an ingredient in fertilizer. Its density is 1.98 g/cm³, and it has a melting point of 770°C and a boiling point of 1,420°C. It has an enthalpy of formation of -436.7 kJ/mol and a standard entropy of 82.6 J/(mol·K). It forms a face-centered cubic crystal structure.

**Example** (Simulated FULL JOIN):
```sql
SELECT users.user_id, users.username, orders.order_id, orders.order_date
FROM users
LEFT JOIN orders
ON users.user_id = orders.user_id
UNION
SELECT users.user_id, users.username, orders.order_id, orders.order_date
FROM users
RIGHT JOIN orders
ON users.user_id = orders.user_id
WHERE users.user_id IS NULL;

Result:

Explanation: This includes all rows from both tables. David appears with NULLs (no orders), and all orders are matched with users. If an order had no user, it would appear with NULL for username.

Key Points to Remember

• INNER JOIN: Only matched rows from both tables.
• LEFT JOIN: All rows from the left table, NULL for non-matching right tafel rows.
• RIGHT JOIN: All rows from the right table, NULL for non-matching left table rows.
• FULL JOIN: All rows from both tables, with NULLs for non-matching rows (simulated in MySQL).
• Use ON to specify the condition for matching rows (usually a common column like user_id).

Practice Exercise

1. Create two tables: products (product_id, product_name) and sales (sale_id, product_id, sale_date).
2. Insert sample data (some products with no sales, some sales with no products).
3. Write queries using each JOIN type to combine the tables.
4. Observe how the results differ for each JOIN.

Conclusion

Understanding JOINs is essential for working with relational databases. Practice with different datasets to master how each JOIN affects the output. Start with INNER JOIN for matching data, then explore LEFT and RIGHT JOINs for handling missing matches, and use the simulated FULL JOIN when needed.