Master SQL JOINS:🧵👇
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
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.
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:
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.
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.
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).
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).
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).
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 @vikasrajputin for more.
If you find this thread helpful then Like/Retweet the first tweet below:
I help people get started with Backend Development.
Follow me @vikasrajputin 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:
linkedin.com
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:
linkedin.com
جاري تحميل الاقتراحات...