MySQL - Inner Join

MySQL - INNER JOIN

Understanding MySQL INNER JOIN

The INNER JOIN is the most common type of join in SQL. It combines rows from two or more tables based on a related column between them. The result includes only the rows where the values in the joined columns match in both tables.

You can visualize an INNER JOIN as the intersection of two sets. If you imagine two overlapping circles representing your tables, the INNER JOIN returns only the data that exists in the overlapping area where the join condition is met.

The keyword INNER is optional. If you just use JOIN, MySQL will perform an INNER JOIN by default. However, explicitly writing INNER JOIN is considered good practice as it improves the readability of your query.

Syntax

The basic syntax for an INNER JOIN is as follows:

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

The ON clause specifies the condition that links the two tables. This is typically a comparison between the primary key of one table and the corresponding foreign key in the other.

Syntax and Basic Example

Let’s create a classic example with a customers table and an orders table. We want to list all orders along with the name of the customer who placed them.

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATETIME NOT NULL,
    customer_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
      ON DELETE CASCADE
);

Notice the FOREIGN KEY constraint. This enforces relational integrity, ensuring that every customer_id in the orders table corresponds to an actual customer. Now, let’s insert some data.

INSERT INTO customers (name, email) VALUES
(1, 'Alice Smith', 'alice@example.com'),
(2, 'Bob Johnson', 'bob@example.com'),
(3, 'Charlie Brown', 'charlie@example.com');

INSERT INTO orders (order_date, customer_id, amount) VALUES
('2023-10-26 10:00:00', 1, 99.50),
('2023-10-26 11:30:00', 2, 45.00),
('2023-10-27 14:00:00', 1, 120.00),
('2023-10-28 09:00:00', 3, 250.75);

To get a list of orders with customer names, we join the tables on customers.id = orders.customer_id.

SELECT
    c.name AS customer_name,
    o.order_id,
    o.order_date,
    o.amount
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id;

Output

The result will only include customers who have placed orders.

customer_name	order_id	order_date	amount
Alice Smith	1	2023-10-26 10:00:00	99.50
Bob Johnson	2	2023-10-26 11:30:00	45.00
Alice Smith	3	2023-10-27 14:00:00	120.00
Charlie Brown	4	2023-10-28 09:00:00	250.75

Joining Multiple Tables

You can chain INNER JOIN clauses to combine more than two tables. For instance, let’s add an order_items table and join all three to see which products were in each order.

CREATE TABLE order_items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

INSERT INTO order_items (order_id, product_name, quantity) VALUES
(1, 'Laptop', 1),
(2, 'Mouse', 1),
(2, 'Keyboard', 1),
(3, 'Monitor', 1);

-- Join three tables
SELECT
    c.name AS customer_name,
    o.order_id,
    oi.product_name,
    oi.quantity
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id
INNER JOIN order_items AS oi ON o.order_id = oi.order_id;

Combining INNER JOIN with a WHERE Clause

You can add a WHERE clause after the join clauses to further filter the results. For example, to find all orders placed by ‘Alice Smith’:

SELECT
    c.name AS customer_name,
    o.order_id,
    o.amount
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id
WHERE c.name = 'Alice Smith';

Performance and Best Practices

The Importance of Indexing

For INNER JOIN to be performant, the columns used in the ON clause must be indexed. When you define a PRIMARY KEY or a UNIQUE constraint, MySQL automatically creates an index. FOREIGN KEY columns should also always be indexed. Without an index, MySQL has to perform a full table scan for each row of the other table, which is extremely slow on large datasets.

Common Mistakes

• Ambiguous Columns: If both tables have a column with the same name (e.g., id), you must qualify it with the table name or alias (e.g., customers.id). Failing to do so results in an ‘ambiguous column’ error.
• Accidental Cross Join: Forgetting the ON clause entirely will turn your INNER JOIN into a CROSS JOIN, producing a Cartesian product and likely incorrect results.
• Using WHERE instead of ON: While SELECT * FROM t1, t2 WHERE t1.id = t2.id can produce the same result, it is an outdated syntax. The modern JOIN ... ON syntax is preferred because it separates the joining logic (ON) from the filtering logic (WHERE), making queries much easier to read and maintain.

Using INNER JOIN in Application Code

When executing joins from an application, use prepared statements, especially if the WHERE clause contains user input.

PHP (PDO)
Node.js (mysql2)
Java (JDBC)
Python (mysql-connector)



Use a prepared statement to securely pass filter values to your join query.

```php
<?php
// ... (PDO connection setup)
try {
    $pdo = new PDO($dsn, $user, $pass, $options);

    $sql = "SELECT c.name, o.order_id, o.amount FROM customers AS c " .
           "INNER JOIN orders AS o ON c.id = o.customer_id WHERE c.id = :customer_id";

    $stmt = $pdo->prepare($sql);
    $stmt->execute(['customer_id' => 1]);

    echo "Orders for customer ID 1:\n";
    foreach ($stmt->fetchAll() as $row) {
        print_r($row);
    }

} catch (\PDOException $e) { /* ... error handling ... */ }
?>

Use mysql2/promise with async/await for clean, non-blocking database access.

const mysql = require('mysql2/promise');

async function getCustomerOrders(customerId) {
    let connection;
    try {
        connection = await mysql.createConnection({ /* ... connection config ... */ });

        const sql = 'SELECT c.name, o.order_id, o.amount FROM customers AS c ' +
                    'INNER JOIN orders AS o ON c.id = o.customer_id WHERE c.id = ?';

        const [rows] = await connection.execute(sql, [customerId]);

        console.log(`Orders for customer ID ${customerId}:`);
        console.log(rows);

    } catch (error) {
        console.error('Query failed:', error);
    } finally {
        if (connection) await connection.end();
    }
}

getCustomerOrders(1);

Use a PreparedStatement to safely include parameters in your query.

import java.sql.*;

public class InnerJoinExample {
    public static void main(String[] args) {
        String sql = "SELECT c.name, o.order_id, o.amount FROM customers AS c " +
                     "INNER JOIN orders AS o ON c.id = o.customer_id WHERE c.id = ?";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, 1); // Set the customer_id parameter
            try (ResultSet rs = stmt.executeQuery()) {
                System.out.println("Orders for customer ID 1:");
                while (rs.next()) {
                    System.out.printf("Name: %s, Order ID: %d, Amount: %.2f%n",
                        rs.getString("name"),
                        rs.getInt("order_id"),
                        rs.getBigDecimal("amount"));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Pass query parameters as a tuple to the cursor’s execute method.

import mysql.connector

config = { /* ... connection config ... */ }

def get_customer_orders(customer_id):
    try:
        with mysql.connector.connect(**config) as cnx:
            with cnx.cursor(dictionary=True) as cursor:
                query = ("SELECT c.name, o.order_id, o.amount FROM customers AS c "
                         "INNER JOIN orders AS o ON c.id = o.customer_id WHERE c.id = %s")

                cursor.execute(query, (customer_id,))

                print(f"Orders for customer ID {customer_id}:")
                for row in cursor.fetchall():
                    print(row)

    except mysql.connector.Error as err:
        print(err)

get_customer_orders(1)