Using the SQL Server INNER JOIN Clause

SQL INNER JOIN fundamentals

SQL JOIN clauses retrieve data from two or more database tables and present that data in a result table based on a related column between the tables that are queried. The INNER JOIN is the most common of all.

What is INNER JOIN in SQL?

The INNER JOIN is used to fetch records that have matching values in two or more tables. Hence, the result table is created containing matching rows in all these tables.

INNER JOIN explanation

How to use INNER JOIN in SQL

How to write INNER JOIN query in SQL

Let's try to gain insight into how to use INNER JOINs in SQL Server on the following example. In the customerdemo database, we have three tables: Customer, Orders, and Countries. The CustomerId column of the Customers table is a foreign key referencing the CustomerId column of the Orders table.

INNER JOIN Syntax

Below is the syntax example showing how to use INNER JOIN to join two tables in SQL. 

SELECT tableA.column1, tableB.column2...
FROM tableA
INNER JOIN tableB
ON tableA.id_field = tableB.id_field;



The query returns a result table containing only the matching records from the two tables.

dbForge SQL Complete

Enjoy even the most complex JOINs with SQL Complete

Download

INNER JOIN example

The Customers and Countries tables in our customerdemo database are linked with a foreign key. We want to retrieve data from these tables to see the list of customers and countries they live in. In other words, we want the result table to combine data from these two tables. For this, we run the following query: 

SELECT
	cs.CustomerID
	,cs.Name
	,cs.CountryID
	,cn.CountryName
FROM Customers cs
INNER JOIN Countries cn
	ON cs.CountryID = cn.CountryID;
INNER JOIN condition in SQL

INNER JOIN on three tables: Example

When working with INNER JOIN, you are not limited to just two tables. In SQL, it is possible to use multiple joins to combine three or even more tables — just add another JOIN clause and specify another condition. 

SELECT column_name1,column_name2,..
FROM tableA
INNER JOIN tableB
    ON condition_1
INNER JOIN tableC
    ON condition_2;
Double INNER JOIN in SQL to join 3 tables

Using WHERE clause with
INNER JOIN

SQL query with INNER JOIN and WHERE condition allows you not only to retrieve data from the two or more tables but also to filter the data according to the specified criterion. 

SELECT tableA.column1, tableB.column2...
FROM tableA
INNER JOIN tableB
    ON tableA.id_field = tableB.id_field
WHERE search_condition
INNER JOIN in SQL with WHERE condition

Using GROUP BY with
INNER JOIN

You can use GROUP BY with INNER JOIN in SQL Server to group the results according to the values in a list of one or more column expressions. 

SELECT tableA.column1, tableB.column2...
FROM tableA
INNER JOIN tableB
    ON tableA.id_field = tableB.id_field
GROUP BY column_name | column_expression
GROUP BY with INNER JOIN query in SQL Server

Deleting data from database tables using INNER JOIN

You can also use INNER JOIN to delete rows in one table based on data in another table. To put it differently, if the DELETE statement contains the JOIN clause, it will remove the records that satisfy the specified JOIN condition. 

DELETE tableA
FROM tableA
INNER JOIN tableB
ON tableA.id_field = tableB.id_field
WHERE search_condition
Delete from INNER JOIN SQL Server

SQL Complete

Advanced solution for SQL database development, management, and administration

Download for free

With SQL Complete, you GET

  • Suggestions for full joining conditions after the JOIN keyword
  • Suggestions for joining conditions after the ON keyword
  • Context-based prompts for all possible combinations to join tables
  • Significant time savings
  • An opportunity to improve your code quality
  • Freedom from the need to memorize table and column names

Your ultimate SSMS add-in

SQL Complete is an advanced solution for SQL database development, management, and administration. Available as an add-in for Visual Studio and SSMS, the tool offers excellent autocompletion, code formatting and refactoring capabilities.

dbForge SQL Complete

Get a free fully functional 14-day trial of dbForge SQL Complete and benefit from the rich feature set confined in a lightweight solution.
Download

We use cookies to provide you with a better experience on the Devart website. You can read more about our use of cookies in our Cookies Policy.
Click OK to continue browsing the Devart site. Be aware you can disable cookies at any time.