1. Tutorials
  2. SQL JOINs Explained with Examples: INNER, LEFT, RIGHT, FULL OUTER and SELF JOIN in MySQL

SQL JOINs Explained with Examples: INNER, LEFT, RIGHT, FULL OUTER and SELF JOIN in MySQL

Submitted by jaypabs on Sunday, November 1, 2026 - 09:18.

If you have ever stored data in more than one table, you have run into this question: how do I show the student's name together with the course name when they live in different tables? The answer is a JOIN. JOINs are one of the most used features of SQL, they show up in almost every capstone project, and they are a favorite topic in technical interviews.

In this tutorial, you will learn how INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, SELF JOIN and CROSS JOIN work in MySQL, using one small school database. Every query below was run on a real MySQL-compatible server (MariaDB 11.8), and the result tables are copied straight from the console, so you can follow along and compare your output line by line.

What Is a SQL JOIN?

A JOIN combines rows from two (or more) tables based on a related column. Usually that related column is a foreign key in one table that points to the primary key of another. In our example, students.course_id points to courses.course_id.

The different types of JOIN only disagree on one thing: what to do with rows that have no match in the other table. Keep that in mind and every JOIN type becomes easy to remember.

Step 1: Create the Sample Database

Open phpMyAdmin, MySQL Workbench, or the MySQL command line, and run the script below. It creates a school_joins database with courses, students, enrollments and employees. Notice two details that we added on purpose: Ella Garcia has no course yet (course_id is NULL), and BS Data Science has no students. These "unmatched" rows are what make the JOIN types behave differently.

  1. -- SQL JOINs tutorial sample database
  2. DROP DATABASE IF EXISTS school_joins;
  3. CREATE DATABASE school_joins;
  4. USE school_joins;
  5.  
  6. CREATE TABLE courses (
  7.     course_id   INT PRIMARY KEY,
  8.     course_name VARCHAR(50) NOT NULL
  9. );
  10.  
  11. CREATE TABLE students (
  12.     student_id INT PRIMARY KEY,
  13.     full_name  VARCHAR(50) NOT NULL,
  14.     course_id  INT NULL,
  15.     FOREIGN KEY (course_id) REFERENCES courses(course_id)
  16. );
  17.  
  18. INSERT INTO courses (course_id, course_name) VALUES
  19. (1, 'BS Information Technology'),
  20. (2, 'BS Computer Science'),
  21. (3, 'BS Information Systems'),
  22. (4, 'BS Data Science');
  23.  
  24. INSERT INTO students (student_id, full_name, course_id) VALUES
  25. (101, 'Ana Cruz',      1),
  26. (102, 'Ben Santos',    2),
  27. (103, 'Carla Reyes',   1),
  28. (104, 'Dan Lopez',     3),
  29. (105, 'Ella Garcia',   NULL);
  30.  
  31. CREATE TABLE enrollments (
  32.     student_id INT,
  33.     subject    VARCHAR(50),
  34.     grade      DECIMAL(4,2),
  35.     PRIMARY KEY (student_id, subject),
  36.     FOREIGN KEY (student_id) REFERENCES students(student_id)
  37. );
  38.  
  39. INSERT INTO enrollments VALUES
  40. (101, 'Database Systems', 1.50),
  41. (101, 'Web Development',  1.25),
  42. (102, 'Database Systems', 2.00),
  43. (103, 'Web Development',  1.75),
  44. (104, 'Database Systems', 2.25);
  45.  
  46. CREATE TABLE employees (
  47.     emp_id     INT PRIMARY KEY,
  48.     name       VARCHAR(50),
  49.     manager_id INT NULL
  50. );
  51.  
  52. INSERT INTO employees VALUES
  53. (1, 'Maria (Dean)', NULL),
  54. (2, 'Jose',  1),
  55. (3, 'Liza',  1),
  56. (4, 'Mark',  2);

Step 2: INNER JOIN – Only Matching Rows

INNER JOIN returns only the rows that have a match in both tables. It is the default JOIN, so writing just JOIN means the same thing.

  1. SELECT s.full_name, c.course_name
  2. FROM students s
  3. INNER JOIN courses c ON s.course_id = c.course_id
  4. ORDER BY s.student_id;

Output:

+-------------+---------------------------+
| full_name   | course_name               |
+-------------+---------------------------+
| Ana Cruz    | BS Information Technology |
| Ben Santos  | BS Computer Science       |
| Carla Reyes | BS Information Technology |
| Dan Lopez   | BS Information Systems    |
+-------------+---------------------------+

Ella is missing because she has no course, and BS Data Science is missing because nobody is enrolled in it. Use INNER JOIN when you only care about complete pairs, such as "list all students with their course."

Step 3: LEFT JOIN – All Rows from the Left Table

