Intro 👋🏾

I don't know !! , but many people seems blocked when it comes to sql joins 🙃. Many of us uses sql on daily basis but yet when it comes to joins we suck( this is a funny reality) . Why is that ? Is it because we don't know how to use basic sets ? Or just because the name "join" frighten us ? Well, we'll tackle that rapidly and you'll be glad this post came across your path😄.

Definition - What is sql join ?

Sql join simply as it is, it means coupling two more tables in one query to print out records related to these tables.

This allows the power of relational databases to be tapped for results that combine data from multiple tables in an efficient manner.

Type of joins

Several methods exist to link up two or more tables. Here are some in the list below :

• Inner join : This is the most common join you'll find in Sql. This join return records when the condition is true in both tables.

• Left (outer) join : This join returns all records from the left table even if the condition is not verified on the right table.

• Right (outer) join : This join returns all records from the right table even if the condition is not verified on the left table.

• Self join : This join enable us to make a join with the same table as if it was another table.

• Cross join : This join is quite particular. It generates a paired combination of each row of the first table with each row of the second table. This type of join is also known as cartesian join.

• Full (outer) join : This join returns all records when there is a match in either the left or right table.

• Natural join : This is a type of inner join that provides the result based on the column with the same data types present in tables to be joined.

Examples

We are going to use two(02) tables for our examples: student & study.

• student ( code, firstname , lastname, email)

• study ( code_student, city, code_department)

INNER JOIN

Query

-- Format query
SELECT *
FROM A 
INNER JOIN B ON A.key = B.key;

-- Example
SELECT * FROM student AS st
INNER JOIN study AS sy ON st.code = sy.code_student;

LEFT JOIN

Query

-- Format query
SELECT *
FROM A 
LEFT JOIN B ON A.key = B.key;

-- Example
SELECT * FROM student AS st
LEFT JOIN study AS sy ON st.code = sy.code_student;

LEFT OUTER JOIN

Query

-- Format query
SELECT *
FROM A 
LEFT JOIN B ON A.key = B.key
WHERE B.key IS NULL;

-- Example
SELECT * FROM student AS st
LEFT JOIN study AS sy ON st.code = sy.code_student
WHERE sy.key IS NULL;

RIGHT JOIN

Query

-- Format query
SELECT *
FROM A 
RIGHT JOIN B ON A.key = B.key;

-- Example
SELECT * FROM student AS st
RIGHT JOIN study AS sy ON st.code = sy.code_student;

RIGHT OUTER JOIN

Query

-- Format query
SELECT *
FROM A 
RIGHT JOIN B ON A.key = B.key
WHERE B.key IS NULL;

-- Example
SELECT * FROM student AS st
RIGHT JOIN study AS sy ON st.code = sy.code_student
WHERE sy.key IS NULL;

SELF JOIN

Query

-- Format query
SELECT *
FROM A 
INNER JOIN B
ON A.key = B.key;

-- Example
SELECT * FROM student AS st
INNER JOIN study AS sy ON st.code = sy.code_student;

FULL JOIN

Query

-- Format query
SELECT *
FROM A 
FULL JOIN B ON A.key = B.key;

-- Example
SELECT * FROM student AS st
FULL JOIN study AS sy ON st.code = sy.code_student;

FULL OUTER JOIN

Query

-- Format query
SELECT *
FROM A 
FULL JOIN B ON A.key = B.key
WHERE A.key IS NULL OR B.key IS NULL ;

-- Example
SELECT * FROM student AS st
INNER JOIN study AS sy ON st.code = sy.code_student
WHERE st.KEY IS NULL OR sy.KEY IS NULL ;

CROSS JOIN

In this example we will consider heavy meal for dinner. First let's create two tables.Then we populate them with some data.

-- Creation of the tables
CREATE TABLE complement ( complement VARCHAR(100));
CREATE TABLE sujet( sujet VARCHAR(100));

-- Inserting sample data
INSERT INTO complement
VALUES ("Plantain"),("Riz"),("Patate");
INSERT INTO sujet
VALUES ("sauce_tomate"), ("sauce_creme"), ("légumes");

-- Select query 
SELECT * from complement;
SELECT * from sujet;

-- Cross join query
SELECT * FROM complement 
CROSS JOIN sujet;
---- or
SELECT * FROM complement, sujet;

Results of the cross join query 👇🏾

Hope you had a great journey here, see you later on my next post ✨😃.