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.
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', '[email protected]','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', '[email protected]','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', '[email protected]','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', '[email protected]','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.
Here is the syntax for MySQL INNER JOIN:
SELECT columns
FROM tableA
INNER JOIN tableB
ON tableA.column = tableB.column;
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.
Here is the syntax for MySQL LEFT JOIN:
SELECT columns
FROM tableA
LEFT [OUTER] JOIN tableB
ON tableA.column = tableB.column;
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.
Here is the syntax for MySQL RIGHT JOIN:
SELECT columns
FROM tableA
RIGHT [OUTER] JOIN tableB
ON tableA.column = tableB.column;
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.
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;
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
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. Also, you can watch this video tutorial and download our free brief guide to MySQL JOINs:
