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 AdventureWorks2008R2; GO SELECT * FROM HumanResources.Employee AS e INNER JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID ORDER BY p.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 AdventureWorks2008R2; 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 AdventureWorks2008R2; 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;
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 AdventureWorks2008R2; 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