Progress-in-maths

Math + You = 1

SQL
Courses data processing

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 :

secusalary
130 000
250 000
321 000
470 000
545 000
741 000

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:

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.

namefirst namesalary
DuchenePierre30 000
DupontPaul50 000
DupontBaptiste21 000
DurandJean70 000
DurandJacques45 000
GirardBaptistenull

The RIGHT JOIN operator

Here is a diagram representing the right join:

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

namefirst namesalary
DuchenePierre30 000
DupontPaul50 000
DupontBaptiste21 000
DurandJean70 000
DurandJacques45 000
nullnull41 000

The FULL JOIN operator

Here is a graphical representation of the full join:

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:

namefirst namesalary
DuchenePierre30 000
DupontPaul50 000
DupontBaptiste21 000
DurandJean70 000
DurandJacques45 000
GirardBaptistenull
nullnull41 000

The INNER JOIN operator

Here is the graphical representation of this operator:

Inner join

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:

namefirst namesalary
DuchenePierre30 000
DupontPaul50 000
DupontBaptiste21 000
DurandJean70 000
DurandJacques45 000

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

secu_socialenamefirst namecity
4DupontPaulParisis
11DoeJohnLondon
12SmithJoshManchester

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 :

secu_socialenamefirst namecity
1DupontBaptisteParis
2DurandJeanMarseille
3DuchenePierreLyon
4DupontPaulParisis
5DurandJacquesMarseille
6GirardBaptisteParis
4DupontPaulParisis
11DoeJohnLondon
12SmithJoshManchester

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:

secu_socialenamefirst namecity
1DupontBaptisteParis
2DurandJeanMarseille
3DuchenePierreLyon
4DupontPaulParisis
5DurandJacquesMarseille
6GirardBaptisteParis
11DoeJohnLondon
12SmithJoshManchester

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:

secu_socialenamefirst namecity
4DupontPaulParisis

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!

1 COMMENTS

Leave comments