Understanding JOINs in MySQL and Other Relational Databases

Craig Buckler
Craig Buckler
Share
Learn more on MySQL with our screencast on MySQL record searching and updating.
This article was written in 2011 and remains one of our most popular posts. If you’re keen to learn more about MySQL, you may find this recent article on administering MySQL of great interest. “JOIN” is an SQL keyword used to query data from two or more related tables. Unfortunately, the concept is regularly explained using abstract terms or differs between database systems. It often confuses me. Developers cope with enough confusion, so this is my attempt to explain JOINs briefly and succinctly to myself and anyone who’s interested.

Related Tables

MySQL, PostgreSQL, Firebird, SQLite, SQL Server and Oracle are relational database systems. A well-designed database will provide a number of tables containing related data. A very simple example would be users (students) and course enrollments: ‘user’ table:
id name course
1 Alice 1
2 Bob 1
3 Caroline 2
4 David 5
5 Emma (NULL)
MySQL table creation code:

CREATE TABLE `user` (
	`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
	`name` varchar(30) NOT NULL,
	`course` smallint(5) unsigned DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB;
The course number relates to a subject being taken in a course table… ‘course’ table:
id name
1 HTML5
2 CSS3
3 JavaScript
4 PHP
5 MySQL
MySQL table creation code:

CREATE TABLE `course` (
	`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
	`name` varchar(50) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB;
Since we’re using InnoDB tables and know that user.course and course.id are related, we can specify a foreign key relationship:

ALTER TABLE `user`
ADD CONSTRAINT `FK_course`
FOREIGN KEY (`course`) REFERENCES `course` (`id`)
ON UPDATE CASCADE;
In essence, MySQL will automatically:
  • re-number the associated entries in the user.course column if the course.id changes
  • reject any attempt to delete a course where users are enrolled.
important: This is terrible database design!
This database is not efficient. It’s fine for this example, but a student can only be enrolled on zero or one course. A real system would need to overcome this restriction — probably using an intermediate ‘enrollment’ table which mapped any number of students to any number of courses.
JOINs allow us to query this data in a number of ways.

INNER JOIN (or just JOIN)

SQL INNER JOINThe 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

SQL LEFT JOINWhat 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

SQL RIGHT JOINPerhaps 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
David MySQL
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)

SQL FULL OUTER JOINOur 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. Learn more on MySQL with our screencast on MySQL record searching and updating. Comments on this article are closed. Have a question about MySQL? Why not ask it on our forums?

Frequently Asked Questions (FAQs) about SQL Joins in MySQL Database

What is the difference between INNER JOIN and OUTER JOIN in MySQL?

In MySQL, INNER JOIN and OUTER JOIN are two different ways to combine rows from two or more tables based on a related column. An INNER JOIN returns only the rows where there is a match in both tables. On the other hand, an OUTER JOIN returns all the rows from one table and the matched rows from another table. If there is no match, the result is NULL on the side of the table that does not have a match.

How can I use the LEFT JOIN in MySQL?

In MySQL, the LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match. The syntax for using LEFT JOIN is as follows: SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

What is a Self Join and how can it be implemented in MySQL?

A Self Join in MySQL is used to combine rows with other rows in the same table when there is a match based on specified conditions. It can be implemented by using the INNER JOIN or LEFT JOIN clause along with a table alias to assign different names to the same table within the query.

How does a CROSS JOIN work in MySQL?

A CROSS JOIN in MySQL returns the Cartesian product of rows from the tables in the join. In other words, it will produce rows which combine each row from the first table with each row from the second table. If the first table has ‘n’ rows and the second table has ‘m’ rows, the result will be n*m rows.

What is the purpose of USING clause in MySQL JOIN?

The USING clause is used in MySQL to specify the columns upon which the tables should be joined. It is used when the columns being joined have the same name in both tables. The USING clause is a more concise way of specifying the join conditions compared to the ON clause.

How can I optimize the performance of MySQL JOINs?

There are several ways to optimize the performance of MySQL JOINs. One way is to use indexes on the columns that are used in the JOIN condition. Another way is to limit the size of the result set by using the WHERE clause to filter out rows that are not needed. Also, using the EXPLAIN statement can help identify potential performance issues in the JOIN query.

Can I use multiple JOINs in a single SQL query in MySQL?

Yes, you can use multiple JOINs in a single SQL query in MySQL. This allows you to combine rows from more than two tables. The tables are joined in the order they are listed in the FROM clause, from left to right.

What is the difference between a JOIN and a UNION in MySQL?

A JOIN in MySQL combines rows from two or more tables based on a related column. A UNION, on the other hand, is used to combine the result set of two or more SELECT statements. The main difference is that JOIN combines columns from different tables, while UNION combines rows from different tables.

How can I use a JOIN to combine more than two tables in MySQL?

To combine more than two tables in MySQL using a JOIN, you simply list all the tables in the FROM clause and specify the join conditions in the ON clause. The tables are joined in the order they are listed, from left to right.

What is a Natural Join in MySQL?

A Natural Join in MySQL is a type of join that creates an implicit join clause for you based on the common columns in the two tables being joined. It selects records that have matching values in both tables.