Throughout this post we are going to do a brief review of the different forms of union between tables that exist in the MySQL language, explaining it in a very simple way.
But for this, let’s start with a definition … What is a relational database?
Table of Contents
What is a relational database?
A relational database consists of several related data tables that are linked by common columns, known as Foreign Keys.
On the other hand we have the JOIN, or union. The JOIN is a method of linking data between one or more tables, based on the common column values between the tables.
MySQL in particular supports the following types of joins:
- Innerjoin
- Left join
- Right join
- Cross join
Creating the database
We are going to first create the database with which we are going to work. In this case we will use an example familiar to us, simplifying it as much as possible: our Maestralia Training Centers portal.
In our example, we will use it to see what the structure of the database of students who sign up for our courses would look like. To do this, we create a database called Maestralia and two tables: one of courses and the other of students that are related to each other by the idCurso column.
We start by creating the database:
mysql> CREATE DATABASE maestralia;
We select the database that we are going to use:
mysql> USE maestralia;
We create two tables:
Courses table
mysql> CREATE TABLE cursos ( -> idCurso INT AUTO_INCREMENT, -> curso VARCHAR(100), -> PRIMARY KEY(idCurso) -> );
We insert several test courses:
mysql> INSERT INTO cursos (curso)
-> VALUES ('Auxiliar de Enfermería'),
-> ('Doblaje'),
-> ('Pastelería');
SELECT * FROM cursos;
+---------+------------------------+
| idCurso | curso |
+---------+------------------------+
| 1 | Auxiliar de Enfermería |
| 2 | Doblaje |
| 3 | Pastelería |
+---------+------------------------+
Students table
mysql> CREATE TABLE alumnos ( -> idAlumno INT AUTO_INCREMENT, -> nombre VARCHAR(100), -> apellidos VARCHAR(100), -> idCurso INT, -> FOREIGN KEY (idCurso) REFERENCES cursos(idCurso), -> PRIMARY KEY(idAlumno) -> );
We insert data from test students:
INSERT INTO alumnos (nombre, apellidos, idCurso)
-> VALUES('Pilar', 'Mirás', '2'),('Antonio', 'Cambados', '2'),
-> ('Pedro', 'Fraile', '3'),('Miguel', 'Yañez', '3'),
-> ('Carlos', 'Sanchidrián', NULL), ('Alejandro', 'Rey', NULL);
mysql> SELECT * FROM alumnos;
+----------+-----------+-------------+---------+
| idAlumno | nombre | apellidos | idCurso |
+----------+-----------+-------------+---------+
| 7 | Pilar | Mirás | 2 |
| 8 | Antonio | Cambados | 2 |
| 9 | Pedro | Fraile | 3 |
| 10 | Miguel | Yañez | 3 |
| 11 | Carlos | Sanchidrián | NULL |
| 12 | Alejandro | Rey | NULL |
+----------+-----------+-------------+---------+
Once we have the data loaded, we begin to review the various join options.
INNER JOIN
The INNER JOIN clause joins two tables based on a common column by comparing each row in the first table with each row in the second table.
If the values of both rows match, the INNER JOIN clause creates a new row whose column contains all the columns from the two rows of both tables and includes this new row in the result set.
The INNER JOIN clause only includes matching rows from both tables as can be seen in the following example:
mysql> SELECT * -> FROM alumnos a -> INNER JOIN cursos c -> ON a.idCurso = c.idCurso; +----------+---------+-----------+---------+---------+------------+ | idAlumno | nombre | apellidos | idCurso | idCurso | curso | +----------+---------+-----------+---------+---------+------------+ | 7 | Pilar | Mirás | 2 | 2 | Doblaje | | 8 | Antonio | Cambados | 2 | 2 | Doblaje | | 9 | Pedro | Fraile | 3 | 3 | Pastelería | | 10 | Miguel | Yañez | 3 | 3 | Pastelería | +----------+---------+-----------+---------+---------+------------+
The following Venn diagram illustrates the behavior of the INNER JOIN:
LEFT JOIN
The LEFT JOIN is similar to the INNER JOIN, only that when selected it shows all the values of the table on the left even if it does not have a correspondence in the table on the right. For each row in the table on the left, the LEFT JOIN compares each row in the table on the right.
If the value of the two rows meets the JOIN condition, the LEFT JOIN clause creates a new row that contains all the columns of the two tables, including those that do not meet the join condition.
If the value does not match, the LEFT JOIN still creates a row that contains all the data in the table on the left, and the match to the table on the right is completed with NULL.
The LEFT JOIN clause includes all the rows in the table on the left as can be seen in the following example:
mysql> SELECT * -> FROM alumnos a -> LEFT JOIN cursos c -> ON a.idCurso = c.idCurso; +----------+-----------+-------------+---------+---------+------------+ | idAlumno | nombre | apellidos | idCurso | idCurso | curso | +----------+-----------+-------------+---------+---------+------------+ | 7 | Pilar | Mirás | 2 | 2 | Doblaje | | 8 | Antonio | Cambados | 2 | 2 | Doblaje | | 9 | Pedro | Fraile | 3 | 3 | Pastelería | | 10 | Miguel | Yañez | 3 | 3 | Pastelería | | 11 | Carlos | Sanchidrián | NULL | NULL | NULL | | 12 | Alejandro | Rey | NULL | NULL | NULL | +----------+-----------+-------------+---------+---------+------------+
The following Venn diagram illustrates the behavior of the LEFT JOIN:
RIGHT JOIN
The RIGHT JOIN is similar to the INNER JOIN, only that when selected it shows all the values of the table on the right, even if it does not have correspondence in the table on the left. For each row in the table on the right, the RIGHT JOIN compares each row in the table on the left.
If the value of the two rows meets the JOIN condition, the RIGHT JOIN clause creates a new row that contains all the columns of the two tables, including those that do not meet the join condition.
The RIGHT JOIN clause includes all the rows in the table on the right as can be seen in the following example:
mysql> SELECT * -> FROM alumnos a -> RIGHT JOIN cursos c -> ON a.idCurso = c.idCurso; +----------+---------+-----------+---------+---------+------------------------+ | idAlumno | nombre | apellidos | idCurso | idCurso | curso | +----------+---------+-----------+---------+---------+------------------------+ | NULL | NULL | NULL | NULL | 1 | Auxiliar de Enfermería | | 1 | Pilar | Mirás | 2 | 2 | Doblaje | | 2 | Antonio | Cambados | 2 | 2 | Doblaje | | 3 | Pedro | Fraile | 3 | 3 | Pastelería | | 4 | Miguel | Yañez | 3 | 3 | Pastelería | +----------+---------+-----------+---------+---------+------------------------+
The following Venn diagram illustrates the behavior of the RIGHT JOIN:
CROSS JOIN
The CROSS JOIN is a special type of JOIN that returns the Cartesian product between the tables that are part of the union. It behaves the same as the INNER JOIN as if it did not have the “ON” part:
mysql> SELECT * -> FROM alumnos a -> CROSS JOIN cursos c; +----------+-----------+-------------+---------+---------+------------------------+ | idAlumno | nombre | apellidos | idCurso | idCurso | curso | +----------+-----------+-------------+---------+---------+------------------------+ | 7 | Pilar | Mirás | 2 | 1 | Auxiliar de Enfermería | | 7 | Pilar | Mirás | 2 | 2 | Doblaje | | 7 | Pilar | Mirás | 2 | 3 | Pastelería | | 8 | Antonio | Cambados | 2 | 1 | Auxiliar de Enfermería | | 8 | Antonio | Cambados | 2 | 2 | Doblaje | | 8 | Antonio | Cambados | 2 | 3 | Pastelería | | 9 | Pedro | Fraile | 3 | 1 | Auxiliar de Enfermería | | 9 | Pedro | Fraile | 3 | 2 | Doblaje | | 9 | Pedro | Fraile | 3 | 3 | Pastelería | | 10 | Miguel | Yañez | 3 | 1 | Auxiliar de Enfermería | | 10 | Miguel | Yañez | 3 | 2 | Doblaje | | 10 | Miguel | Yañez | 3 | 3 | Pastelería | | 11 | Carlos | Sanchidrián | NULL | 1 | Auxiliar de Enfermería | | 11 | Carlos | Sanchidrián | NULL | 2 | Doblaje | | 11 | Carlos | Sanchidrián | NULL | 3 | Pastelería | | 12 | Alejandro | Rey | NULL | 1 | Auxiliar de Enfermería | | 12 | Alejandro | Rey | NULL | 2 | Doblaje | | 12 | Alejandro | Rey | NULL | 3 | Pastelería | +----------+-----------+-------------+---------+---------+------------------------+
The following Venn diagram illustrates the behavior of the CROSS JOIN:
If we add the ON clause, it will behave exactly the same as the INNER JOIN:
mysql> SELECT * -> FROM alumnos a -> CROSS JOIN cursos c -> ON a.idCurso = c.idCurso; +----------+---------+-----------+---------+---------+------------+ | idAlumno | nombre | apellidos | idCurso | idCurso | curso | +----------+---------+-----------+---------+---------+------------+ | 7 | Pilar | Mirás | 2 | 2 | Doblaje | | 8 | Antonio | Cambados | 2 | 2 | Doblaje | | 9 | Pedro | Fraile | 3 | 3 | Pastelería | | 10 | Miguel | Yañez | 3 | 3 | Pastelería | +----------+---------+-----------+---------+---------+------------+
