SQL: Beginner Guide to Joins
a thread...
a thread...
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 types of Join:
1. Inner Join
2. Left Join
3. Right Join
4. Full Join
Tables are related to each other using a key i.e. Primary, Foreign key, etc.
There’re 4 types of Join:
1. Inner Join
2. Left Join
3. Right Join
4. Full Join
Inner Join:
Only matching data from both tables will be fetched in this join.
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.
Left Join:
Matching data from both tables and all unmatched records from the Left side table will be fetched.
Right Join:
Matching data from both tables and all unmatched records from the Right side table will be fetched in this join.
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.
Full Join:
Matching data and all unmatched data from both tables will be fetched.
Classic Join Diagram:
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.
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.
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.
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).
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).
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).
Conclusion:
Out of all the four Joins, most common one which you will use is the inner join. So make sure you understand that better.
Least one which you will use is Outer Join.
But yes from SQL interview perspective it's advisable to know basics of all the Joins.
Out of all the four Joins, most common one which you will use is the inner join. So make sure you understand that better.
Least one which you will use is Outer Join.
But yes from SQL interview perspective it's advisable to know basics of all the Joins.
That's it for now!
If you like this then you might also like reading my previous thread on git:
If you like this then you might also like reading my previous thread on git:
Thanks for Reading.
Namaste, I'm Vikas!
Every Mon, Wed & Fri - I write threads on
Java, Javascript & Fullstack Development.
To read all my future threads follow @vikasrajputin
Any Query or Suggestions?
Put them in the comments below!
Happy Friday :)
Namaste, I'm Vikas!
Every Mon, Wed & Fri - I write threads on
Java, Javascript & Fullstack Development.
To read all my future threads follow @vikasrajputin
Any Query or Suggestions?
Put them in the comments below!
Happy Friday :)
** Correction **
Don't know why I've not paid attention to my Join Diagram.
Thanks to @mcmanamynf for pointing it out.
Sharp readers like him, make me feel good about my content and my readers.
Below is the correct join diagram:
Don't know why I've not paid attention to my Join Diagram.
Thanks to @mcmanamynf for pointing it out.
Sharp readers like him, make me feel good about my content and my readers.
Below is the correct join diagram:
جاري تحميل الاقتراحات...