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.
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.
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.
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.
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;
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;
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
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
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
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.