INNER JOIN in SQL

INNER JOIN in SQL

3 mins read4.7K Views Comment
clickHere
Vikram
Vikram Singh
Assistant Manager - Content
Updated on Oct 3, 2023 11:32 IST

SQL JOINS are used to combine more than two or more tables together to extract the useful data from all the tables. In this article, we will discuss INNER JOIN in SQL.

2022_07_MicrosoftTeams-image-231.jpg

When you have multiple interrelated tables and want to extract the data from more than two or more tables then SQL JOINS comes into the picture that combines the records on the basis of a common column between the tables. 

There are mainly four types of Joins in SQL

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN 

In this article, we will discuss SQL INNER JOIN.

Table of Content

What is SQL INNER JOIN?

SQL INNER JOIN combines the table based on the common columns and selects the records that have matching values in these columns.

It is similar to the intersection of the sets in Mathematics. i.e. when you take the intersection of two or more sets only the common element (in all the sets) are taken together.

Here, we have two tables: The student table (that contains the Student ID and name of the student) and the Department table (that contains the student id and department name). Since, both the tables have Student ID as a common column so we can take intersections of both the tables ( or can perform an Inner Join on both the tables).

Must Read: Introduction to SQL

Must Read: SQL ACID Properties

Syntax

 
SELECT table_1.column_name1, table_2.column_name1, ......
FROM table_1
INNER JOIN table_2
ON table_1.matching_column_name = table_2.matching_column_name;
Copy code

Now let’s take some examples to get a better understanding of SQL INNER JOIN.

Let us have two tables Student, department, and faculty table, that contain the record:

StudentID Name E-mail Percentage(%) DepartmentID
1001 Ajay ajay@xyz.com 85 1
1002 Babloo babloo@xyz.com 67 2
1003 Chhavi chhavi@xyz.com 89 3
1004 Dheeraj dheeraj@xyz.com 75
1005 Evina evina@xyz.com 91 1
1006 Krishna krishna@xyz.com 99 5
Student Record
Department ID Department Name
1 Mathematics
2 Physics
3 English
Department Record
Department ID Faculty Name
1 Piyush
2 Namita
3 Ashneer
4 Ghazal
5 Anupam
Faculty Record

Must Read: What is the difference between SQL and MySQL?

Must Read: Difference between SQL and NoSQL

Example -1: Join the Student and Department table to extract the record from both the table where Student.DepartmentID = Department.DepartmentID.

Query

 
SELECT Student.StudentID, Student.Name, Student.Email, Student.Percentage, Department.DepartmentName
FROM Student
INNER JOIN Department
ON Student.DepartmentID = Department.DepartmentID
Copy code

Output

StudentID Name E-mail Percentage(%) DepartmentName
1001 Ajay ajay@xyz.com 85 Mathematics
1002 Babloo babloo@xyz.com 67 Physics
1003 Chhavi chhavi@xyz.com 89 English
1005 Evina evina@xyz.com 91 Mathematics

The above query fetches only records from both the table where the Department Id in Student table matches with the Department Id in Department table. If the Department id is NULL or not matching it wouldn’t retrieve the record.

Note: It is not necessary to use the INNER JOIN clause we can also use the WHERE clause to get the same result

 
SELECT Student.StudentID, Student.Name, Student.Email, Student.Percentage, Department.DepartmentName
FROM Student, Department
WHERE Student.DepartmentID = Department.DepartmentID;
Copy code

Must Read: Subqueries in SQL

Must Read: SQL CREATE TABLE

SQL INNER JOIN with three tables

From the above example, we get to know how to join two tables. Similarly, we can join three tables also.

Syntax:

 
SELECT table_1.column_name1, table_2.column_name1, table_3.column_name1, ......
FROM table_1
INNER JOIN table_2
ON table_1.matching_column_name = table_2.matching_column_name
INNER JOIN table_3
ON table_2.matching_column_name = table_3..matching_column_name;
Copy code

Must Read: SQL WHERE

Must Read: Introduction to Normalization

Example – 2:  Join Student, Department and Faculty table using inner join.

Query

 
SELECT st.StudentID, st.Name, st.Email, st.Percentage, dt.DepartmentName, ft.FacultyName
FROM Student st
INNER JOIN Department dt
ON st.DepartmentID = dt.DepartmentID
INNER JOIN Faculty ft
ON dt.DepartmentID = ft.DepartmentID
Copy code

Output

StudentID Name E-mail Percentage(%) DepartmentName Faculty Name
1001 Ajay ajay@xyz.com 85 Mathematics Piyush
1002 Babloo babloo@xyz.com 67 Physics Namita
1003 Chhavi chhavi@xyz.com 89 English Ashneer
1005 Evina evina@xyz.com 91 Mathematics Piyush

Must Read: SQL LIMITS

Must Check: SQL Online Course and Certifications

INNER JOIN with WHERE and ORDER BY clause

Example:  Join Student, Department and Faculty table using inner join where the percentage is greater than or equal to 85 and sort them in descending order.

Query

 
SELECT st.StudentID, st.Name, st.Email, st.Percentage, dt.DepartmentName, ft.FacultyName
FROM Student st
INNER JOIN Department dt
ON st.DepartmentID = dt.DepartmentID
INNER JOIN Faculty ft
ON dt.DepartmentID = ft.DepartmentID
WHERE st.Percentage >= 85
ORDER BY st.Percentage DESC;
Copy code

Must Read: SQL Comparison and Arithmetic Operator

Must Read: SQL Logical Operator

Conclusion

In this article, we have discussed Inner join in sql, how to join three or more tables and how we can use inner join with WHERE and WHERE.

Hope this article, will help you in your dats science/data analysis journey.
Keep Learning!!
Keep Sharing!!

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