SQL Joins: Inner, Left, Right and Full

SQL Joins: Inner, Left, Right and Full

7 mins read792 Views 1 Comment
clickHere
Vikram
Vikram Singh
Assistant Manager - Content
Updated on Jan 16, 2024 16:01 IST

Understanding SQL Joins is fundamental when it comes to database management and data analysis. This article explores the four primary types of SQL Joins: Left, Right, Inner, and Full. Each type uniquely combines rows from two or more tables based on a related column. By the end of this article, you'll have a solid understanding of how these joins work, when to use them, and how they impact data manipulation and querying. Whether you're a beginner or an experienced SQL user looking to refine your skills, this guide is valuable for exploring SQL Joins.

sql join : left, right, full and inner

Table of Content

What are SQL Joins?

SQL Joins are a fundamental concept in SQL (Structured Query Language), used to combine rows from two or more tables based on a related column between them. The primary purpose of joins is to query data from multiple tables to create a set of results that can be used for analysis or reporting. 
In simple terms, joins are the backbone of combining data from multiple tables in SQL. They act like bridges, connecting related information scattered across different tables based on shared fields. Essentially, joins let you retrieve data from more than one table in a single query, presenting a unified view of your information.

There are 4 different types of joins that you can use. Refer below.

Types of Joins in SQL

Following are the four types of joins that you can use:

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN
100+ SQL Interview Questions and Answers for 2023
100+ SQL Interview Questions and Answers for 2023
Here’s the list of top SQL interview questions. Apart from guiding you in your interviews, the detailed answers provided in this article will give you a basic understanding of different...read more

Now, in the next section, we will understand how these joins work with the help of examples. For that, we will take three tables: Patients Table (patient_id, patient_name, sex, age, phone_number, state, country, haemoglobin), Medicines Table (medicine_id, medicine_name, patient_id, doctor_id, price), Doctors Table (doctor_id, doctor_name, phone_number, email_id patient_id).

Patients Table

Patient ID Patient Name Sex Age Phone Number State Country Haemoglobin
1 Sheela F 23 9876543210 Telangana India 82.321
2 Rehan M 21 7429061312 Karnataka India 83.231
3 Anay M 56 7382221121 Haryana India 94.567
4 Mahira F 42 9318281092 Gujarat India 78.567
5 Nishant M 12 8209109311 Maharashtra India 65.234

Medicines Table

MedicineID Medicine Name PatientID DoctorID Price
M01 MeftalForte 1 4 500
M02 Dolo 2 2 670
M03 Wikoryl 3 1 980
M04 PanD 1 4 420
M05 Uprise 4 3 930
M06 Boroline 6 3 470
M07 Volini 6 1 820
M08 Vicks 4 2 640
M09 Cetaphil Day Cream 3 3 230
M10 MeftalSpas 5 5 850

Doctors Table

Doctor ID Doctor Name Phone Number EmailID PatientID
01 Dr.Manoj 7626313013 manoj@gmail.com 2
02 Dr.Dipika 8654345678 dipika@gmail.com 1
03 Dr.Sana 9456786543 sana@gmail.com 4
04 Dr.Paras 8423231133 paras@gmail.com 3
05 Dr. Suhail 6789056798 suhail@gmail.com 4

Let's start with the Inner Join

Inner Join

This is the most common type of join. INNER JOIN returns rows when at least one match exists in both tables. If there is no match, the rows are not returned.
Think of it like merging two tables perfectly based on identical keys.

Syntax of Inner Join


 
SELECT TableName1.ColumnName1,TableName2.ColumnName1,....
FROM TableName1
INNER JOIN TableName2
ON TableName1.MatchingColumnName = TableName2.MatchingColumnName;
Copy code
Problem Statement: Fetch the record of patient_id, patient_name, patient_age, medicine_id and medicine name using medicine and patient table.
Answer: Since both the tables (patient and medicine) have a common column patient_id, so we will join both the tables on the patient_id column.

 
SELECT Patients.PatientID, Patients.PatientName, Patients.Age, Medicines.MedicineID, Medicines.MedicineName
FROM Patients
INNER JOIN Medicines ON Patients.PatientID=Medicines.PatientID;
Copy code

Output:

Patient ID Patient Name Age MedicineID MedicineName
1 Sheela 23 M01 MeftalForte
2 Rehan 21 M02 Dolo
3 Anay 56 M03 Wikoryl
1 Sheela 23 M04 PanD
4 Mahira 42 M05 Uprise
4 Mahira 42 M08 Vicks
3 Anay 56 M09 Cetaphil Day Cream
5 Nishant 12 M10 MeftalSpas

Still, Have Doubts on Inner Join? Check our blog, INNER JOIN IN SQL, to master the concept of inner joins.

SQL LEFT JOIN

Left join returns all the records from the left table and only the records that match the condition from the right table. Here, please note that for all the records which do not have matching values in the right table, NULL values will be returned. 

2022_05_LEFT-JOIN.jpg

Syntax of SQL LEFT JOIN:


 
SELECT TableName1.ColumnName1,TableName2.ColumnName1,....
FROM TableName1
LEFT JOIN TableName2
ON TableName1.MatchingColumnName = TableName2.MatchingColumnName;
Copy code

Example :


 
SELECT Patients.PatientName, Patients.Age, Medicines.MedicineID, Medicines.MedicineName
FROM Patients
LEFT JOIN Medicines ON Patients.PatientID=Medicines.PatientID;
Copy code

Output:

