Export (0) Print
Expand All

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

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

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;
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
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

© 2014 Microsoft