LEFT JOIN (also written LEFT OUTER JOIN) returns every row from the table on the left side of the JOIN, plus the matching data from the right table. When there is no match, the right-side columns are filled with NULL.

  1. SELECT s.full_name, c.course_name
  2. FROM students s
  3. LEFT JOIN courses c ON s.course_id = c.course_id
  4. ORDER BY s.student_id;

Output:

+-------------+---------------------------+
| full_name   | course_name               |
+-------------+---------------------------+
| Ana Cruz    | BS Information Technology |
| Ben Santos  | BS Computer Science       |
| Carla Reyes | BS Information Technology |
| Dan Lopez   | BS Information Systems    |
| Ella Garcia | NULL                      |
+-------------+---------------------------+

Now Ella appears with a NULL course. This is the JOIN you will use most in real projects, because reports usually must list every student, customer or product, even the ones without related records.

Finding Rows with No Match

A very handy trick is combining LEFT JOIN with WHERE ... IS NULL. This finds records that are missing a relationship, for example students who are not assigned to any course yet:

  1. SELECT s.full_name
  2. FROM students s
  3. LEFT JOIN courses c ON s.course_id = c.course_id
  4. WHERE c.course_id IS NULL;

Output:

+-------------+
| full_name   |
+-------------+
| Ella Garcia |
+-------------+

Step 4: RIGHT JOIN – All Rows from the Right Table

RIGHT JOIN is the mirror image of LEFT JOIN. It keeps every row from the right table (courses) and fills in NULL when no student matches.

  1. SELECT s.full_name, c.course_name
  2. FROM students s
  3. RIGHT JOIN courses c ON s.course_id = c.course_id
  4. ORDER BY c.course_id, s.student_id;

Output:

+-------------+---------------------------+
| full_name   | course_name               |
+-------------+---------------------------+
| Ana Cruz    | BS Information Technology |
| Carla Reyes | BS Information Technology |
| Ben Santos  | BS Computer Science       |
| Dan Lopez   | BS Information Systems    |
| NULL        | BS Data Science           |
+-------------+---------------------------+

BS Data Science now appears with a NULL student name. In practice, most developers simply swap the table order and use a LEFT JOIN instead, because it reads more naturally, but it is good to recognize RIGHT JOIN when you see it in someone else's code.

Step 5: FULL OUTER JOIN in MySQL

A FULL OUTER JOIN returns all rows from both tables, matched where possible. PostgreSQL and SQL Server support it directly, but MySQL and MariaDB do not have FULL OUTER JOIN. The standard workaround is to combine a LEFT JOIN and a RIGHT JOIN with UNION, which also removes the duplicate matched rows:

  1. SELECT s.full_name, c.course_name
  2. FROM students s
  3. LEFT JOIN courses c ON s.course_id = c.course_id
  4. UNION
  5. SELECT s.full_name, c.course_name
  6. FROM students s
  7. RIGHT JOIN courses c ON s.course_id = c.course_id
  8. ORDER BY full_name IS NULL, full_name;

Output:

+-------------+---------------------------+
| full_name   | course_name               |
+-------------+---------------------------+
| Ana Cruz    | BS Information Technology |
| Ben Santos  | BS Computer Science       |
| Carla Reyes | BS Information Technology |
| Dan Lopez   | BS Information Systems    |
| Ella Garcia | NULL                      |
| NULL        | BS Data Science           |
+-------------+---------------------------+

Both "orphans" are in the result: Ella without a course and BS Data Science without students.

Step 6: Joining Three Tables

You can chain as many JOINs as you need. Each new JOIN connects one more table using its own ON condition. Here we show each student's course together with their subjects and grades:

  1. SELECT s.full_name, c.course_name, e.subject, e.grade
  2. FROM students s
  3. INNER JOIN courses c     ON s.course_id  = c.course_id
  4. INNER JOIN enrollments e ON e.student_id = s.student_id
  5. ORDER BY s.full_name, e.subject;

Output:

+-------------+---------------------------+------------------+-------+
| full_name   | course_name               | subject          | grade |
+-------------+---------------------------+------------------+-------+
| Ana Cruz    | BS Information Technology | Database Systems |  1.50 |
| Ana Cruz    | BS Information Technology | Web Development  |  1.25 |
| Ben Santos  | BS Computer Science       | Database Systems |  2.00 |
| Carla Reyes | BS Information Technology | Web Development  |  1.75 |
| Dan Lopez   | BS Information Systems    | Database Systems |  2.25 |
+-------------+---------------------------+------------------+-------+

Ana appears twice because she has two enrollments. That is normal: a JOIN produces one row per matching combination. If you want one row per student, add GROUP BY.

Step 7: LEFT JOIN with GROUP BY and COUNT

