JOINs in SQL, fully explained.
⇩
⇩
We will explain:
❯ Inner Join
❯ Outer Join
❯ Left Outer
❯ Right Outer
❯ Full Outer
❯ Cross Join
❯ Others
❯ Equi Join
❯ Natural Join
❯ Self Join
❯ Anti Join
❯ Semi Join
Now, let's start with the introduction.
❯ Inner Join
❯ Outer Join
❯ Left Outer
❯ Right Outer
❯ Full Outer
❯ Cross Join
❯ Others
❯ Equi Join
❯ Natural Join
❯ Self Join
❯ Anti Join
❯ Semi Join
Now, let's start with the introduction.
➊ 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
⬘ 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.
⬘ 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.
⬘ 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>
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
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
⬘ 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
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.
⬘ 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
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
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.
⬘ 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
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
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.
⬘ 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
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
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.
⬘ 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;
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
...
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
➀ 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.
➀ 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.
➃ Semi Join
It returns one copy of the rows from a table for which at least one match is found in the other table.
➄ Anti Join
It returns one copy of the rows from a table for which no match is found in the other table. NOT EXISTS or NOT IN are used for it.
It returns one copy of the rows from a table for which at least one match is found in the other table.
➄ Anti Join
It returns one copy of the rows from a table for which no match is found in the other table. NOT EXISTS or NOT IN are used for it.
Hello Twitterati 👋
I am a Tech Writer, Educator, and Mentor from India 🇮🇳
I am sharing
❯ Tutorials
❯ Career Tips
❯ Roadmaps
❯ Useful Resources
Do you find this guide useful? Reach out to me: @swapnakpanda
Like, Retweet, and Comment on this tweet.
I am a Tech Writer, Educator, and Mentor from India 🇮🇳
I am sharing
❯ Tutorials
❯ Career Tips
❯ Roadmaps
❯ Useful Resources
Do you find this guide useful? Reach out to me: @swapnakpanda
Like, Retweet, and Comment on this tweet.
Loading suggestions...