MySQL JOINs

A JOIN combines rows from two or more tables, using a column that connects them. Here students.course_id points to courses.id.

The students table has the course_id values 1, 2, 1, 3, NULL, 2 (Vikram has no course, and nobody has course 4).

---

INNER JOIN

Returns only the rows that have a match in both tables.

SELECT s.name, c.course_name
FROM students s
INNER JOIN courses c ON s.course_id = c.id
ORDER BY s.id;

Vikram is missing, because he has no course.

---

LEFT JOIN

Returns all rows of the left table (students), and the matching rows of the right table. When there is no match, the right side is NULL.

SELECT s.name, c.course_name
FROM students s
LEFT JOIN courses c ON s.course_id = c.id
ORDER BY s.id;

---

RIGHT JOIN

Returns all rows of the right table (courses), and the matching rows of the left table.

SELECT c.course_name, s.name
FROM students s
RIGHT JOIN courses c ON s.course_id = c.id
ORDER BY c.id, s.id;

The course “C Programming” has no student, so the name is NULL.

---

JOIN with GROUP BY

SELECT c.course_name, COUNT(s.id) AS students
FROM courses c
LEFT JOIN students s ON s.course_id = c.id
GROUP BY c.id, c.course_name
ORDER BY c.id;

---

Which JOIN to use?

JOIN	Use when
INNER JOIN	You want only rows that match in both tables
LEFT JOIN	You want all rows of the first table, even without a match
RIGHT JOIN	Same as LEFT, with the tables swapped. Most people just use LEFT
CROSS JOIN	Every row with every row (rarely needed)

---

Summary

• INNER JOIN = matches only, LEFT JOIN = all left rows, RIGHT JOIN = all right rows.
• Use table aliases to keep the query short.
• Next: Subqueries.

• Previous Page
• Next Page

---

Exercises & Assignments
No Content Found.

Interview Questions & Answers
No Content Found.