A classic report is "how many students are in each course?" The important detail is to count a column from the right table, COUNT(s.student_id), instead of COUNT(*). COUNT(column) skips NULLs, so empty courses correctly show 0 instead of 1.

  1. SELECT c.course_name, COUNT(s.student_id) AS total_students
  2. FROM courses c
  3. LEFT JOIN students s ON s.course_id = c.course_id
  4. GROUP BY c.course_id, c.course_name
  5. ORDER BY total_students DESC, c.course_name;

Output:

+---------------------------+----------------+
| course_name               | total_students |
+---------------------------+----------------+
| BS Information Technology |              2 |
| BS Computer Science       |              1 |
| BS Information Systems    |              1 |
| BS Data Science           |              0 |
+---------------------------+----------------+

Step 8: SELF JOIN – Joining a Table to Itself

A SELF JOIN is useful when a table refers to itself, like employees and their managers. You join the same table twice and give each copy a different alias (e for employee, m for manager):

  1. SELECT e.name AS employee, m.name AS reports_to
  2. FROM employees e
  3. LEFT JOIN employees m ON e.manager_id = m.emp_id
  4. ORDER BY e.emp_id;

Output:

+--------------+--------------+
| employee     | reports_to   |
+--------------+--------------+
| Maria (Dean) | NULL         |
| Jose         | Maria (Dean) |
| Liza         | Maria (Dean) |
| Mark         | Jose         |
+--------------+--------------+

We used LEFT JOIN so that the dean, who has no manager, still appears in the list.

Step 9: CROSS JOIN – Every Combination

CROSS JOIN pairs every row of the first table with every row of the second, with no ON condition. With 5 students and 4 courses, we get 5 × 4 = 20 rows:

  1. SELECT COUNT(*) AS combinations
  2. FROM students
  3. CROSS JOIN courses;

Output:

+--------------+
| combinations |
+--------------+
|           20 |
+--------------+

CROSS JOIN is useful for generating combinations (for example, every size with every color), but be careful with large tables, because the row count multiplies quickly.

Common Mistake: Filtering a LEFT JOIN in WHERE

This is the bug that confuses almost everyone at least once. Suppose you want all students, and show their Database Systems enrollment if they have one. If you put the filter in the WHERE clause, the students without a match disappear, and your LEFT JOIN quietly behaves like an INNER JOIN:

  1. SELECT s.full_name, e.subject
  2. FROM students s
  3. LEFT JOIN enrollments e ON e.student_id = s.student_id
  4. WHERE e.subject = 'Database Systems'
  5. ORDER BY s.student_id;

Output:

+------------+------------------+
| full_name  | subject          |
+------------+------------------+
| Ana Cruz   | Database Systems |
| Ben Santos | Database Systems |
| Dan Lopez  | Database Systems |
+------------+------------------+

Carla and Ella are gone, because WHERE runs after the JOIN, and NULL = 'Database Systems' is never true. The fix is to move the condition into the ON clause, so it only controls which rows are matched:

  1. SELECT s.full_name, e.subject
  2. FROM students s
  3. LEFT JOIN enrollments e ON e.student_id = s.student_id
  4.                        AND e.subject = 'Database Systems'
  5. ORDER BY s.student_id;

Output:

+-------------+------------------+
| full_name   | subject          |
+-------------+------------------+
| Ana Cruz    | Database Systems |
| Ben Santos  | Database Systems |
| Carla Reyes | NULL             |
| Dan Lopez   | Database Systems |
| Ella Garcia | NULL             |
+-------------+------------------+

Now every student is listed, and those without the subject show NULL.

Quick Summary of SQL JOIN Types

  • INNER JOIN – only rows that match in both tables.
  • LEFT JOIN – all rows from the left table, NULL where the right side has no match.
  • RIGHT JOIN – all rows from the right table, NULL where the left side has no match.
  • FULL OUTER JOIN – all rows from both tables (use LEFT JOIN + UNION + RIGHT JOIN in MySQL).
  • SELF JOIN – a table joined to itself using two aliases.
  • CROSS JOIN – every possible combination of rows.

Tips for Faster and Cleaner JOINs

  • Always use table aliases (s, c) and prefix columns with them. It avoids "column is ambiguous" errors.
  • Add an index on foreign key columns used in ON. InnoDB does this automatically when you declare a FOREIGN KEY.
  • Select only the columns you need instead of SELECT *, especially when joining many tables.
  • Add ORDER BY when the order matters. Without it, the database may return rows in any order.

Conclusion

JOINs look intimidating at first, but they all follow the same idea: match rows using a related column, then decide what to do with the rows that don't match. INNER JOIN drops them, LEFT and RIGHT JOIN keep one side, and FULL OUTER JOIN keeps both. Run the sample database above, change the data, and re-run the queries. Seeing how the results change is the fastest way to make JOINs second nature. You can download the SQL script used in this tutorial below.

Happy coding!

Tags
SQL
MYSQL
SQL Join
left join
join
database
sql tutorial