Inner Join in sql server

The inner join is one of the most commonly used joins in SQL Server. The inner join clause allows you to query data from two or more related tables.
See the following products and categories tables:
Products & Categories Tables
The following statement retrieves the product information from the production.products table:
SQL Server Inner Join Sample Table
The query returned only a list of category identification numbers, not the category names. To include the category names in the result set, you use the INNER JOIN clause as follows:
SQL Server Inner Join example
In this query:
The c and p are the table aliases of the production.categories  and  production.products tables. By doing this, when you reference a column in these table, you can use the alias.column_name instead of using the table_name.column_name. For example, the query uses c.category_id instead of production.categories.category_id. Hence, it saves you some typings.
For each row in the production.products table, the inner join clause matches it with every row in the product.categories table based on the values of the category_id column:
  • If both rows have the same value in the category_id column, the inner join forms a new row whose columns are from the rows of the production.categories and production.products tables according to the columns in the select list and includes this new row in the result set.
  • If the row in the production.products table doesn’t match the row from the production.categories table, the inner join clause just ingore these rows and does not include them in the result set.

SQL Server INNER JOIN syntax

The following shows the syntax of the SQL Server INNER JOIN clause:
In this syntax, the query retrieved data from both T1 and T2 tables:
  • First, specify the main table (T1) in the FROM clause
  • Second, specify the second table in the INNER JOIN clause (T2) and a join predicate. Only rows that cause the join predicate to evaluate to TRUE are included in the result set.
The INNER JOIN clause compares each row of the table T1 with rows of table T2 to find all pairs of rows that satisfy the join predicate. If the join predicate evaluates to TRUE, the column values of the matching rows of T1 and T2 are combined into a new row and included in the result set.
The following table illustrates the inner join of two tables T1 (1,2,3) and T2 (A,B,C). The result includes rows: (2,A) and (3,B) as they have the same patterns.
Note that the INNER keyword is optional, you can skip it as shown in the following query:

More SQL Server inner join examples

See the following productscategories, and brands tables:
Categories, products & brands tables
The following statement uses two INNER JOIN clauses to query data from the three tables:
SQL Server Inner Join clause - select from three tables example
In this tutorial, you have learned how to use the SQL Server INNER JOIN clause to query data from multiple tables.

Post a Comment