How to Join Tables in SQL

Use JOIN when related data lives in separate tables and you need it in one result. This is the standard way to combine users with orders, products with categories, or employees with departments.

What you’ll build or solve

You’ll learn how to join tables in SQL using INNER JOIN and LEFT JOIN. You’ll also know how to match rows safely with primary and foreign keys.

When this approach works best

This approach is the right choice when normalized tables store connected data.

Common real-world scenarios include:

• Users with orders
• Products with categories
• Employees with managers
• Posts with authors
• Invoices with customers

This is a bad idea when unrelated tables are combined without a meaningful key relationship.

Prerequisites

You only need:

• Two related SQL tables
• Basic SELECT and WHERE knowledge
• A shared key such as user_id

Step-by-step instructions

Step 1: Match related rows with JOIN ... ON

Use INNER JOIN when you want only matching rows.

SELECT users.first_name, orders.total
FROM users
INNER JOIN orders
ON users.user_id = orders.user_id;

This returns only users who have matching orders.

Use LEFT JOIN when the left table should stay complete.

SELECT users.first_name, orders.total
FROM users
LEFT JOIN orders
ON users.user_id = orders.user_id;

This keeps all users, even those without orders.

What to look for:

• INNER JOIN keeps only matches
• LEFT JOIN keeps all rows from the left table
• Always join on a meaningful shared key
• Great for normalized database design
• Table aliases improve readability

Examples you can copy

Customers with orders

SELECT customers.name, orders.order_id
FROM customers
JOIN orders
ON customers.customer_id = orders.customer_id;

Products with categories

SELECT products.name, categories.category_name
FROM products
JOIN categories
ON products.category_id = categories.category_id;

Users with optional profiles

SELECT users.email, profiles.bio
FROM users
LEFT JOIN profiles
ON users.user_id = profiles.user_id;

Common mistakes and how to fix them

Mistake 1: Joining on the wrong columns

What the reader might do:

ON users.email = orders.user_id

Why it breaks: unrelated columns create invalid matches.

Corrected approach:

Use the actual relational key.

ON users.user_id = orders.user_id

Mistake 2: Forgetting the join condition

What the reader might do:

FROM users
JOIN orders

Why it breaks: missing ON causes invalid SQL or accidental cartesian joins in some systems.

Corrected approach:

Always define the matching key.

Mistake 3: Using INNER JOIN when missing matches should still appear

What the reader might do:

SELECT users.email, profiles.bio
FROM users
INNER JOIN profiles
ON users.user_id = profiles.user_id;

Why it breaks: users without profiles disappear.

Corrected approach:

Use LEFT JOIN.

Troubleshooting

If too many rows appear, verify the join key.

If rows disappear unexpectedly, switch from INNER JOIN to LEFT JOIN.

If duplicate rows appear, check for one-to-many relationships.

If the query becomes hard to read, add short table aliases.

Quick recap

• Use JOIN to combine related tables
• INNER JOIN keeps only matches
• LEFT JOIN keeps all left rows
• Always join on shared keys
• Use aliases for readability