INNER JOIN - Select Records with Matching Values in Both Tables in SQL

The INNER JOIN clause in SQL is used to select records that have matching values in both tables involved in the join. It is one of the most common types of joins and is used to combine rows from two or more tables based on a related column between them. When using an INNER JOIN, only the rows where there is a match in both tables will be included in the result set.

1. Syntax of INNER JOIN

The basic syntax of an INNER JOIN is as follows:

      SELECT column1, column2, ...
      FROM table1
      INNER JOIN table2
      ON table1.column = table2.column;
    

In this syntax:

• table1 and table2 are the names of the tables to be joined.
• column1, column2 are the columns you want to retrieve from the tables.
• table1.column and table2.column are the columns from each table that are being compared for a match.

2. Example of INNER JOIN

Suppose you have two tables: Employees and Departments. The Employees table contains employee details, and the Departments table contains department details. You want to select the employees along with their department names. You can use an INNER JOIN to retrieve only the employees who belong to a department:

      SELECT Employees.EmployeeID, Employees.Name, Departments.DepartmentName
      FROM Employees
      INNER JOIN Departments
      ON Employees.DepartmentID = Departments.DepartmentID;
    

This query joins the Employees table with the Departments table where the DepartmentID values match. The result set will include the employee ID, name, and their respective department name for each employee that belongs to a department.

3. Example of INNER JOIN with Multiple Tables

It is possible to join more than two tables using an INNER JOIN. Suppose you also have an Orders table that records the sales made by employees. You want to retrieve a list of employees along with their department names and the total sales they made. You can join the three tables as follows:

      SELECT Employees.Name, Departments.DepartmentName, SUM(Orders.SalesAmount) AS TotalSales
      FROM Employees
      INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID
      INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID
      GROUP BY Employees.Name, Departments.DepartmentName;
    

This query performs an INNER JOIN on all three tables: Employees, Departments, and Orders. It retrieves the employee name, department name, and the total sales made by each employee. The GROUP BY clause groups the result by employee name and department name, while the SUM function calculates the total sales per employee.

4. How INNER JOIN Works

When using an INNER JOIN, the SQL engine compares the values of the specified columns from both tables. If a match is found, the corresponding rows from both tables are included in the result set. If there is no match, the rows are excluded.

For example, if the Employees table has 5 employees, but only 3 of them belong to a department listed in the Departments table, only those 3 employees will be returned by the query. The employees without a matching department will not appear in the result set.

5. INNER JOIN with Aliases

When working with multiple tables, it is common to use table aliases to make the query more concise and easier to read. Here is an example of using aliases with an INNER JOIN:

      SELECT e.EmployeeID, e.Name, d.DepartmentName
      FROM Employees AS e
      INNER JOIN Departments AS d
      ON e.DepartmentID = d.DepartmentID;
    

In this example, e is used as an alias for the Employees table, and d is used as an alias for the Departments table. This allows you to refer to the tables using shorter names in the query.

6. INNER JOIN with Conditions

You can also apply additional filtering conditions using the WHERE clause in combination with an INNER JOIN. For example, if you want to list only those employees who have made sales greater than $500:

      SELECT Employees.Name, Departments.DepartmentName, SUM(Orders.SalesAmount) AS TotalSales
      FROM Employees
      INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID
      INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID
      WHERE Orders.SalesAmount > 500
      GROUP BY Employees.Name, Departments.DepartmentName;
    

This query filters the sales data to only include orders where the SalesAmount is greater than $500.

7. Key Points to Remember About INNER JOIN

• The INNER JOIN clause returns only the rows where there is a match in both tables based on the specified condition.
• If there is no matching row in one of the tables, the result will exclude that row.
• You can join more than two tables using multiple INNER JOIN clauses.
• Table aliases make queries more concise and easier to understand, especially when joining multiple tables.

8. Conclusion

The INNER JOIN clause is an essential tool for combining data from multiple tables in SQL. By using INNER JOIN, you can retrieve records where there is a match in both tables, allowing you to link related data together. Whether you are working with two tables or multiple tables, INNER JOIN helps you create meaningful result sets by combining data based on common columns.