MySQL JOINs Tutorial with Examples

April 13th, 2021

The article presents a detailed tutorial on MySQL JOINs. In it, you will learn about different types of JOINS that are supported in MySQL, their syntax, and how to use them.

Introduction to MySQL JOINs
Different types of JOINs in MySQL
1. MySQL INNER JOIN clause
2. MySQL OUTER JOINs
2.1 MySQL LEFT JOIN clause
2.2 MySQL RIGHT JOIN clause
3. MySQL CROSS JOIN clause
MySQL JOINs guidelines
Conclusion

Introduction to MySQL JOINs

MySQL databases usually store large amounts of data. To analyze that data efficiently, analysts and DBAs have a constant need to extract records from two or more tables based on certain conditions. That’s where JOINs come to the aid.

JOINS are used to retrieve data from multiple tables in a single query. For JOINs to work, the tables need to be related to each other with a common key value.
JOIN clauses are used in the SELECT, UPDATE, and DELETE statements.

download dbForge Studio for MySQL

Different types of JOINs in MySQL

MySQL JOIN type defines the way two tables are related in a query. MySQL supports the following types of JOIN clauses: INNER JOIN, OUTER JOIN, and CROSS JOIN. OUTER JOINs can further be divided into LEFT JOINs and RIGHT JOINs.

To better demonstrate how the JOINs work, we will create two tables.

CREATE TABLE `users` (
  `auid` int(10) UNSIGNED NOT NULL,
  `username` varchar(100) NOT NULL,
  `password` varchar(150) NOT NULL,
  `createdate` datetime NOT NULL,
  `isActive` tinyint(1) NOT NULL
);


CREATE TABLE `userprofile` (
  `apid` int(10) UNSIGNED NOT NULL,
  `auid` int(10) UNSIGNED NOT NULL,
  `firstname` varchar(50) NOT NULL,
  `lastname` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(45) NOT NULL
);

And fill them with data.

Insert into users
(auid, username,password, createdate, isActive)
values
(1,'admin','pswrd123', curdate(), 1);
Insert into userprofile
(apid, auid, firstname, lastname, email, phone)
values
(1,1,'Jack', 'Wolf', 'bettestroom@gmail.com','600075764216');
Insert into users
(auid,username,password, createdate, isActive)
values
(2, 'admin1','pass506', curdate(), 1);
Insert into userprofile
(apid, auid, firstname, lastname, email, phone)
values
(2, 3, 'Tom', 'Collins', 'tnkc@outlook.com','878511311054');
Insert into users
(auid, username,password, createdate, isActive)
values
(4,'fox12','45@jgo0', curdate(), 1);
Insert into userprofile
(apid, auid, firstname, lastname, email, phone)
values
(4,5,'Bill', 'Fonskin', 'bill_1290@gmail.com','450985764216');
Insert into users
(auid,username,password, createdate, isActive)
values
(6, 'lexus1267','98hnfRT6', curdate(), 1);
Insert into userprofile
(apid, auid, firstname, lastname, email, phone)
values
(7, 7, 'Ivan', 'Levchenko', 'ivan_new@outlook.com','878511311054');

1. MySQL INNER JOIN clause

INNER JOINs are used to fetch only common matching records. The INNER JOIN clause allows retrieving only those records from Table A and Table B, that meet the join condition. It is the most widely used type of JOIN.

To gain a better understanding of INNER JOINs, look at the Venn diagram below.

MySQL INNER JOIN

Here is the syntax for MySQL INNER JOIN:

SELECT columns
FROM tableA 
INNER JOIN tableB
ON tableA.column = tableB.column;
MySQL INNER JOIN in dbForge Studio for MySQL

2. MySQL OUTER JOINs

In contrast to INNER JOINs, OUTER JOINs return not only matching rows but non-matching ones as well. In case there are non-matching rows in a joined table, the NULL values will be shown for them.

There are the following two types of OUTER JOIN in MySQL: MySQL LEFT JOIN and MySQL RIGHT JOIN. Let’s take a closer look at each of them.

2.1 MySQL LEFT JOIN clause

LEFT JOINs allow retrieving all records from Table A, along with those records from Table B for which the join condition is met. For the records from Table A that do not match the condition, the NULL values are displayed.

To get a better understanding of LEFT JOINs, study a Venn diagram below.

MySQL LEFT JOIN

Here is the syntax for MySQL LEFT JOIN:

SELECT columns
FROM tableA
LEFT [OUTER] JOIN tableB
ON tableA.column = tableB.column;
MySQL LEFT JOIN in dbForge Studio for MySQL

2.2 MySQL RIGHT JOIN clause

Accordingly, RIGHT JOINs allow retrieving all records from Table B, along with those records from Table A for which the join condition is met. For the records from Table B that do not match the condition, the NULL values are displayed.

Below is a Venn diagram for you to gain a deeper insight into RIGHT JOINs.

MySQL RIGHT JOIN

Here is the syntax for MySQL RIGHT JOIN:

SELECT columns
FROM tableA
RIGHT [OUTER] JOIN tableB
ON tableA.column = tableB.column;
MySQL RIGHT JOIN in dbForge Studio for MySQL

3. MySQL CROSS JOIN clause

MySQL CROSS JOIN, also known as a cartesian join, retrieves all combinations of rows from each table. In this type of JOIN, the result set is returned by multiplying each row of table A with all rows in table B if no additional condition is introduced.

To better understand CROSS JOINs, study a Venn diagram below.

MySQL CROSS JOIN

When you might need that type of JOIN? Envision that you have to find all combinations of a product and a color. In that case, a CROSS JOIN would be highly advantageous. 

Here is the syntax for MySQL CROSS JOIN:

SELECT columns
FROM tableA
CROSS JOIN tableB;
CROSS JOIN in dbForge Studio for MySQL

MySQL JOINs guidelines

JOINs in MySQL allow you to use a single JOIN query instead of running multiple simple queries. Thus, you can achieve better performance, reduce server overhead, and decrease the number of data transfers between MySQL and your application.

Unlike SQL Server, MySQL does not support FULL OUTER JOIN as a separate JOIN type. However, to get the results same to FULL OUTER JOIN, you can combine LEFT OUTER JOIN and RIGHT OUTER JOIN.

SELECT * FROM tableA
LEFT JOIN tableB ON tableA.id = tableB.id
UNION
SELECT * FROM tableA
RIGHT JOIN tableB ON tableA.id = tableB.id
MySQL FULL OUTER JOIN

Using MySQL JOINs, you can also join more than two tables.

SELECT *
FROM tableA
LEFT JOIN tableB
ON tableA.id = tableB.id
LEFT JOIN tableC
ON tableC.id = tableA.id;

Conclusion

It is vitally important for any analyst and DBA to have a thorough understanding of  JOINs and use them freely in everyday work. That’s where dbForge Studio for MySQL becomes indispensable. Its advanced code completion works impeccably even for complex JOIN clauses. You don’t need to memorize hundreds of column names or aliases, dbForge Studio for MySQL will prompt you a full JOIN clause. Its feature-rich functionality significantly simplifies building complex queries and managing JOIN conditions.

download dbForge Studio for MySQL

Comments are closed.