1. Introduction
Join is used to fetch results from related database tables.
Tables are related to each other using a key i.e. Primary, Foreign key, etc.
Thereβre 4 common types of Join:
- Inner Join
- Left Join
- Right Join
- Full Join
1.1. Inner Join
Only matching data from both tables will be fetched in this join.
1.2. Left Join:
Matching data from both tables and all unmatched records from the Left side table will be fetched.
1.3. Right Join
Matching data from both tables and all unmatched records from the Right side table will be fetched in this join.
1.4. Full Join:
Matching data and all unmatched data from both tables will be fetched.
Classic Join Diagram:
2. Example
To understand Join, let's consider below example:
DEPARTMENT_TABLE:
Contains department information, here dep_id is the primary key.
EMPLOYEE_TABLE:
Contains employee information, here emp_id is the primary key and dept_id is a foreign key.
3. Inner Join
Syntax:
"SELECT * FROM <T1> ππ‘π‘ππ₯ ππππ <T2> ON <T1>.COL1=<T2>.COL1;"
Eg:
When we query Department and Employee table, then only matching records from both tables are fetched as shown below.
4. Left Join
Syntax:
"SELECT * FROM <T1> πππ π ππππ <T2> ON <T1>.COL1=<T2>.COL1;"
Eg:
This time, Matching records from both tables are fetched along with all unmatched records of the left table(DEPARTMENT_TABLE).
5. Right Join
Syntax:
"SELECT * FROM <T1> πππππ ππππ <T2> ON <T1>.COL1=<T2>.COL1;"
Eg:
This time, Matching records from both tables are fetched along with all unmatched records of the Right table(EMPLOYEE_TABLE).
6. Full Join
Syntax:
"SELECT * FROM <T1> π πππ ππππ <T2> ON <T1>.COL1=<T2>.COL1;"
Eg:
This time, Matching records from both tables are fetched along with all unmatched records from the Left(DEPARTMENT_TABLE) and Right table(EMPLOYEE_TABLE).
That's a wrap!
I help people get started with Backend Development.
Follow me @Vikas Rajput for more.
If you find this thread helpful then Like/Retweet the first tweet below:
Before you go, do you know?
I've already started writing on LinkedIn
I share some exclusive content there, which I never share here.
Follow me on Linkedin to stay updated with Backend content:
https://www.linkedin.com/in/vikasrajputinβ¦