New join operators are available in SQL Server 6.5. Because future versions of SQL Server may discontinue support for the "*=" and "=*" outer join operators, it is recommended that you use the ANSI-standard join clauses (LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN) instead. For SQL Server 6.5 information, see Joins and Select Statement in What's New for SQL Server 6.5.
Join operators compare two or more tables (or views) by specifying a column from each, comparing the values in those columns row by row, and concatenating rows in which the comparison is true. Comparisons must only be made between variables, columns, and so on, of similar type.
These are the join operators:
| Symbol | Meaning |
|---|---|
| *= | Include in the results all rows from the first table that meet the statement's restrictions. The second table returns values if there is a match on the join condition. Otherwise, the second table generates null values. |
| =* | Include in the results all rows from the second table that meet the statement's restrictions. The first table returns values if there is a match on the join condition. Otherwise, the first table generates null values. |
Joins that preserve all rows from a table in the results set, regardless of whether there is a matching row in the table to which they're being joined (*= and =*), are called outer joins.
Since bit columns do not permit null values, a value of 0 appears in an outer join when there is no match for a bit column in the inner table. Joins cannot be used for columns containing text or image values.
Null values in tables or views being joined will never match each other.
Joins can also be stated as subqueries. For details, see the Subqueries topic.