Step-by-Step Guide to INNER JOIN in SQL Server: Syntax and Real-world Example

An INNER JOIN is a type of SQL join that combines rows from two or more tables based on a related column between them. The INNER JOIN retrieves only the rows that have matching values in both tables. In other words, it returns the intersection of the two tables.

Let's break down the components and explain how the INNER JOIN works:

1. Syntax:

   Syntax to perform inner join on a column

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

2. table1 and table2: These are the names of the two tables that you want to join.

3. column1, column2, ...: These are the columns you want to retrieve from the result set. You can specify which columns you want to select from the joined tables.

4. ON clause: The ON clause specifies the condition for joining the two tables. It defines the relationship between the tables by specifying which columns to compare.

5. Result: The result of an INNER JOIN includes only the rows for which the value of the specified column in table1 matches the value of the specified column in table2.

Sample Data to Demo Inner Join

You can use this DB Fiddle to follow along and practice inner join

Let's consider two tables, "Customers" and "Orders," with the following data:

Customers table:

CustomerID	CustomerName	Email
1	John Doe	john.doe@example.com
2	Jane Smith	jane.smith@example.com
3	Michael Johnson	michael.johnson@example.com
4	Emily Brown	emily.brown@example.com

Orders table:

OrderID	CustomerID	OrderDate	TotalAmount
101	1	2023-08-06 10:00:00.000	100.00
102	2	2023-08-06 11:30:00.000	50.00
103	1	2023-08-06 12:15:00.000	75.00
104	3	2023-08-06 14:20:00.000	200.00

Inner Join Customers and Orders Tables on CustomerID Column

To retrieve customer information along with their orders, we can use an INNER JOIN as follows:

Perform inner join on CustomerID column

SELECT Customers.CustomerID
	,Customers.CustomerName
	,Orders.OrderID
	,Orders.OrderDate
	,Orders.TotalAmount
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

The result of this INNER JOIN would be:

CustomerID	CustomerName	OrderID	OrderDate	TotalAmount
1	John Doe	101	2023-08-06 10:00:00.000	100.00
2	Jane Smith	102	2023-08-06 11:30:00.000	50.00
1	John Doe	103	2023-08-06 12:15:00.000	75.00
3	Michael Johnson	104	2023-08-06 14:20:00.000	200.00

As you can see, the INNER JOIN only includes the rows where the CustomerID is common between the "Customers" and "Orders" tables, resulting in a combined result set with customer information and their corresponding orders. Customers (Emily Brown in this example data set) with no matching orders or orders with no matching customers are excluded from the result.