The SQL Server INNER JOIN combines two or more tables and returns the records (or rows) present in those tables if there is at least one match between columns. The INNER JOIN uses the common column/field to create a JOIN between the two tables. Alternatively, we can say that an INNER JOIN returns the records (or rows) present in both tables, as long as the Condition after the ON Keyword is true.
The primary focus of the SQL INNER JOIN is to filter out the non-matching records from the result set. So, it is very helpful to see the products with sales or countries that do business, etc.
The SQL Server INNER JOIN is the default, so it is optional to use the INNER Keyword. In general, it uses the Primary and Foreign key columns to perform the JOIN operation. Let us see the visual representation (Venn diagram) of the INNER JOIN for better understanding.
From the above image, you can easily understand that the Inner join only displays the matching records (intersection space) from Table 1 and Table 2 (Same as the intersection of sets in Mathematics).
SQL INNER JOIN Syntax
The syntax of the INNER JOIN is as shown below.
SELECT Table1.Column(s), Table2.Column(s),
FROM Table1
INNER JOIN Table2
ON Table1.Common_Column = Table2.Common_Column
--OR We can Simply Write it as
SELECT Table1. Column(s), Table2. Column(s),f
FROM Table1
JOIN Table2
ON Table1.Common_Column = Table2.Common_Column
From the above SQL Server INNER JOIN syntax:
- Table1 and Table2 are the two tables that you want to join.
- Columns: Please specify the column name that you want to retrieve from the two tables.
- ON clause: It defines how the match should happen. Here, you must provide the condition to join the two tables. The final INNER JOIN result set returns the rows that match this condition.
- As we mentioned in the syntax, you can use either INNER JOIN or JOIN. They are both the same and return the same result.
- There is an optional WHERE clause, TOP clause, and ORDER BY. If needed, you can use them.
To demonstrate the SQL INNER JOIN, we will create three new tables. Please choose the required database and execute the code below to test the examples in your work environment.
ProductCategory with four records:
CREATE TABLE ProductCategory (
CategoryID INT NOT NULL PRIMARY KEY,
CategoryName VARCHAR(50)
);
INSERT INTO ProductCategory VALUES
(1, 'Bikes'),
(2, 'Components'),
(3, 'Clothing'),
(4, 'Accessories')
Creating a Geography table with three rows.
CREATE TABLE Geography (
GeographyID INT PRIMARY KEY,
Country VARCHAR(50)
);
INSERT INTO Geography VALUES
(1, 'USA'),
(2, 'INDIA'),
(3, NULL);
A fact SQL Server table containing sales and orders information on the products.
CREATE TABLE Fact (
ID INT PRIMARY KEY,
CategoryID INT,
GeographyID INT,
Orders INT NULL,
Sale DECIMAL(10, 2),
OrderDate DATE
FOREIGN KEY (CategoryID) REFERENCES ProductCategory(CategoryID),
FOREIGN KEY (GeographyID) REFERENCES Geography(GeographyID)
);
INSERT INTO Fact VALUES
(301, 1, 1, 15, 7500.00, '2025-09-28'),
(302, 2, 2, 45, 500.00, '2025-04-01'),
(303, 3, NULL, 75, 100.00, '2024-08-15'),
(304, NULL, 1, 10, 4000.00, '2023-07-11'),
(305, 4, 1, 9, 1500.00, '2024-12-31'),
(306,1, 2, 22,1200.00, '2025-09-30'),
(307, 2, 1, 30, 8000.00, '2025-05-01'),
(308, 4, 3, 25, 9000.00, '2025-01-13');
SQL INNER JOIN Example
The INNER JOIN helps combine rows from two tables based on the common columns between them. When the condition inside the ON clause is elevated to TRUE, that particular row will display as the output. For instance, if you have the customers table and their sales table in a database. We can use the INNER JOIN to display the customers who placed at least one order.
To demonstrate the INNER JOIN functionality, we use the ProductCategory and Fact tables. If you observe the table creation, there is a PRIMARY and FOREIGN key relationship between the two tables.
In this example, we use the SQL INNER JOIN with the SELECT * FROM statement to display all the columns from two tables. The following example shows all the matching records from the ProductCategory and Fact tables.
SELECT * FROM ProductCategory
INNER JOIN Fact
ON ProductCategory.CategoryID = Fact.CategoryID
The above query checks whether the CategoryID column in the ProductCategory table has a matching record, i.e., a sale in the Fact table. Here, the join between the two tables occurs based on the Category ID column of both tables. If TRUE, return that record; otherwise, do the same for the remaining ones. Although we successfully joined two tables, there are many unwanted columns in the result set.
For instance, the CategoryID column is repeated twice, and there is an unwanted GeographyID, which is annoying to the user. By selecting individual column names, we can avoid unwanted columns. So, please avoid the SELECT * Statement.
NOTE: The INNER JOIN returns only common rows. If there is a row in ProductCategory without a CategoryID or the Fact table without CategoryID (NULL), those records won’t be displayed in the result set.
SQL INNER JOIN Select a Few Columns
Apart from the above-mentioned unwanted columns, it is a waste of resources if both tables have a larger number of columns. To choose a few required columns and avoid unwanted columns in the INNER JOIN, please place the required ones after the SELECT Statement.
To demonstrate it, we have chosen the category name, orders, sales, and order date from both tables.
SELECT CategoryName, Orders, Sale, OrderDate FROM ProductCategory
INNER JOIN Fact ON
ProductCategory.CategoryID = Fact.CategoryID
SQL INNER JOIN Ambiguous column name Error and Fix
The above-mentioned INNER JOIN query will work perfectly as long as the column names from both tables are different, like above. What happens if we have the same column titles in both tables? Well, you will end up in a mess. Let us see how to resolve the issue.
Before we explain the solution, let me demonstrate the actual problem using one practical example. As you can see, we are using the above query, but we added the CategoryID from the ProductCategory table as an additional column.
SELECT CategoryID, CategoryName, Orders, Sale, OrderDate FROM ProductCategory
INNER JOIN Fact ON
ProductCategory.CategoryID = Fact.CategoryID
You can see that the SQL INNER JOIN is throwing an error: ‘Ambiguous column name CategoryID’. It is because the CategoryID is present in both the ProductCategory and Fact tables. And the Server doesn’t know which one you are asking it to retrieve.
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'CategoryID'.To resolve these kinds of problems, always use the table name before the column name. The following query uses the ALIAS table name before the column names. By this approach, we can inform the server that we are requesting the Category ID belonging to the ProductCategory table.
SELECT c.CategoryID, c.CategoryName, f.Orders, f.Sale, f.OrderDate FROM ProductCategory c
INNER JOIN Fact f ON
c.CategoryID = f.CategoryID
JOIN Vs INNER JOIN
As we mentioned earlier, SQL INNER JOIN is the default one to combine two or more tables, and it is optional to include the INNER keyword. In simple terms, both INNER JOIN and JOIN are the same and return the same results.
For beginners or to understand the code by others, people use the explicit INNER keyword before the JOIN. For instance, the query below returns the same result set as the example above.
SELECT c.CategoryID, c.CategoryName, f.Orders, f.Sale, f.OrderDate FROM ProductCategory c
JOIN Fact f ON
c.CategoryID = f.CategoryID
Sorting the Data
From the above query result set, there is no particular order in displaying the products and their sales. When you use the SQL Server INNER JOIN and ORDER BY Clause, the query will display the records in either ascending or descending order based on the column. Here, the sales by category information is sorted in ascending order based on the CategoryID.
SELECT c.CategoryID, c.CategoryName, f.Orders, f.Sale, f.OrderDate FROM ProductCategory c
INNER JOIN Fact f ON
c.CategoryID = f.CategoryID
ORDER BY c.CategoryID
Handling NULL Values
As we mentioned earlier, the SQL INNER JOIN will return the matching records and ignore the rows with NULL values. For instance, although the Fact table has seven records with sales and order details, the following query returns only six records because one record in the Fact table has a GeographyID value of NULL. So, the INNER JOIN ignores that record. It also happened in the above examples while joining the category and fact tables.
SELECT g.GeographyID, g.Country,
f.Orders, f.Sale, f.OrderDate FROM Geography g
INNER JOIN Fact f ON g.GeographyID = f.GeographyID
ORDER BY g.GeographyID
Using SQL INNER JOIN with GROUP BY Clause
When combining data from two or more tables, calculating the aggregated values is the most common scenario. The INNER JOIN allows using the GROUP BY clause along with the available aggregate functions. With the help of these two, you can combine a measure and Dimension values from two tables and find their average, count, min, etc.
The following two examples give an idea of how to use the GROUP BY clause along with the INNER JOIN.
Sales By Product Category
The query below uses the SQL INNER JOIN to combine the ProductCategory and Fact tables based on the ID column. Next, the GROUP BY clause groups the common values by product category name. Then, the aggregate SUM function calculated the sum of the sales and total orders.
SELECT CategoryName, SUM(Orders) AS TotalOrder, SUM(Sale) AS TotalSales
FROM ProductCategory INNER JOIN Fact
ON ProductCategory.CategoryID = Fact.CategoryID
GROUP BY CategoryName
CategoryName TotalOrder TotalSales
-------------------------------------------------- ----------- ----------
Accessories 9 1500.00
Bikes 37 8700.00
Clothing 75 100.00
Components 75 8500.00Sales by Country
Similar to the above, this query calculates the total sales amount by country.
SELECT Country, SUM(Orders) AS TotalOrder, SUM(Sale) AS TotalSales
FROM Geography INNER JOIN Fact
ON Geography.GeographyID = Fact.GeographyID
GROUP BY Country
Country TotalOrder TotalSales
-------------------------------------------------- ----------- ---------
INDIA 67 1700.00
USA 64 21000.00Joining Three Tables
In all the above-mentioned examples, we use the SQL INNER JOIN to combine data from two tables; however, we can also join multiple tables (e.g., 3, 4, etc.). To do so, we must use another INNER JOIN with a condition to enter the third table.
In the query below, the first INNER JOIN combines the ProductCategory table and the Fact table based on the CategoryID column in both tables. Next, the second one joins the Geography table using the common GeographyID column in the Fact and Geography tables.
SELECT c.CategoryID, c.CategoryName, g.GeographyID,
g.Country, Orders, Sale, OrderDate
FROM ProductCategory c
INNER JOIN Fact f ON c.CategoryID = f.CategoryID
INNER JOIN Geography g ON g.GeographyID = f.GeographyID
ORDER BY c.CategoryID
Instead of simply joining the two tables, you can use the GROUP BY and aggregate functions to calculate the total sales and orders for each category and country group. As we don’t have any duplicate or extra records with category and country combination in the fact table, it returns the same result. If you add a few more records, you can see the change in sales and order values.
SELECT CategoryName, Country, SUM(Orders) AS TotalOrder,
SUM(Sale) AS TotalSales FROM Fact f
INNER JOIN ProductCategory c ON f.CategoryID = c.CategoryID
INNER JOIN Geography g ON g.GeographyID = f.GeographyID
GROUP BY CategoryName, Country
ORDER BY CategoryName
Using SQL INNER JOIN with WHERE Clause
The INNER JOIN also allows us to use the WHERE Clause to limit the number of rows delivered by it. It is important to use the WHERE clause while performing an INNER JOIN on tables with a large number of rows. It helps to filter the number of records returned by the result set.
In this example, we will use that WHERE Clause along with the INNER JOIN to display the product sales that happened in the year 2025.
SELECT c.CategoryName, f.Orders, f.Sale, f.OrderDate FROM ProductCategory c
INNER JOIN Fact f ON
c.CategoryID = f.CategoryID
WHERE YEAR(OrderDate) = 2025
TIP: Replace the last statement with any other data values to filter the data based on dates. For instance, WHERE OrderDate > ‘2025-01-01’ to see the sales from January 2025. Use WHERE OrderDate < ‘2025-03-31’ to see the sales below March 2025.
Similar to the above, you can use the WHERE clause to filter the records returned by the INNER JOIN while combining three tables. The query below joins three tables and displays the product sales of INDIA.
SELECT c.CategoryID, c.CategoryName,
g.Country, Orders, Sale, OrderDate
FROM ProductCategory c
INNER JOIN Fact f ON c.CategoryID = f.CategoryID
INNER JOIN Geography g ON g.GeographyID = f.GeographyID
WHERE g.Country = 'INDIA'
ORDER BY c.CategoryID
CategoryID CategoryName Country Orders Sale OrderDate
1 Bikes INDIA 22 1200.00 2025-09-30
2 Components INDIA 45 500.00 2025-04-01TIP: You can also use the aggregated values and the HAVING clause to filter them.
To see the sales within a certain range, use the query below.
SELECT c.CategoryName, f.Orders, f.Sale, f.OrderDate FROM ProductCategory c
INNER JOIN Fact f ON
c.CategoryID = f.CategoryID
WHERE OrderDate BETWEEN '2024-10-01' AND '2025-05-30'
Difference between SQL INNER JOIN and OUTER JOIN
The INNER JOIN returns only the matching records from both tables. Whereas OUTER JOINS return the non-matching records.
- LEFT JOIN returns all records from the left table and matching rows from the right.
- RIGHT JOIN returns all records from the right table and matching from the left.
- FULL JOIN returns all records from both left and right tables.
Best Practices
- Double-check the column that you’re using to JOIN two tables.
- Always use the index columns after the ON clause to establish the SQL Server INNER JOIN connection between the two tables.
- Please select the required columns from both tables. Don’t use SELECT * or all the unnecessary columns.
- Always use the fully qualified names in INNER JOIN, including the table name or alias table name, to avoid confusion and errors.
- Whenever possible, use the WHILE clause to filter the INNER JOIN result set.
- Replace the CROSS JOIN with INNER JOIN when there is a possibility because it improves the performance.