Math + You = 1

# Everything you need to know in SQL – Part 3

This course on SQL aims to provide a basis for anyone who would like to have a global vision on this subject. It allows to have a global vision on how to read a database and make queries. This article is the third in a series of 5 articles and is intended to present the joins.

This course is the continuation of 2 part, il va présenter les jointures en SQL. If you are completely new to SQL, start with 1 part !

## Joins

Suppose we now have a second table called revenue, giving the gross annual salary according to the social security number. So we have 2 columns:

• A first column secu who is the security number
• A second column salary which is the gross annual salary

Thanks to the joins, we will be able to make the link between this table and the previous table using a join key. A join key and a set of rules between columns to link multiple tables together. Here for example, the two tables have in common the social security number which is a unique identifier.
Here is the table revenue :

Note: We do not have a known salary for social security number 6 and we do not have the surname or first name of social security number 7 (see the table in 1 part)
We have different types of joins, which will now be presented.

### The different types of join

• LEFT JOIN: We keep all the results of the left table even if the join key is not present in the right table
• RIGHT JOIN: We keep all the results of the right table even if the join key is not present in the right table
• INNER JOIN: We keep the results if the join condition is true in the 2 tables.
• FULL JOIN: We keep the results if the join condition is true on one side or the other.
• CROSS JOIN: Join allowing the Cartesian product between two tables.

A bit of math now, let's define what a Cartesian product is.

Cartesian product : The Cartesian product of two sets is the set of couples X and Y whose first component belongs to X and the second component belongs to Y.

Concretely, if we make a CROSS JOIN between a table which has 10 rows and a table which has 50 rows, we obtain a table of 10 x 50 = 500 rows corresponding to all the possible crossings.

### The LEFT JOIN operator

Here is a diagram to properly represent the left join:

Suppose we want to retrieve the surnames, first names and salaries of people whose first and last names we know. Here is the query we are going to do:

``````SELECT
nom, prenom, salaire
FROM
personnes
LEFT JOIN
revenue
ON
secu_sociale = secu
``````

Here is the result of the SQL query.

### The RIGHT JOIN operator

Here is a diagram representing the right join:

We want to find the surnames, first names and salaries of people whose salaries we know. Here is the SQL query we will do for this:

``````SELECT
nom, prenom, salaire
FROM
personnes
RIGHT JOIN
revenue
ON
secu_sociale = secu
``````

And here is what we get

### The FULL JOIN operator

Here is a graphical representation of the full join:

We want to find the surnames, first names and salaries of people for whom we have at least one piece of information. Here is the SQL query we will do for this:

``````SELECT
nom, prenom, salaire
FROM
personnes
FULL JOIN
revenue
ON
secu_sociale = secu
``````

We then obtain:

### The INNER JOIN operator

Here is the graphical representation of this operator:

We want to select the surnames, first names and salaries of the people who are present in the 2 tables

``````SELECT
nom, prenom, salaire
FROM
personnes
INNER JOIN
revenue
ON
secu_sociale = secu
``````

And we get as query result:

We have all the tables containing all the information.

## The UNION operator

Now suppose we have another part of the base table called person2. Thanks to the UNION function, we can combine the two tables.

Here is the table person2

### The UNION ALL operator

With the UNION ALL operator, we concatenate the two tables in a row, without asking any questions

``````SELECT
*
FROM
personne
UNION ALL
SELECT
*
FROM
personne2
``````

Here is the result :

### The DISTINCT UNION operator

The two tables are concatenated one after the other, but removing the duplicates. This is done through UNION DISTINCT

``````SELECT
*
FROM
personne
UNION DISTINCT
SELECT
*
FROM
personne2
``````

We therefore obtain:

## The INTERSECT operator

We are looking for rows that are in both the first and the second table. Here is the query to get this result:

``````SELECT
*
FROM
personne
INTERSECT
SELECT
*
FROM
personne2
``````

We then obtain the following result:

## And now the training

To practice this part 3, do SQLBolt exercises 6 to 8:

https://sqlbolt.com/lesson/select_queries_with_joins

And that's it for this part 3 on joins in SQL. Next article: aggregation functions!