SQL Joins: Inner, Left, Right and Full
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.
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.
Best-suited Database and SQL courses for you
Learn Database and SQL with these high-rated online courses
Types of Joins in SQL
Following are the four types of joins that you can use:
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL JOIN
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 TableName1INNER JOIN TableName2ON TableName1.MatchingColumnName = TableName2.MatchingColumnName;
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.MedicineNameFROM PatientsINNER JOIN Medicines ON Patients.PatientID=Medicines.PatientID;
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.
Syntax of SQL LEFT JOIN:
SELECT TableName1.ColumnName1,TableName2.ColumnName1,....FROM TableName1LEFT JOIN TableName2ON TableName1.MatchingColumnName = TableName2.MatchingColumnName;
Example :
SELECT Patients.PatientName, Patients.Age, Medicines.MedicineID, Medicines.MedicineNameFROM PatientsLEFT JOIN Medicines ON Patients.PatientID=Medicines.PatientID;
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.
Synta of RIGHT JOIN:
SELECT TableName1.ColumnName1,TableName2.ColumnName1,....FROM TableName1RIGHT JOIN TableName2ON TableName1.MatchingColumnName = TableName2.MatchingColumnName;
Example :
SELECT Patients.PatientName, Patients.Age, Medicines.MedicineID, Medicines.MedicineNameFROM PatientsRIGHT JOIN Medicines ON Patients.PatientID=Medicines.PatientID;
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.
Syntax of FULL JOIN:
SELECT TableName1.ColumnName1,TableName2.ColumnName1,....FROM TableName1FULL JOIN TableName2ON TableName1.MatchingColumnName = TableName2.MatchingColumnName;
Example:
SELECT Patients.PatientID, Patients.PatientName, Medicines.MedicineID, Medicines.MedicineNameFROM PatientsFULL JOIN Medicines ON Patients.PatientID=Medicines.PatientID;
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.
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
Top Picks & New Arrivals
Comments
(1)
V
10 months ago
Report Abuse
Reply to Venkat Raju
V
Vikram SinghAssistant Manager - Content
Report Abuse