Join MySQL: the different forms of union between tables

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?

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:

  1. Innerjoin 
  2. Left join
  3. Right join
  4. 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:

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:

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: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:

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 | 

+----------+---------+-----------+---------+---------+------------+ 

Pilar Mirás

Technical Project Analyst

Analítica y amante de los datos a partes iguales, combino mi pasión por las nuevas tecnologías con el mundo de la Investigación Criminal y siempre que tengo oportunidad, aprovecho para disfrutar de mi familia.

Wanna talk?
Only PDF, Word and OpenOffice files. Size: 2MB max.

FOR YOUR INTEREST
Premium Leads S. L. will only use your data to answer your request and they will never be given to third parties. To exercise your rights, check our privacy policy.

Contacta con nosotros a través de WhatsApp