Patient Name Age MedicineID MedicineName
Sheela 23 M01 MeftalForte
Rehan 21 M02 Dolo
Anay 56 M03 Wikoryl
Sheela 23 M04 PanD
Mahira 42 M05 Uprise
Mahira 42 M08 Vicks
Anay 56 M09 Cetaphil Day Cream
Nishant 12 M10 MeftalSpas

Are you still confused with the right join in SQL? Explore our article, LEFT JOIN in SQL.

Right Join

Right, join returns all the records from the right table and only those that match the condition from the left table. Here, please note that for all the records which do not have matching values in the left table, NULL values will be returned. 

2022_05_RIGHT-JOIN.jpg
Synta of RIGHT JOIN:

 
SELECT TableName1.ColumnName1,TableName2.ColumnName1,....
FROM TableName1
RIGHT JOIN TableName2
ON TableName1.MatchingColumnName = TableName2.MatchingColumnName;
Copy code

Example :


 
SELECT Patients.PatientName, Patients.Age, Medicines.MedicineID, Medicines.MedicineName
FROM Patients
RIGHT JOIN Medicines ON Patients.PatientID=Medicines.PatientID;
Copy code

Output:

Patient Name Age MedicineID MedicineName
Sheela 23 M01 MeftalForte
Rehan 21 M02 Dolo
Anay 56 M03 Wikoryl
Sheela 23 M04 PanD
Mahira 42 M05 Uprise
NULL NULL M06 Boroline
NULL NULL M07 Volini
Mahira 42 M08 Vicks
Anay 56 M09 Cetaphil Day Cream
Nishant 12 M10 MeftalSpas

SQL Full Join

Full join returns the records that either have matches from one of the tables. 

2022_05_FULL-JOIN.jpg

Syntax of FULL JOIN:


 
SELECT TableName1.ColumnName1,TableName2.ColumnName1,....
FROM TableName1
FULL JOIN TableName2
ON TableName1.MatchingColumnName = TableName2.MatchingColumnName;
Copy code
Example:

 
SELECT Patients.PatientID, Patients.PatientName, Medicines.MedicineID, Medicines.MedicineName
FROM Patients
FULL JOIN Medicines ON Patients.PatientID=Medicines.PatientID;
Copy code

Output:

Patient ID Patient Name MedicineID MedicineName
1 Sheela M01 MeftalForte
2 Rehan M02 Dolo
3 Anay M03 Wikoryl
1 Sheela M04 PanD
4 Mahira M05 Uprise
6 NULL M06 Boroline
6 NULL M07 Volini
4 Mahira M08 Vicks
3 Anay M09 Cetaphil Day Cream
5 Nishant M10 MeftalSpas

Conclusion

Mastering Left, Right, Inner, and Full join in SQL is crucial for anyone working with databases. These joins are more than just commands; they are powerful tools that enable you to view and analyze data in multifaceted ways. By understanding the nuances of each join type, you can make informed decisions about data retrieval and manipulation, enhancing the efficiency and accuracy of your queries.

Keep practicing with real-world examples, and soon, these joins will become an integral part of your SQL toolkit, paving the way for advanced data analysis and robust database management. In conclusion, mastering these join types is an essential skill for anyone working with databases, and it will undoubtedly help improve your data analysis abilities.

FAQs on What is SQL Joins: Left, Right, Inner, and Full Join

What is a Full/Outer Join and when is it used?

A Full Join, also known as a Full Outer Join, retrieves all records from both tables, including both matching and non-matching records. It's used when you need a comprehensive combination of data from both tables without losing any information.

How does a Right Join work in SQL?

A Right Join, or Right Outer Join, returns every record from the right table and the matching records from the left table. Non-matching records in the left table are represented as NULL in the result set.

What is a Left Join and how is it different from an Inner Join?

A Left Join returns every row from the left table and the matched records from the right table. Unlike an Inner Join, which only returns matching rows from both tables, a Left Join includes all records from the left table, even if there are no matching rows in the right table.

Can you provide an example of a Full Outer Join?

Sure, consider two tables, 'movies' and 'directors'. A Full Outer Join between these tables can be performed using FULL OUTER JOIN directors ON directors.id = movies.director_id. This will return all movies with their directors, including movies without directors and directors without movies.

What are some tips for optimizing JOIN performance in SQL?

To optimize JOIN performance, consider using appropriate join types for your needs, ensure indexing on key columns, and consider the size of the tables being joined. For large data sets, using hash joins can be efficient.

How do I use a Right Outer Join in SQL?

A Right Outer Join adds all rows from the right-hand table to the output and only matching rows from the left-hand table. It is useful when you want all records from the right-hand table and only the matching ones from the left-hand table.

What is the difference between Left and Right Joins?

The main difference lies in which table's records are included completely. A Left Join includes all records from the left table, while a Right Join includes all records from the right table. The non-matching rows from the opposite table are represented as NULL.

When should I use an INNER JOIN?

Use an INNER JOIN when you need to retrieve rows that have matching values in both tables involved in the join.

Can you explain the basic concept of SQL Joins?

SQL Joins are used to combine rows from two or more tables based on a related column between them. They are essential for querying data from multiple tables and understanding the relationships between different sets of data.

About the Author
author-image
Vikram Singh
Assistant Manager - Content

Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio

Comments

(1)

In the full join the syntax given is wrong, instead of full join mentioned as Inner join pls check.

Reply to Venkat Raju

V

Vikram SinghAssistant Manager - Content

Thank you, Raju, for notifying me. It's updated now.