Inner Join in SQL: How It Works

Inner Join in SQL

What is an Inner Join?

An Inner Join merges rows from two or more tables based on a matching column.The result includes only rows where there is a match in all tables involved. If a row in one table doesn’t have a corresponding row in another, it’s excluded from the final result. Useful for showing data with established relationships, like orders linked to customers.

Why Use Inner Join?

• Retrieve Related Data: Combine information from different tables where a relationship exists (e.g., customers and their orders).
• Filter Out Unmatched Data: Returns only rows where there is a match, filtering out unrelated data.
• Combine Multiple Tables:Use SQL joins 3 tables with more than two tables for complex queries.

Read More: Different Types of SQL Joins

Inner Join Query Syntax

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

Syntax Explanation

• SELECT:SELECT specifies the columns you want to retrieve.
• FROM:It Indicates the primary table.
• INNER JOIN:It Combines rows where the common column matches.
• ON:It Defines the condition between the tables using a common column.

Example Explained

Let’s take two tables: Customers and Orders.

• The Customers table contains:
  • CustomerID
  • CustomerName
• The Orders table contains:
  • OrderID
  • CustomerID
  • Product
• To show which customer bought which product using an inner join in SQL example:

• 
  SELECT Customers.CustomerName, Orders.Product
  FROM Customers
  INNER JOIN Orders
  ON Customers.CustomerID = Orders.CustomerID;
  

Understanding the Result Set

• The Inner Join query returns only the rows that meet the condition specified in the ON clause.
• If there are rows in either table that don’t have matching rows in the other, they won’t appear in the result.

Example

Data Example:

• Run the Inner Join query:

  
  SELECT Customers.CustomerName, Orders.Product
  FROM Customers
  INNER JOIN Orders
  ON Customers.CustomerID = Orders.CustomerID;
  

Output:

• Aman and the Tablet order are excluded due to the fact that they do not match.

Join Two Tables With a Matching Field

Joining two tables using a matching field is a fundamental concept in SQL that allows you to retrieve related data from different tables based on a common column.

In this scenario, the matching field serves as the basis for the join operation, ensuring that only related records are combined in the result set. This approach is particularly useful when you want to display information that is spread across multiple tables.

Example Explained

Let's consider two tables: Employees and Departments.

• The Employees table contains:
  • EmployeeID
  • EmployeeName
  • DepartmentID
• The Departments table contains:
  • DepartmentID
  • DepartmentName

To show which department each employee belongs to, you can perform an inner join on the DepartmentID field:

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

Understanding the Result Set

This query will return a list of employees along with their respective department names, effectively linking the two tables through the matching DepartmentID field.

Data Example:

Result of the Inner Join:

Inner Join with Multiple Tables

• You can combine more than two tables using Inner Joins.
• For example, adding another Products table with product details:

  Products Table:

  ProductID	ProductName
  201	Laptop
  202	Smartphone
  203	Tablet

• Query to show customer, order, and product details:

  
  SELECT Customers.CustomerName, Orders.Product, Products.ProductName
  FROM Customers
  INNER JOIN Orders
  ON Customers.CustomerID = Orders.CustomerID
  INNER JOIN Products
  ON Orders.Product = Products.ProductID;

Inner Join with WHERE Clause

While Inner Joins typically specify conditions using the ON clause, you can further refine your results by using a WHERE clause. This allows you to filter the combined dataset based on additional criteria.

For example, if you want to retrieve only customers who have placed orders for a specific product, you can add a WHERE condition to your query. Here’s how it works:

SELECT Customers.CustomerName, Orders.Product
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.Product = 'Smartphone';

This query retrieves the names of customers who ordered the Smartphone. The WHERE clause effectively narrows down the result set to meet your specific requirements, demonstrating how Inner Joins in SQL can be combined with conditional filters for more precise data retrieval.

Example: Retrieve Customers Who Ordered a Specific Product

SELECT Customers.CustomerName, Orders.Product
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.Product = 'Smartphone';

Take our Sqlserver skill challenge to evaluate yourself!

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET FREE CHALLENGE