INNER JOIN (or just JOIN)
The most frequently used clause is INNER JOIN. This produces a set of records which match in both the user and course tables, i.e. all users who are enrolled on a course:
SELECT user.name, course.name
FROM `user`
INNER JOIN `course` on user.course = course.id;
Result:
user.name course.name
Alice HTML5
Bob HTML5
Carline CSS3
David MySQL
LEFT JOIN
What if we require a list of all students and their courses even if they’re not enrolled on one? A LEFT JOIN produces a set of records which matches every entry in the left table (user) regardless of any matching entry in the right table (course):
SELECT user.name, course.name
FROM `user`
LEFT JOIN `course` on user.course = course.id;
Result:
user.name course.name
Alice HTML5
Bob HTML5
Carline CSS3
David MySQL
Emma (NULL)
RIGHT JOIN
RIGHT JOIN
Perhaps we require a list all courses and students even if no one has been enrolled? A RIGHT JOIN produces a set of records which matches every entry in the right table (course) regardless of any matching entry in the left table (user):
SELECT user.name, course.name
FROM `user`
RIGHT JOIN `course` on user.course = course.id;
Result:
user.name course.name
Alice HTML5
Bob HTML5
Carline CSS3
(NULL) JavaScript
(NULL) PHP
RIGHT JOINs are rarely used since you can express the same result using a LEFT JOIN. This can be more efficient and quicker for the database to parse:
SELECT user.name, course.name
FROM `course`
LEFT JOIN `user` on user.course = course.id;
We could, for example, count the number of students enrolled on each course:
SELECT course.name, COUNT(user.name)
FROM `course`
LEFT JOIN `user` ON user.course = course.id
GROUP BY course.id;
Result:
course.name count()
HTML5 2
CSS3 1
JavaScript 0
PHP 0
MySQL 1
OUTER JOIN (or FULL OUTER JOIN)
Our last option is the OUTER JOIN which returns all records in both tables regardless of any match. Where no match exists, the missing side will contain NULL.
OUTER JOIN is less useful than INNER, LEFT or RIGHT and it’s not implemented in MySQL. However, you can work around this restriction using the UNION of a LEFT and RIGHT JOIN, e.g.
SELECT user.name, course.name
FROM `user`
LEFT JOIN `course` on user.course = course.id
UNION
SELECT user.name, course.name
FROM `user`
RIGHT JOIN `course` on user.course = course.id;
Result:
user.name course.name
Alice HTML5
Bob HTML5
Carline CSS3
David MySQL
Emma (NULL)
(NULL) JavaScript
(NULL) PHP
I hope that gives you a better understanding of JOINs and helps you write more efficient SQL queries.