SQL Joins Explained: Types and Examples
Translated by
ScribdTranslationsSQL Joins Explained: Types and Examples
Translated by
ScribdTranslationsSQL Join
z
z
Joins in SQL allow to associate
several tables in the same query. This
allows you to exploit the power of the databases
relational data to obtain results
which combine data from several tables of
efficiently.
z
INNER JOIN
z
inner join to return records when the condition is
true in both tables. It's one of the most common joins.
SELECT * FROM A INNER JOIN B ON [Link] = [Link]
z
SELECT * FROM post
INNER JOIN category ON post.category_id = category.category_id;
z
LEFT JOIN
outer join to return all records from the left table (LEFT =
left) even if the condition is not checked in the other table.
SELECT * FROM A LEFT JOIN B ON [Link] = [Link]
SELECT * FROM post AS p
z LEFT JOIN category AS c ON p.category_id = c.category_id;
LEFT JOIN (without the intersection of B)
z
Left join (LEFT JOIN without the intersection B)
SELECT * FROM A LEFT JOIN B ON [Link] = [Link] WHERE [Link] IS NULL
RIGHT JOIN
z
right join to return all records from the right table (RIGHT =
right) even if the condition is not verified in the other table.
Right Join
SELECT * FROM A RIGHT JOIN B ON [Link] = [Link]
SELECT * FROM post AS p
RIGHT JOIN category AS c ON p.category_id = c.category_id;
z
z RIGHT JOIN (without the intersection of A)
Right join (RIGHT JOIN without intersection A)
SELECT * FROM A RIGHT JOIN B ON [Link] = [Link] WHERE [Link] IS NULL
FULL JOIN
z
outer join to return results when the condition is true in at least one
of the 2 tables.
Union of 2 sets
SELECT * FROM A FULL JOIN B ON [Link] = [Link]
(SELECT * FROM post AS p
LEFT JOIN category AS c ON p.category_id = c.category_id
z UNION
(SELECT * FROM post AS p
RIGHT JOIN category AS c ON p.category_id = c.category_id);
CROSS JOIN
z
cross join allowing the Cartesian product of 2 tables. In other words,
allows joining each row of one table with each row of a second table.
Attention, the number of results is generally very high.
SELECT * FROM table1 CROSS JOIN table2
Example
a recipe app
cuisines that contain 2 tables
of ingredients, the vegetable table and the
table fruit
For some reason, the application must associate all vegetables with all.
the fruits. All combinations must be displayed. For this it is appropriate
z to make one or the other of the following requests:
SELECT l_id, l_nom_fr_fr, f_id, f_nom_fr_fr FROM legume CROSS JOIN fruit
The result clearly shows that each vegetable is paired with each fruit. With 3 fruits and 3 vegetables, it
There are therefore 9 lines of results (3 x 3 = 9).
SQL SELF JOIN
z
In SQL, a SELF JOIN corresponds to a join of a table with itself. This type
The request is not so common but very practical in cases where a table links...
information with records from the same table.
SELECT `t1`.`column_name1`, `t1`.`column_name2`, `t2`.`column_name1`, `t2`.`column_name2`
FROM `table` as `t1`
LEFT OUTER JOIN `table` as `t2` ON `t2`.`fk_id` = `t1`.`id`
SQL NATURAL JOIN
z
In SQL, the NATURAL JOIN command allows for a natural join.
between 2 tables. This join is performed on the condition that there are columns with the same name
and of the same type in both tables. The result of a natural join is the creation of a
table with as many rows as there are pairs corresponding to the association of
columns with the same name.
SELECT *
FROM table1
NATURAL JOIN table2
The advantage of a NATURAL JOIN is that there is no need to use the ON clause.
z
SELECT *
FROM user
NATURAL JOIN pays
z
Cet exemple montre qu’il y a bien eu une jointure entre les 2 tables grâce à la
column 'country_id' which is found in both tables.