Welcome  |  Sign in
Skip Navigation Links
SQL Server TechCenter
Click to Rate and Give Feedback
TechNet
TechNet Library
SQL Server
SQL Server 2008
Database Engine
Development
Query Fundamentals
Join Fundamentals
Using Joins
 Using Inner Joins
Other versions are also available for the following:
SQL Server 2008 Books Online
Using Inner Joins

An inner join is a join in which the values in the columns being joined are compared using a comparison operator.

In the ISO standard, inner joins can be specified in either the FROM or WHERE clause. This is the only type of join that ISO supports in the WHERE clause. Inner joins specified in the WHERE clause are known as old-style inner joins.

The following Transact-SQL query is an example of an inner join:

USE AdventureWorks;
GO
SELECT *
FROM HumanResources.Employee AS e
    INNER JOIN Person.Contact AS c
    ON e.ContactID = c.ContactID
ORDER BY c.LastName

This inner join is known as an equi-join. It returns all the columns in both tables, and returns only the rows for which there is an equal value in the join column.

You can also join values in two columns that are not equal. The same operators and predicates used for inner joins can be used for not-equal joins. For more information about the available operators and predicates that can be used in joins, see Using Operators in Expressions and WHERE (Transact-SQL).

The following example uses a less-than (<) join to find sales prices of product 718 that are less than the list price recommended for that product. 

USE AdventureWorks;
GO
SELECT DISTINCT p.ProductID, p.Name, p.ListPrice, sd.UnitPrice AS 'Selling Price'
FROM Sales.SalesOrderDetail AS sd
    JOIN Production.Product AS p 
    ON sd.ProductID = p.ProductID AND sd.UnitPrice < p.ListPrice
WHERE p.ProductID = 718;
GO

Here is the result set. 

ProductID   Name                        ListPrice          Selling Price
----------- --------------------------- ------------------ -------------
718         HL Road Frame - Red, 44     1431.5000          758.0759
718         HL Road Frame - Red, 44     1431.5000          780.8182
718         HL Road Frame - Red, 44     1431.5000          858.90

(3 row(s) affected)

The not-equal join (<>) is rarely used. As a general rule, not-equal joins make sense only when used with a self-join. For example, this not-equal Transact-SQL join and self-join are used to find the subcategories that have at least two different prices less than $15:

USE AdventureWorks;
GO
SELECT DISTINCT p1.ProductSubcategoryID, p1.ListPrice
FROM Production.Product p1
    INNER JOIN Production.Product p2
    ON p1.ProductSubcategoryID = p2.ProductSubcategoryID
    AND p1.ListPrice <> p2.ListPrice
WHERE p1.ListPrice < $15 AND p2.ListPrice < $15
ORDER BY ProductSubcategoryID;
Note:
The expression NOT column_name = column_name is equivalent to column_name <> column_name.

The following Transact-SQL example uses a not-equal join combined with a self-join to find all the rows in the ProductVendor table in which two or more rows have the same ProductID but different VendorID numbers (that is, products that have more than one vendor):

USE AdventureWorks;
GO
SELECT DISTINCT p1.VendorID, p1.ProductID
FROM Purchasing.ProductVendor p1
    INNER JOIN Purchasing.ProductVendor p2
    ON p1.ProductID = p2.ProductID
WHERE p1.VendorID <> p2.VendorID
ORDER BY p1.VendorID
Tags What's this?: Add a tag
Community Content   What is Community Content?
Add new content RSS  Annotations
Processing
© 2008 Microsoft Corporation. All rights reserved. Terms of Use  |  Trademarks  |  Privacy Statement
Page view tracker