Skip to main content
MySQL Basics
CHAPTER 14 Beginner

INNER JOIN, LEFT JOIN, and RIGHT JOIN

Updated: May 16, 2026
5 min read

# CHAPTER 14

INNER JOIN, LEFT JOIN, and RIGHT JOIN

1. Introduction

Normalization forced us to shatter our data into separate tables to ensure efficiency and integrity. The Users table holds the names; the Orders table holds the prices. But the business world requires unified reports. A manager wants an Excel sheet showing the Customer Name right next to the Order Price. To satisfy this request, we must temporarily stitch the tables back together. The most powerful command in relational databases is the JOIN operation. In this chapter, we master table combination.

2. Learning Objectives

By the end of this chapter, you will be able to:
  • Explain the visual concept of a JOIN using Venn Diagrams.
  • Write an INNER JOIN to fetch perfectly matched data.
  • Write a LEFT JOIN to fetch all parent data, regardless of matches.
  • Use Aliases (AS) to keep complex queries readable.
  • Avoid the catastrophic "Cartesian Product" mistake.

3. The INNER JOIN (The Perfect Match)

An INNER JOIN only returns a row if there is a perfect mathematical match in BOTH tables. If a user exists but has never placed an order, they will NOT appear in the results.

Imagine we have two tables: users and orders. They are linked because orders.user_id points to users.id.

sql
1234 
-- We want the User's Name and the Order's Price.
SELECT users.first_name, orders.order_total
FROM users
INNER JOIN orders ON users.id = orders.user_id;

Breaking it down:

  1. 1. FROM users: Start with the users table.
  1. 2. INNER JOIN orders: Bring the orders table over.
  1. 3. ON users.id = orders.userid: This is the Link Condition. It tells MySQL *exactly* how to match the rows up.

4. Table Aliases (Making Code Clean)

Typing users.firstname and orders.order_total gets exhausting. We can assign temporary 1-letter nicknames (Aliases) to the tables in the FROM clause!
sql
1234 
-- Same query as above, but highly professional and concise!
SELECT u.first_name, o.order_total
FROM users AS u
INNER JOIN orders AS o ON u.id = o.user_id;

5. The LEFT JOIN (Keep All Parents)

What if the boss says: *"I want a list of EVERY user in our system, and if they have placed an order, show me the price. If they haven't placed an order, just show a blank space, but DON'T hide the user!"* An INNER JOIN would hide the users without orders. A LEFT JOIN returns ALL rows from the "Left" table (the table specified first in the FROM clause), and matching rows from the "Right" table. If there is no match, it fills the right side with NULL.
sql
12345678910 
SELECT u.first_name, o.order_total
FROM users AS u                 -- The "Left" Table (Give me ALL of these)
LEFT JOIN orders AS o           -- The "Right" Table (Give me matches, or NULL)
ON u.id = o.user_id;

-- Output:
-- first_name | order_total
-- John       | 50.00
-- Sarah      | 120.00
-- Mike       | NULL         <-- Mike exists, but hasn&#039;t bought anything!

6. The RIGHT JOIN

A RIGHT JOIN is the exact opposite of a Left Join. It returns ALL rows from the Right table, and matching rows from the Left table. *(Pro Tip: RIGHT JOIN is almost never used in the industry. Developers simply flip the order of their tables and use a LEFT JOIN because it is easier for human brains to read top-to-bottom).*

7. Mini Project: E-commerce Order Dashboard

Let's build a complex dashboard query. We want the user's name, the product they bought, and the price. We need to join THREE tables! (users -> orders -> products).
sql
12345 
-- Assume orders has &#039;user_id' and 'product_id'
SELECT u.first_name, p.product_name, p.price
FROM users AS u
INNER JOIN orders AS o ON u.id = o.user_id
INNER JOIN products AS p ON o.product_id = p.id;

*You can chain as many INNER JOINs as your architecture requires!*

8. Common Mistakes

  • Forgetting the ON Clause (The Cartesian Product): If you write SELECT * FROM users JOIN orders; without the ON condition, MySQL doesn't know how to link them. It will multiply them together! If you have 100 users and 100 orders, it will return 10,000 rows of pure garbage data. Always specify the ON condition!
  • Ambiguous Column Errors: If both tables have a column named createdat, and you write SELECT createdat FROM..., MySQL will crash with an "Ambiguous Column" error. You must explicitly tell it which one: SELECT u.createdat...

9. Best Practices

  • Explicit Naming: Always use INNER JOIN rather than just JOIN. While MySQL treats JOIN as INNER JOIN by default, explicitly writing INNER JOIN makes your code universally readable to other developers.

10. Exercises

  1. 1. Rewrite this query using standard Table Aliases (e and d): SELECT employees.name, departments.title FROM employees INNER JOIN departments ON employees.deptid = departments.id;
  1. 2. Why would a LEFT JOIN return a NULL value in the resulting dataset?

11. MCQ Quiz with Answers

Question 1

What is the fundamental difference in the output of an INNER JOIN versus a LEFT JOIN?

Question 2

When writing a JOIN query, what does the ON clause do?

12. Interview Questions

  • Q: Explain the concept of a "Cartesian Product" (Cross Join) and why it usually indicates a catastrophic syntax error in a developer's SQL code.
  • Q: In a database tracking Students and Exams, if you are asked to generate a report showing *every single student*, including those who were absent and have no exam scores, which JOIN must you use and why?

13. FAQs

Q: Can I use a WHERE clause alongside an INNER JOIN? A: Yes! You stitch the tables together first, and then filter them. ...INNER JOIN orders o ON u.id = o.userid WHERE o.ordertotal > 100;

14. Summary

The JOIN clause is the beating heart of relational SQL. By utilizing INNER JOIN to extract perfect relationships and LEFT JOIN to safely include un-matched parent records, you possess the power to reassemble normalized data into massive, highly readable analytics grids without sacrificing database efficiency.

15. Next Chapter Recommendation

We have mastered standard two-table and three-table joins. But what if a table needs to join *to itself*? (e.g., An employee table where the "Manager" is also an employee in the same table). In Chapter 15: Advanced JOIN Queries, we tackle complex Self Joins and query optimization.

Finish this Chapter

Save your progress on your learning path and prepare for coding interview challenges.

Previous Chapter Foreign Keys and Relationships
Next Chapter Advanced JOIN Queries

Discussion

Join the discussion

Log in or create a free account to participate.

Sort: ·

Explore More

📖 Related Tutorials 6

❓ Related Quizzes 6