MySQL - Using Joins

MySQL - Modern Guide to Using JOINs

A JOIN clause is a fundamental SQL concept used to combine rows from two or more tables based on a related column between them. This allows you to query and retrieve data that is logically spread across a normalized database structure as if it were in a single table.

The Importance of Explicit JOIN Syntax

Historically, SQL allowed for an implicit, comma-based join syntax in the FROM clause, with the join condition in the WHERE clause. This is now considered outdated and bad practice.

Old Syntax (Avoid): SELECT ... FROM table1, table2 WHERE table1.id = table2.fk_id;

Modern Explicit Syntax (Best Practice): SELECT ... FROM table1 INNER JOIN table2 ON table1.id = table2.fk_id;

• Clarity: It clearly separates the logic for joining tables (ON clause) from the logic for filtering rows (WHERE clause).
• Safety: It prevents accidental CROSS JOIN operations. Forgetting the WHERE condition in the old syntax results in a Cartesian product of all rows, which can crash your application. An explicit JOIN without an ON clause is a syntax error.
• Power: It is the only syntax that supports OUTER JOINs (LEFT, RIGHT, FULL).

Types of JOINs

SQL defines several types of JOINs to handle different scenarios:

• INNER JOIN: Returns only the records that have matching values in both tables. This is the most common type of join.
• LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table, and the matched records from the right table. If there is no match, the result is NULL on the right side.
• RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table, and the matched records from the left table. If there is no match, the result is NULL on the left side.
• FULL OUTER JOIN: Returns all records when there is a match in either the left or right table. (As covered in another chapter, MySQL requires emulation for this).

Example: Joining Customers and Orders

Let’s set up two tables, CUSTOMERS and ORDERS, to demonstrate these joins.

CREATE TABLE CUSTOMERS(
    ID INT PRIMARY KEY,
    NAME VARCHAR(50) NOT NULL,
    CITY VARCHAR(50)
);

CREATE TABLE ORDERS(
    OID INT PRIMARY KEY,
    ORDER_DATE DATETIME NOT NULL,
    CUSTOMER_ID INT,
    AMOUNT DECIMAL(10, 2),
    FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS(ID)
);

INSERT INTO CUSTOMERS VALUES (1, 'Ramesh', 'Ahmedabad'), (2, 'Khilan', 'Delhi'), (3, 'Kaushik', 'Kota'), (4, 'Chaitali', 'Mumbai');

INSERT INTO ORDERS VALUES (101, '2023-10-08 10:00:00', 3, 3000.00), (102, '2023-11-20 11:30:00', 2, 1560.00), (103, '2023-05-20 15:00:00', 4, 2060.00);

Note that Ramesh (ID 1) has not placed any orders.

INNER JOIN Example: Get customers who have placed orders.

SELECT c.ID, c.NAME, o.ORDER_DATE, o.AMOUNT
FROM CUSTOMERS c
INNER JOIN ORDERS o ON c.ID = o.CUSTOMER_ID;

Output

ID	NAME	ORDER_DATE	AMOUNT
3	Kaushik	2023-10-08 10:00:00	3000.00
2	Khilan	2023-11-20 11:30:00	1560.00
4	Chaitali	2023-05-20 15:00:00	2060.00

LEFT JOIN Example: Get all customers and their orders, if any.

SELECT c.ID, c.NAME, o.ORDER_DATE, o.AMOUNT
FROM CUSTOMERS c
LEFT JOIN ORDERS o ON c.ID = o.CUSTOMER_ID;

Notice that Ramesh is now included in the result, with NULL values for the order details.

Joins Using a Client Program (Modern Best Practices)

When performing joins from an application, it is critical to use prepared statements to prevent SQL injection vulnerabilities. Here are modern examples for popular languages.

PHP
NodeJS
Java
Python



This example uses the modern object-oriented `mysqli` extension with prepared statements.

```php
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';

// Establish connection
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

// Join query to get customers who ordered and live in a specific city
$city = 'Kota';
$sql = 'SELECT c.ID, c.NAME, o.AMOUNT FROM CUSTOMERS c JOIN ORDERS o ON c.ID = o.CUSTOMER_ID WHERE c.CITY = ?';

$stmt = $mysqli->prepare($sql);
$stmt->bind_param('s', $city);
$stmt->execute();
$result = $stmt->get_result();

if ($result->num_rows > 0) {
    echo "Orders from customers in {$city}:\n";
    while ($row = $result->fetch_assoc()) {
        printf("ID: %s, Name: %s, Amount: %s\n", $row['ID'], $row['NAME'], $row['AMOUNT']);
    }
} else {
    echo "No results found.";
}

$stmt->close();
$mysqli->close();
?>

This example uses the mysql2/promise library with async/await for clean, modern asynchronous code.

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

async function getOrdersByCity(city) {
    let connection;
    try {
        // Use a connection pool in real applications
        connection = await mysql.createConnection({
            host: 'localhost',
            user: 'root',
            password: 'password',
            database: 'TUTORIALS'
        });

        const sql = `
            SELECT c.ID, c.NAME, o.AMOUNT
            FROM CUSTOMERS c
            JOIN ORDERS o ON c.ID = o.CUSTOMER_ID
            WHERE c.CITY = ?`;

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

        if (rows.length > 0) {
            console.log(`Orders from customers in ${city}:`);
            console.log(rows);
        } else {
            console.log('No results found.');
        }

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

getOrdersByCity('Kota');

This example uses modern JDBC with try-with-resources for automatic resource management and PreparedStatement for security.

import java.sql.*;

public class JoinExample {
    private static final String URL = "jdbc:mysql://localhost:3306/TUTORIALS";
    private static final String USER = "root";
    private static final String PASSWORD = "password";

    public static void main(String[] args) {
        String city = "Kota";
        String sql = "SELECT c.ID, c.NAME, o.AMOUNT FROM CUSTOMERS c " +
                     "JOIN ORDERS o ON c.ID = o.CUSTOMER_ID WHERE c.CITY = ?";

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

            pstmt.setString(1, city);

            try (ResultSet rs = pstmt.executeQuery()) {
                System.out.println("Orders from customers in " + city + ":");
                while (rs.next()) {
                    System.out.printf("ID: %d, Name: %s, Amount: %.2f%n",
                            rs.getInt("ID"),
                            rs.getString("NAME"),
                            rs.getBigDecimal("AMOUNT"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This example uses mysql-connector-python with context managers (with) for automatic handling of connections and cursors.

import mysql.connector
from mysql.connector import errorcode

def get_orders_by_city(city):
    config = {
        'user': 'root',
        'password': 'password',
        'host': 'localhost',
        'database': 'TUTORIALS'
    }

    sql_query = ("""
        SELECT c.ID, c.NAME, o.AMOUNT
        FROM CUSTOMERS c
        JOIN ORDERS o ON c.ID = o.CUSTOMER_ID
        WHERE c.CITY = %s
    """)

    try:
        with mysql.connector.connect(**config) as connection:
            with connection.cursor(dictionary=True) as cursor:
                cursor.execute(sql_query, (city,))
                results = cursor.fetchall()

                if results:
                    print(f"Orders from customers in {city}:")
                    for row in results:
                        print(row)
                else:
                    print("No results found.")

    except mysql.connector.Error as err:
        print(f"Something went wrong: {err}")

if __name__ == '__main__':
    get_orders_by_city('Kota')