Python MySQL Join

A JOIN combines rows from two or more related tables into a single result set, based on a matching column between them -- like linking each order in an orders table to the specific customer it belongs to in a separate customers table, using a shared customer_id column.

In this page:

Basic INNER JOIN
LEFT JOIN: Including Unmatched Rows
Joining Three or More Tables
Avoiding Ambiguous Column Errors
Joins with WHERE, ORDER BY, and LIMIT

Basic INNER JOIN

cursor.execute("SELECT users.name, products.name FROM users INNER JOIN products ON users.fav = products.id") combines matching rows from the users and products tables wherever a user's favorite (fav) column equals a product's id -- users with no matching product are excluded entirely from the result.

Note: Use INNER JOIN (often just written JOIN) as the default choice when you only care about rows that genuinely have a match in both related tables.

Warning: INNER JOIN silently excludes any row from either table that has no matching counterpart -- if you need those unmatched rows included too, LEFT JOIN or RIGHT JOIN is needed instead.

Example: Basic INNER JOIN

python

from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute(
    "SELECT users.name, products.name FROM users "
    "INNER JOIN products ON users.fav = products.id"
)
print("Only users with a matching favorite product are included")

LEFT JOIN: Including Unmatched Rows

LEFT JOIN returns every row from the left (first-named) table regardless of whether it has a match in the right table -- when there is no match, the columns from the right table simply come back as NULL, which is useful for finding, for example, every customer including ones who have never placed an order.

Note: Use LEFT JOIN specifically when the goal includes finding or displaying rows from the left table that might NOT have a corresponding match, such as customers with zero orders.

Warning: Columns pulled from the right table in unmatched LEFT JOIN rows come back as None in Python -- code consuming these results must handle that None case explicitly.

Example: LEFT JOIN: Including Unmatched Rows

python

from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute(
    "SELECT customers.name, orders.id FROM customers "
    "LEFT JOIN orders ON customers.id = orders.customer_id"
)
print("Customers with no orders still appear, with NULL order id")

Joining Three or More Tables

Multiple JOIN clauses can be chained together in a single query to combine data from three or more related tables -- for instance linking orders to customers, and separately linking each order to the products it contains, all in one statement.

Note: Alias each table with a short name (like o for orders, c for customers) when a query joins several tables, to keep the SQL more readable and avoid repeating full table names.

Warning: Chained joins can produce a surprisingly large result set if the relationships involve one-to-many links at multiple levels -- worth double-checking the expected row count.

Example: Joining Three or More Tables

python

from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute(
    "SELECT c.name, o.id, p.name FROM customers c "
    "JOIN orders o ON c.id = o.customer_id "
    "JOIN products p ON o.product_id = p.id"
)
print("Three tables combined with short aliases")

Avoiding Ambiguous Column Errors

When two joined tables share a column name (both having an "id" column, for instance), referencing that name alone in the SELECT list or WHERE clause is ambiguous and raises an error -- qualifying it with the table name (or alias), like customers.id, resolves the ambiguity.

Note: Get in the habit of always qualifying column names with their table (or alias) in any query involving a JOIN, even for columns that are not currently ambiguous, for consistency and future-proofing.

Warning: An "ambiguous column" error specifically means the database cannot tell which of the joined tables you meant -- it must be resolved by qualifying the column, not by renaming anything.

Example: Avoiding Ambiguous Column Errors

python

from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute(
    "SELECT customers.id, orders.id FROM customers "
    "JOIN orders ON customers.id = orders.customer_id"
)
print("Qualifying 'id' with a table name avoids ambiguity")

Joins with WHERE, ORDER BY, and LIMIT

A JOIN combines naturally with every other SELECT clause already covered -- WHERE to filter the joined result, ORDER BY to sort it, and LIMIT to cap how many combined rows come back -- applied in that same logical order: join, then filter, then sort, then limit.

Note: Build complex multi-table queries incrementally: start with the JOIN and confirm it looks right, then layer in WHERE, then ORDER BY, then LIMIT one at a time.

Warning: WHERE conditions in a joined query can filter on columns from either joined table -- forgetting to qualify which table a filtered column belongs to is a common source of the ambiguous-column error here too.

Example: Joins with WHERE, ORDER BY, and LIMIT

python

from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute(
    "SELECT c.name, o.total FROM customers c JOIN orders o ON c.id = o.customer_id "
    "WHERE o.total > %s ORDER BY o.total DESC LIMIT 5",
    (100,),
)
print("Join, then filter, then sort, then limit")

Common Mistakes

Forgetting the ON clause that specifies how the two tables relate, which either causes a syntax error or (with certain join forms) an unintended cross join matching every row against every other row.
Confusing INNER JOIN (only rows with a match in both tables) with LEFT JOIN (all rows from the left table, with NULLs filling in where there is no match on the right).
Not qualifying a column name with its table when the same column name exists in both joined tables, causing an ambiguous column reference error.

Chapter Summary

JOIN combines rows from two tables based on a related column between them, specified in an ON clause.
INNER JOIN returns only rows that have a match in both tables.
LEFT JOIN returns all rows from the left table, with NULL filled in for any columns from the right table that has no match.

Browser Support

JOIN syntax is standard SQL, executed identically across all mysql-connector-python versions.

🔒

Chapter Quiz — Complete all 12 topics to unlock

0/12 topics done

Complete these topics first:

← Previous Lesson Python MySQL Limit

Python MySQL Join

Basic INNER JOIN

LEFT JOIN: Including Unmatched Rows

Joining Three or More Tables

Avoiding Ambiguous Column Errors

Joins with WHERE, ORDER BY, and LIMIT

Chapter Quiz — Complete all 12 topics to unlock

Login to run this code