This blog was originally published in September 2023 and was updated in June 2024.
In simple terms, a join in SQL combines columns from one or more tables to extract the required data. When used effectively, joins can simplify queries and save a lot of time.
Some of these SQL joins are only available natively in some relational databases; for the sake of simplicity, we will only consider the most widely used open-source databases in this blog: MySQL and PostgreSQL.
Different types Of joins
Inner join
Left join
Right join
Full outer join
Left join excluding inner join
Right join excluding inner join
Outer join excluding inner join
In order to best explain, let’s consider two identical tables, A and B, with two columns – id and val. We have created the table in both MySQL and PostgreSQL and inserted two values in each table.
| +-------+-------------+ | Field | Type | +-------+-------------+ | id | int | | val | varchar(50) | +-------+-------------+ |
Values in both tables:
| Table A: | Table B: |
|---|
| SELECT * FROM A; +------+------+ | id | val | +------+------+ | 1 | V | | 2 | W | +------+------+ |
| | SELECT * FROM B; +------+------+ | id | val | +------+------+ | 2 | X | | 3 | Y | +------+------+ |
|
Explaining Inner Join on MySQL vs PostgreSQL
An inner join returns the common values in both tables based on the key column. MySQL and PostgreSQL both support it natively.
| MySQL : | PostgreSQL: |
|---|
| mysql> SELECT A.id, B.id, A.val, B.val FROM A INNER JOIN B ON A.id = B.id; |
| | test=# SELECT A.id, B.id, A.val, B.val FROM A INNER JOIN B ON A.id = B.id; |
|
| +------+------+------+------+ | id | id | val | val | +------+------+------+------+ | 2 | 2 | W | X | +------+------+------+------+ 1 row in set (0.00 sec) |
| | id | id | val | val ----+----+-----+----- 2 | 2 | W | X (1 row) |
|
Left Join on MySQL and PostgreSQL
A left join or left outer join returns all the values in the leftmost tables based on the key column, even if there are no matching rows in the right table. For the rows not present in other tables, NULL will be displayed for the columns of the other table. MySQL and PostgreSQL both support it natively.
| MySQL : | PostgreSQL: |
|---|
| mysql> SELECT A.id, B.id, A.val, B.val FROM A LEFT JOIN B ON A.id = B.id; |
| | test=# SELECT A.id, B.id, A.val, B.val FROM A LEFT JOIN B ON A.id = B.id; |
|
| +------+------+------+------+ | id | id | val | val | +------+------+------+------+ | 1 | NULL | V | NULL | | 2 | 2 | W | X | +------+------+------+------+ 2 rows in set (0.00 sec) |
| | id | id | val | val ----+----+-----+----- 1 | | V | 2 | 2 | W | X (2 rows) |
|
Right Join on MySQL and PostgreSQL
A right join or right outer join returns all the values in the rightmost tables based on the key column, even if there are no matching rows in the left table. For the rows not present in other tables, NULL will be displayed for the columns of the other table. MySQL and PostgreSQL both support it natively.
| MySQL : | PostgreSQL: |
|---|
| mysql> SELECT A.id, B.id, A.val, B.val FROM A RIGHT JOIN B ON A.id = B.id; |
| | test=# SELECT A.id, B.id, A.val, B.val FROM A RIGHT JOIN B ON A.id = B.id; |
|
| +------+------+------+------+ | id | id | val | val | +------+------+------+------+ | 2 | 2 | W | X | | NULL | 3 | NULL | Y | +------+------+------+------+ 2 rows in set (0.00 sec) |
| | id | id | val | val ----+----+-----+----- 2 | 2 | W | X | 3 | | Y (2 rows) |
|
Left join excluding inner join for MySQL and PostgreSQL
Left join excluding inner join returns the values in the leftmost table and excludes the common values in both tables. For the rows not present in other tables, NULL will be displayed for the columns of the other table. This is not natively supported by MySQL or Postgres, and we have to put a where condition to get the desired result.
| MySQL : | PostgreSQL: |
|---|
| mysql> SELECT A.id, B.id, A.val, B.val FROM A LEFT JOIN B ON A.id = B.id WHERE B.id IS NULL; |
| | test=# SELECT A.id, B.id, A.val, B.val FROM A LEFT JOIN B ON A.id = B.id WHERE B.id IS NULL; |
|
| +------+------+------+------+ | id | id | val | val | +------+------+------+------+ | 1 | NULL | V | NULL | +------+------+------+------+ 1 row in set (0.00 sec) |
| | id | id | val | val ----+----+-----+----- 1 | | V | (1 row) |
|
Right join excluding inner join for MySQL and PostgreSQL
Right join excluding inner join returns the values in the rightmost table and excludes the common values in both tables. For the rows not present in other tables, NULL will be displayed for the columns of the other table. This is not natively supported by MySQL or Postgres, and we have to put a condition to get the desired result.
| MySQL : | PostgreSQL: |
|---|
| mysql> SELECT A.id, B.id, A.val, B.val FROM A RIGHT JOIN B ON A.id = B.id WHERE A.id IS NULL; |
| | test=# SELECT A.id, B.id, A.val, B.val FROM A RIGHT JOIN B ON A.id = B.id WHERE A.id IS NULL; |
|
| +------+------+------+------+ | id | id | val | val | +------+------+------+------+ | NULL | 3 | NULL | Y | +------+------+------+------+ 1 row in set (0.00 sec) |
| | id | id | val | val ----+----+-----+----- | 3 | | Y (1 row) |
|
Full outer join
A full outer join return combines the result set of both the left and right join and returns the values common between both and the non-matching values as well. It is supported by Postgres, but it’s not supported in MySQL natively. For MySQL, we have to use UNION to combine the results of left and right join.
| MySQL : | PostgreSQL: |
|---|
| mysql> SELECT A.id, B.id, A.val, B.val FROM A LEFT JOIN B ON A.id = B.id UNION SELECT A.id, B.id, A.val, B.val FROM A RIGHT JOIN B ON A.id = B.id; |
| | test=# SELECT A.id, B.id, A.val, B.val FROM A FULL OUTER JOIN B ON A.id = B.id; |
|
| +------+------+------+------+ | id | id | val | val | +------+------+------+------+ | 1 | NULL | V | NULL | | 2 | 2 | W | X | | NULL | 3 | NULL | Y | +------+------+------+------+ 3 rows in set (0.00 sec) |
| | id | id | val | val ----+----+-----+----- 1 | | V | 2 | 2 | W | X | 3 | | Y (3 rows) |
|
Outer join excluding inner join
An outer join excluding inner join returns the uncommon values in both tables. The matching values are omitted here. It is not supported by Postgres and MySQL natively. For MySQL, we have to use UNION to combine the results of left and right join and add a where condition. For Postgres, we have to use outer join and where condition.
| MySQL : | PostgreSQL: |
|---|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | mysql> SELECT A.id, B.id, A.val, B.val FROM A LEFT JOIN B ON A.id = B.id WHERE B.id IS NULL UNION SELECT A.id, B.id, A.val, B.val FROM A RIGHT JOIN B ON A.id = B.id WHERE A.id IS NULL; |
| | test=# SELECT A.id, B.id, A.val, B.val FROM A FULL OUTER JOIN B ON A.id = B.id WHERE A.id IS NULL OR B.id IS NULL; |
|
| +------+------+------+------+ | id | id | val | val | +------+------+------+------+ | 1 | NULL | V | NULL | | NULL | 3 | NULL | Y | +------+------+------+------+ 2 rows in set (0.00 sec) |
| | id | id | val | val ----+----+-----+----- 1 | | V | | 3 | | Y (2 rows) |
|
UNION and UNION ALL
UNION and UNION ALL both combine result sets of two or more select queries into a single result set. UNION produces a distinct result set where duplicate values are not shown. UNION ALL produces a result set that shows results from all the select queries. The duplicate values are not omitted; they are also shown.
| UNION : | UNION ALL: |
|---|
| SELECT A.id FROM A UNION SELECT B.id FROM B; |
| | SELECT A.id FROM A UNION ALL SELECT B.id FROM B; |
|
| +------+ | id | +------+ | 1 | | 2 | | 3 | +------+ |
| | +------+ | id | +------+ | 1 | | 2 | | 2 | | 3 | +------+ |
|
Mastering SQL Joins is essential for performant databases
Mastering SQL joins is an essential skill for any database user. It helps in enhancing query performance, extracting relevant data, and delivering insights. Above are examples of how the most commonly used queries for extracting data between two tables. Depending on the specific conditions, the queries can be modified further to filter out irrelevant data according to your needs. The usage and understanding of joins will help to easily create complex queries and assist in data retrieval and data analytics. Incorrect usage of joins could also make your query slower; always check the query performance using EXPLAIN to make optimizations and subsequent modifications in the query.
Discover more performance optimization best practices in our eBooks for MySQL or PostgreSQL.
Related
About the Author
Smit Arora Smit has been working with Percona since August 2022. He resides in Delhi and has been working in MySQL related technologies since 2018. He is interested in technology, movies and retro games.
Smit Arora
