The WHERE clause specifies the connection between the tables or views named in the FROM clause and restricts the rows to be included in the results. It gives the names of the columns to be joined (qualified by table names if necessary) and the join operator, such as equality.
Important If you omit the WHERE clause in a join, you will get unexpected results. Without a WHERE clause, any of the join queries discussed so far produce 184 rows instead of 2! This situation is explained in How Joins Are Processed.
Use the following comparison operators to determine the basis on which columns will be matched:
| Symbol | Meaning |
|---|---|
| = | Equal to |
| > | Greater than |
| > = | Greater than or equal to |
| < | Less than |
| < = | Less than or equal to |
| < > | Not equal to |
| ! > | Not greater than |
| ! < | Not less than |
Joins that use the relational operators are collectively called theta joins. Another set of join operators is used for outer joins (discussed in detail later in this chapter). The outer join operators are *=, which includes in the results all the rows from the first table, not just the ones in which the joined columns match, and =*, which includes in the results all the rows from the second table, not just the ones in which the joined columns match.
Columns being joined need not have the same name or be the same datatype. However, if the datatypes are not identical, they must be compatible ¾ types that SQL Server automatically converts. (For details about datatype conversion, see Using Built-in Functions.)
The WHERE clause of a join statement can include other conditions in addition to the one that links columns from different tables.
Note Tables cannot be joined on text or image columns. You can, however, compare the lengths of text columns from two tables with a WHERE clause. For example:
WHERE DATALENGTH(textab_1.textcol) > DATALENGTH(texttab_2.textcol)