Mastering SQL Inner Join: A Comprehensive Guide With Practical Examples

SQL (Structured Query Language) is the standard language for managing relational databases. It is a powerful tool that allows us to store, manipulate, and retrieve data efficiently. One of the most commonly used operations in SQL is the JOIN operation, which allows us to combine data from two or more tables into a single result set.

The INNER JOIN clause is one of the most frequently used types of JOIN in SQL. It combines rows from two or more tables based on a related column between them. The INNER JOIN returns only the rows that have matching values in both tables, i.e., the intersection of the two tables. 

The following picture illustrates the Venn diagram of INNER JOIN. The shaded area in the diagram represents the common values shared between the two tables.

In this article, We will discuss the SQL INNER JOIN clause in detail with multiple examples.

SQL INNER JOIN syntax

The basic syntax of the SQL Inner Join clause is as follows:

SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

In the above syntax, the SELECT statement specifies the columns we want to retrieve from the tables. The FROM statement is used to specify the tables from which we want to retrieve data.
The INNER JOIN clause combines the rows from the specified tables based on the matching column(s) between them.

Suppose we have two tables:

•  Employees
•  Departments

The Employees table contains the following columns: ‘EmployeeID’, ‘FirstName’, ‘LastName’, and ‘DepartmentID’. The Departments table contains the following columns: ‘DepartmentID’, ‘DepartmentName’.

Employees Table:

Departments Table:

The below query joins two tables: Employees and Departments, using the INNER JOIN clause. The ON clause ensures that only the rows with matching DepartmentIDs in both tables will be included in the result set.

The SELECT statement selects three columns: FirstName and LastName from the Employees table and DepartmentName from the Departments table.

SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Result:

SQL INNER JOIN With WHERE Clause

Suppose we want to retrieve the names of all employees who work in the ‘Sales’ department. We can use the following query:

SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID
WHERE Departments.DepartmentName = 'Sales';

The above query joins the Employees and Departments tables using the INNER JOIN clause on the ‘DepartmentID’ column. It then uses the WHERE clause to filter the results to include only those rows where the ‘DepartmentName’ is ‘Sales’.

The output of this query will be:

INNER JOIN With GROUP BY Clause

Suppose we want to retrieve the total number of employees in each department. We can use the following query:

SELECT Departments.DepartmentName, COUNT(*) as TotalEmployees
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID
GROUP BY Departments.DepartmentName;

The above query joins the Employees and Departments tables using the INNER JOIN clause on the DepartmentID column. It then uses the GROUP BY clause to group the results by the DepartmentName column. Finally, it uses the COUNT(*) function to count the total number of employees in each department.

The output of this query will be:

Using SQL INNER JOIN With 3 Tables

Suppose we have three tables: Students, Courses, and Enrollments. 

The Students table contains information about students, including their student IDs and names. 

The Courses table contains course information, including the course IDs and names. 

The Enrollments table contains information about which students are enrolled in which courses, including the student IDs and course IDs.

Let’s first create these tables:

Students:

Courses:

Enrollments:

To retrieve a list of all students and the courses they are enrolled in, we can use the following query:

SELECT Students.StudentID, Students.Name, Courses.CourseName
FROM Students
INNER JOIN Enrollments ON Students.StudentID = Enrollments.StudentID
INNER JOIN Courses ON Enrollments.CourseID = Courses.CourseID;

The output of the above query will be:

This table shows all the students and the courses they are enrolled in. For example, Krishna is enrolled in Math and Science, Sweta is in Science and History, and Charlie is in Math and History.

Using Aliases with SQL INNER JOIN

Aliases provide a way to give a table or column a different name to make the SQL statement easier to read and understand. Here is an example of using aliases with SQL INNER JOIN:

SELECT emp.FirstName, emp.LastName, dep.DepartmentName
FROM Employees AS emp
INNER JOIN Departments AS dep ON emp.DepartmentID = dep.DepartmentID;

Using Subqueries with SQL INNER JOIN 

A subquery is a query that is nested inside another query. We can use SQL INNER JOIN with subqueries to perform more complex data. 

Here is an example of using a subquery with SQL INNER JOIN:

SELECT emp.FirstName, emp.LastName, dep.DepartmentName
FROM Employees AS emp
INNER JOIN (SELECT * FROM Departments WHERE DepartmentID IN (1, 2, 3)) AS dep ON emp.DepartmentID = dep.DepartmentID;

This query selects only the employees who work in the departments with IDs 1, 2, or 3.

Using UNION with SQL INNER JOIN 

UNION combines the result sets of two or more SELECT statements into a single result set. SQL INNER JOIN can be used with UNION to combine data from multiple tables. 

Here is an example of using UNION with SQL INNER JOIN:

SELECT FirstName, LastName, DepartmentName FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID
UNION
SELECT FirstName, LastName, 'No Department' AS DepartmentName FROM Employees
WHERE DepartmentID IS NULL;

This query selects all employees and their department names and includes employees who are not assigned to any department with a default “No Department” value.

Using CROSS JOIN with SQL INNER JOIN

CROSS JOIN returns the Cartesian product of two tables, which returns all possible combinations of rows from both tables. SQL INNER JOIN can be used with CROSS JOIN to join all rows from one table to all rows in another table. 

Here is an example of using CROSS JOIN with SQL INNER JOIN:

SELECT emp.FirstName, emp.LastName, dep.DepartmentName, ord.OrderDate
FROM Employees AS emp
CROSS JOIN Departments AS dep
INNER JOIN Orders AS ord ON emp.EmployeeID = ord.EmployeeID AND dep.DepartmentID = ord.DepartmentID;

FAQs

You might want to read this too:

• Types of Joins in SQL Server
• Having VS Where Clause in SQL
• SQL EXISTS – Exploring EXISTS Operator in SQL Server
• Like operator in SQL
• Different ways to delete duplicate rows in SQL Server
• COALESCE in SQL With Examples and Use Cases for Handling NULL Values
• SQL Server CONVERT Function: How to Convert Data Types in SQL Server
• Delete vs Truncate vs Drop in SQL Server
• SQL Server TRY_CAST() Function – Understanding TRY_CAST() in SQL with [Examples]
• SQL Server Try Catch: Error Handling in SQL Server With [Examples]
• Triggers in SQL Server
• Function vs Stored procedure in SQL Server
• UNION vs UNION ALL in SQL SERVER
• View In SQL | Types Of Views In SQL Server

• Author
• Recent Posts

Shekh Ali

Meet Sheikh Ali, a skilled Software engineer and passionate a blogger. He enjoys sharing his expertise in C#, Java, SQL, Design Patterns, and other related topics through his writing. When he's not coding, Sheikh Ali can often indulge in his passions for reading books and fitness training.

Latest posts by Shekh Ali (see all)

• Difference Between Array And ArrayList In C#: Choosing the Right Collection - May 28, 2024
• C# Program to Capitalize the First Character of Each Word in a String - February 21, 2024
• C# Program to Find the Longest Word in a String - February 19, 2024