Key Takeaways
- JOIN’ is an SQL keyword used to query data from two or more related tables in relational databases such as MySQL, PostgreSQL, Firebird, SQLite, SQL Server and Oracle. The JOIN types include INNER JOIN, LEFT JOIN, RIGHT JOIN and OUTER JOIN (or FULL OUTER JOIN).
- INNER JOIN produces a set of records which match in both tables, LEFT JOIN produces a set of records which matches every entry in the left table regardless of any matching entry in the right table, RIGHT JOIN produces a set of records which matches every entry in the right table regardless of any matching entry in the left table, and OUTER JOIN returns all records in both tables regardless of any match.
- The efficiency of SQL queries can be improved by understanding and correctly using different types of JOINs. For instance, the same result can be expressed using a LEFT JOIN instead of a RIGHT JOIN, which can be more efficient and quicker for the database to parse.
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) |
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 |
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.
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
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 |
David | MySQL |
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 |
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.
Craig is a freelance UK web consultant who built his first page for IE2.0 in 1995. Since that time he's been advocating standards, accessibility, and best-practice HTML5 techniques. He's created enterprise specifications, websites and online applications for companies and organisations including the UK Parliament, the European Parliament, the Department of Energy & Climate Change, Microsoft, and more. He's written more than 1,000 articles for SitePoint and you can find him @craigbuckler.