JOINs in SQL, fully explained.

⇩

➊ Introduction

⬘ A JOIN clause in SQL corresponds to a join operation in relational algebra.

⬙ A join operation combines columns from one or more tables.

⬗ There are 5 types of JOINs

➤ INNER
➤ LEFT OUTER
➤ RIGHT OUTER
➤ FULL OUTER
➤ CROSS

➋ Why do we require JOIN?

⬘ In a relational database, data is kept mostly normalized in tables.

⬗ By normalization, we mean tables are defined as independent as possible.

⬙ But, fetching meaningful data may require combining multiple tables, for which we require JOIN.

➌ How does a JOIN mostly operate?

⬘ The tables/views are joined using a condition. The condition mostly involves the matching column(s).

⬙ Based on the matching conditions and the type of join we use, the results may vary.

➍ Common Syntax

SELECT <COLUMN_NAMES>
FROM <TABLE_OR_VIEW_1>
<JOIN_CLAUSE>
<TABLE_OR_VIEW_2>
ON
<JOIN_CONDITION>

➎ Data for our examples

We will have 2 tables.

➤ TEACHERS
ID NAME
100 Deepesh
200 Jayati
300 Chris

➤ STUDENTS
ID NAME TEACHER_ID
1 Misha 100
2 Pavan 100
3 Sanjay 200
4 Prava 200
5 Shyam 100
6 Mrinal 400

➏ INNER JOIN

⬘ Inner Join fetches only those records for which the joining condition fully matches.

⬙ Example

To fetch the teacher's name for each student,

SELECT S. ID, S. NAME, T. NAME
FROM STUDENTS S
INNER JOIN TEACHERS T
ON
S. TEACHER_ID = T. ID

⬗ Result

S. ID S. NAME T. NAME
1 Misha Deepesh
2 Pavan Deepesh
3 Sanjay Jayati
4 Prava Jayati
5 Shyam Deepesh

➐ LEFT OUTER JOIN

⬘ LEFT OUTER JOIN or simply LEFT JOIN is one type of outer join.

⬙ In this type of join, the left-side table retains all data even if there is no matching row.

⬗ Example

To get the teacher's name for all students,

SELECT S. ID, S. NAME, T. NAME
FROM STUDENTS S
LEFT JOIN TEACHERS T
ON
S. TEACHER_ID = T. ID

⬖ Result

S. ID S. NAME T. NAME
1 Misha Deepesh
2 Pavan Deepesh
3 Sanjay Jayati
4 Prava Jayati
5 Shyam Deepesh
6 Mrinal NULL

➑ RIGHT OUTER JOIN

⬘ RIGHT OUTER JOIN or simply RIGHT JOIN is one type of outer join.

⬙ In this type of join, the right-side table retains all data even if there is no matching row.

✧ Both LEFT and RIGHT joins can replace each other functionally.

⬗ Example

To get the teacher's name for all students,

SELECT S. ID, S. NAME, T. NAME
FROM TEACHERS T
RIGHT JOIN STUDENTS S
ON
S. TEACHER_ID = T. ID

⬖ Result

S. ID S. NAME T. NAME
1 Misha Deepesh
2 Pavan Deepesh
3 Sanjay Jayati
4 Prava Jayati
5 Shyam Deepesh
6 Mrinal NULL

➒ FULL OUTER JOIN

⬘ FULL OUTER JOIN or simply FULL JOIN is one type of outer join.

⬙ In this type of join, both side tables retain all data even if there is no matching row.

⬗ Example

Fetch all students and their teacher's names, along with unassigned students and teachers.

SELECT S. ID, S. NAME, T. NAME
FROM STUDENTS S
FULL JOIN TEACHERS T
ON
S. TEACHER_ID = T. ID

⬖ Result

S. ID S. NAME T. NAME
1 Misha Deepesh
2 Pavan Deepesh
3 Sanjay Jayati
4 Prava Jayati
5 Shyam Deepesh
6 Mrinal NULL
NULL NULL Chris

➓ CROSS JOIN

⬘ CROSS JOIN returns the Cartesian product of rows from tables in the join.

⬙ So, it will produce rows that combine each row from the first table with each row from the second table.

⬗ Example

SELECT S. ID, S. NAME, T. NAME
FROM STUDENTS S
CROSS JOIN TEACHERS T;

Or,

SELECT S. ID, S. NAME, T. ID
FROM STUDENTS S, TEACHERS T;

⬖ Result

S. ID S. NAME T. ID
1 Misha 100
1 Misha 200
1 Misha 300
2 Pavan 100
2 Pavan 200
2 Pavan 300
...

➊➊ Other Types of Joins

➀ Equi Join
A join where the condition (predicate) involves only an equality check.

➁ Natural Join
A special type of Equi Join where each similar columns are equi-joined.

➂ Self Join
If both sides of the join are the same entity.

Hey 👋

I am a Tech Writer, Educator, and Mentor from India 🇮🇳, here sharing

✅ Tutorials
✅ Tricks
✅ Career Tips
✅ Cheat Sheets
✅ Practice Questions
✅ Roadmaps
on
➠ Web Development
➠ Data Structures and Algorithms
➠ Databases

Thanks for reading. 🙏