W3docs
en
Log in

MySQL Join

Learn to combine MySQL tables in Python with INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN. Includes examples with error handling.

A SQL JOIN lets you combine rows from two or more tables based on a related column. This page explains every join type supported when working with MySQL from Python, shows complete runnable examples for each, and covers best practices such as parameterized queries and proper resource cleanup.

Before reading this chapter, make sure you are comfortable connecting to MySQL, creating tables, and selecting rows.

Prerequisites

Install the connector if you have not already:

pip install mysql-connector-python

Sample tables used in this chapter

All examples below assume two tables — customers and orders — exist in a database called mydatabase. Run this SQL once to create and populate them:

CREATE TABLE IF NOT EXISTS customers (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  name    VARCHAR(100) NOT NULL,
  address VARCHAR(200)
);

CREATE TABLE IF NOT EXISTS orders (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  customer_id  INT,
  order_date   DATE,
  order_total  DECIMAL(10, 2),
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

INSERT INTO customers (name, address) VALUES
  ('Alice',   '123 Maple St'),
  ('Bob',     '456 Oak Ave'),
  ('Charlie', '789 Pine Rd');

INSERT INTO orders (customer_id, order_date, order_total) VALUES
  (1, '2024-01-10', 99.99),
  (1, '2024-02-14', 45.00),
  (2, '2024-03-05', 210.50);
-- Charlie has no orders, so he will appear only in LEFT/FULL joins.

Notice that Charlie has no matching orders. This detail makes the difference between join types obvious in the output.

Types of table joins

Join typeWhat it returns
INNER JOINOnly rows that match in both tables
LEFT JOINAll rows from the left table; NULL where there is no match on the right
RIGHT JOINAll rows from the right table; NULL where there is no match on the left
FULL OUTER JOIN (via UNION)All rows from both tables; NULL on whichever side has no match

MySQL does not have a FULL OUTER JOIN keyword. Use a UNION of a LEFT JOIN and a RIGHT JOIN to achieve the same result.

INNER JOIN

An INNER JOIN returns only the rows where the join condition is satisfied in both tables. Use it when you only care about customers who actually have orders.

import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    sql = """
        SELECT customers.name, customers.address,
               orders.order_date, orders.order_total
        FROM customers
        INNER JOIN orders ON customers.id = orders.customer_id
    """
    mycursor.execute(sql)
    results = mycursor.fetchall()

    for row in results:
        print(row)

except Error as e:
    print(f"Error: {e}")
finally:
    if mycursor:
        mycursor.close()
    if mydb.is_connected():
        mydb.close()

Expected output (using the sample data above):

('Alice', '123 Maple St', datetime.date(2024, 1, 10), Decimal('99.99'))
('Alice', '123 Maple St', datetime.date(2024, 2, 14), Decimal('45.00'))
('Bob',   '456 Oak Ave',  datetime.date(2024, 3,  5), Decimal('210.50'))

Charlie is absent because he has no matching order rows.

LEFT JOIN

A LEFT JOIN returns every row from the left table (customers) and the matching rows from the right table (orders). When there is no match, the columns from the right table are None in Python.

Use a LEFT JOIN when you want to see all customers, even those who have placed no orders yet.

import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    sql = """
        SELECT customers.name, customers.address,
               orders.order_date, orders.order_total
        FROM customers
        LEFT JOIN orders ON customers.id = orders.customer_id
    """
    mycursor.execute(sql)
    results = mycursor.fetchall()

    for row in results:
        print(row)

except Error as e:
    print(f"Error: {e}")
finally:
    if mycursor:
        mycursor.close()
    if mydb.is_connected():
        mydb.close()

Expected output:

('Alice',   '123 Maple St', datetime.date(2024, 1, 10), Decimal('99.99'))
('Alice',   '123 Maple St', datetime.date(2024, 2, 14), Decimal('45.00'))
('Bob',     '456 Oak Ave',  datetime.date(2024, 3,  5), Decimal('210.50'))
('Charlie', '789 Pine Rd',  None,                       None)

Charlie appears with None for the order columns because he has no orders.

RIGHT JOIN

A RIGHT JOIN is the mirror image of a LEFT JOIN. It returns every row from the right table (orders) and the matching rows from the left table (customers). Rows in orders that lack a matching customer show None for the customer columns.

In practice, RIGHT JOIN is less common than LEFT JOIN because you can always rewrite it as a LEFT JOIN by swapping the table order.

import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    sql = """
        SELECT customers.name, customers.address,
               orders.order_date, orders.order_total
        FROM customers
        RIGHT JOIN orders ON customers.id = orders.customer_id
    """
    mycursor.execute(sql)
    results = mycursor.fetchall()

    for row in results:
        print(row)

except Error as e:
    print(f"Error: {e}")
finally:
    if mycursor:
        mycursor.close()
    if mydb.is_connected():
        mydb.close()

Expected output (with the sample data, all orders have a matching customer, so the result looks the same as INNER JOIN):

('Alice', '123 Maple St', datetime.date(2024, 1, 10), Decimal('99.99'))
('Alice', '123 Maple St', datetime.date(2024, 2, 14), Decimal('45.00'))
('Bob',   '456 Oak Ave',  datetime.date(2024, 3,  5), Decimal('210.50'))

FULL OUTER JOIN (via UNION)

MySQL has no FULL OUTER JOIN keyword, but you can get the same result by combining a LEFT JOIN and a RIGHT JOIN with UNION. UNION automatically removes duplicate rows.

import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    sql = """
        SELECT customers.name, customers.address,
               orders.order_date, orders.order_total
        FROM customers
        LEFT JOIN orders ON customers.id = orders.customer_id

        UNION

        SELECT customers.name, customers.address,
               orders.order_date, orders.order_total
        FROM customers
        RIGHT JOIN orders ON customers.id = orders.customer_id
    """
    mycursor.execute(sql)
    results = mycursor.fetchall()

    for row in results:
        print(row)

except Error as e:
    print(f"Error: {e}")
finally:
    if mycursor:
        mycursor.close()
    if mydb.is_connected():
        mydb.close()

Expected output:

('Alice',   '123 Maple St', datetime.date(2024, 1, 10), Decimal('99.99'))
('Alice',   '123 Maple St', datetime.date(2024, 2, 14), Decimal('45.00'))
('Bob',     '456 Oak Ave',  datetime.date(2024, 3,  5), Decimal('210.50'))
('Charlie', '789 Pine Rd',  None,                       None)

All customers appear (including Charlie with no orders) and all orders appear (including any that might have no matching customer).

Filtering JOIN results with WHERE

You can add a WHERE clause to any join to narrow down the result set. Always use parameterized queries (the %s placeholder) instead of string formatting to avoid SQL injection.

The following example retrieves only the orders for a specific customer by name:

import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    sql = """
        SELECT customers.name, orders.order_date, orders.order_total
        FROM customers
        INNER JOIN orders ON customers.id = orders.customer_id
        WHERE customers.name = %s
    """
    val = ("Alice",)
    mycursor.execute(sql, val)
    results = mycursor.fetchall()

    for row in results:
        print(row)

except Error as e:
    print(f"Error: {e}")
finally:
    if mycursor:
        mycursor.close()
    if mydb.is_connected():
        mydb.close()

Expected output:

('Alice', datetime.date(2024, 1, 10), Decimal('99.99'))
('Alice', datetime.date(2024, 2, 14), Decimal('45.00'))

Sorting JOIN results with ORDER BY

Combine a join with ORDER BY to control the output order. This example lists all customer orders sorted by the most recent order first:

import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    sql = """
        SELECT customers.name, orders.order_date, orders.order_total
        FROM customers
        INNER JOIN orders ON customers.id = orders.customer_id
        ORDER BY orders.order_date DESC
    """
    mycursor.execute(sql)
    results = mycursor.fetchall()

    for row in results:
        print(row)

except Error as e:
    print(f"Error: {e}")
finally:
    if mycursor:
        mycursor.close()
    if mydb.is_connected():
        mydb.close()

Expected output:

('Bob',   datetime.date(2024, 3,  5), Decimal('210.50'))
('Alice', datetime.date(2024, 2, 14), Decimal('45.00'))
('Alice', datetime.date(2024, 1, 10), Decimal('99.99'))

Limiting JOIN results with LIMIT

Pair a join with a LIMIT clause to page through large result sets efficiently:

import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    sql = """
        SELECT customers.name, orders.order_date, orders.order_total
        FROM customers
        INNER JOIN orders ON customers.id = orders.customer_id
        ORDER BY orders.order_date DESC
        LIMIT 2
    """
    mycursor.execute(sql)
    results = mycursor.fetchall()

    for row in results:
        print(row)

except Error as e:
    print(f"Error: {e}")
finally:
    if mycursor:
        mycursor.close()
    if mydb.is_connected():
        mydb.close()

Expected output (only the two most recent orders):

('Bob',   datetime.date(2024, 3,  5), Decimal('210.50'))
('Alice', datetime.date(2024, 2, 14), Decimal('45.00'))

Best practices

  • Use parameterized queries. Pass user-supplied values as the second argument to cursor.execute() with %s placeholders. Never use Python string formatting or f-strings to build SQL — that opens your application to SQL injection.
  • Wrap database code in try...except...finally. This ensures connections and cursors are always closed, even when an error occurs.
  • Select only the columns you need. Using SELECT * across joined tables can pull in many redundant columns and hurt performance on large tables.
  • Add indexes on join columns. If orders.customer_id is not indexed, MySQL will scan the entire table for every join. A foreign key constraint (as shown in the setup script above) creates an index automatically.
  • Prefer LEFT JOIN over RIGHT JOIN for readability. A RIGHT JOIN can always be rewritten as a LEFT JOIN by swapping the table positions, which most developers find easier to follow.

Quick reference

ScenarioJoin to use
Only records with matches in both tablesINNER JOIN
All records from the primary (left) table, matched or notLEFT JOIN
All records from the secondary (right) table, matched or notRIGHT JOIN
Every record from both tables, matched or notLEFT JOIN ... UNION ... RIGHT JOIN
Narrow the joined rowsAdd a WHERE clause with parameterized values
Control output orderAdd ORDER BY column ASC|DESC
Page resultsAdd LIMIT n (see MySQL Limit)
Was this page helpful?