Blog

Forward! With this tutorial I want you to learn clearly and directly the SQL JOIN clause.

Since there is not a single way to do a JOIN And since I know how important it is to do a good job with a database, in this article I am going to show you all the types of JOIN that exist with clear examples and simple explanations.

Content

• What is an SQL JOIN?
• Tables for JOIN examples
  • Example Class and Student tables
• INNER JOIN in SQL
  • JOIN on WHERE
• LEFT and RIGHT JOIN
• OUTER JOIN

What is an SQL JOIN?

A SQL JOIN it is the way that this language has to obtain related results between several tables in a coherent way.

The results will be records made up of information from various records in different tables.

A common use of the JOIN clause would be, for example, when we have two related tables and from which we want to obtain composite information.

The structure of a JOIN will always be the same and will be made up of 2 tables and their columns to be related:

SELECT *
FROM tabla1 JOIN tabla2 ON tabla1.columna1 = tabla2.columna3

The columns that intervene in the union of both tables must coincide in TYPE and VALUE. In this way the database will be able to do the union of the tables and return rows composed of the data of the row of the first table, plus the data of the row of the second table.

I know that it is difficult to understand it conceptually, for this very reason, I am going to introduce you to some tables that will serve as an example for the rest of the content of this article.

Tables for JOIN examples

Throughout this tutorial I will constantly use the information from 2 tables: players and teams

Example Class and Student tables

I create the Class table with the following query:

CREATE TABLE clase (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	codigo VARCHAR(20) NOT NULL,
	observaciones VARCHAR(200) NULL
);

The information that I have entered in the table for the examples is the following:

This table has only 3 columns:

• Column id which is used to uniquely identify each row in the table.
• Column code which is a 20 letter text to identify the class. For example 1A.
• Column observations which is a column with a small text as an observation. Example value: scandalous students.

Next I create the Student table

CREATE TABLE alumno(
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	nombre VARCHAR(20) NOT NULL,
	id_clase INT NULL
);

This table has 3 columns too and each one is for:

• id: It is used to uniquely identify each record.
• name: student’s name.
• class_id: reference column (foreign key) to the table class and his column id.

The values ​​of this table will be the following:

INNER JOIN in SQL

The first type of JOIN that you should know is the INNER (internal) is the most common type of JOIN and the one that you will probably use the most.

With a good command of this JOIN you will be able to perform complex data queries between related tables with great ease.

The INNER JOIN can be done in two different ways with the same results:

1. Specifying the clause and its components in the FROM clause
2. Linking columns directly in the WHERE.

INNER JOIN in FROM

SELECT alumno.nombre, alumno.apellido, clase.codigo
FROM alumno INNER JOIN clase ON alumno.id = clase.id;

JOIN on WHERE

Next I am going to perform an INNER JOIN in the WHERE clause and its result will be totally equivalent to the previous example in the FROM:

SELECT alumno.nombre, alumno.apellido, clase.codigo
FROM alumno, clase
WHERE alumno.id = clase.id;

LEFT and RIGHT JOIN

OUTER JOIN

SELF JOIN