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 SQL-92 standard, inner joins can be specified in either the FROM or WHERE clause. This is the only type of join that SQL-92 supports in the WHERE clause. Inner joins specified in the WHERE clause are known as old-style inner joins.

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.

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:

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):