INNER JOIN in SQL
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.
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
Best-suited Database and SQL courses for you
Learn Database and SQL with these high-rated online courses
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).
SELECT table_1.column_name1, table_2.column_name1, ...... FROM table_1INNER JOIN table_2 ON table_1.matching_column_name = table_2.matching_column_name;
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 | 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 |
| Department ID | Department Name |
| 1 | Mathematics |
| 2 | Physics |
| 3 | English |
| Department ID | Faculty Name |
| 1 | Piyush |
| 2 | Namita |
| 3 | Ashneer |
| 4 | Ghazal |
| 5 | Anupam |
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.DepartmentNameFROM StudentINNER JOIN DepartmentON Student.DepartmentID = Department.DepartmentID
Output
| StudentID | Name | 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.DepartmentNameFROM Student, DepartmentWHERE Student.DepartmentID = Department.DepartmentID;
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_1INNER JOIN table_2 ON table_1.matching_column_name = table_2.matching_column_nameINNER JOIN table_3ON table_2.matching_column_name = table_3..matching_column_name;
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.FacultyNameFROM Student stINNER JOIN Department dtON st.DepartmentID = dt.DepartmentIDINNER JOIN Faculty ftON dt.DepartmentID = ft.DepartmentID
Output
| StudentID | Name | 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.FacultyNameFROM Student stINNER JOIN Department dtON st.DepartmentID = dt.DepartmentIDINNER JOIN Faculty ftON dt.DepartmentID = ft.DepartmentIDWHERE st.Percentage >= 85ORDER BY st.Percentage DESC;
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!!
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
