Home

	Expand Search

Transact-SQL User's Guide

Chapter 4 Joins: Retrieving Data from Several Tables

How joins are structured

The where clause

The where clause

Use the where clause to determine which rows are included in the results. where specifies the connection between the tables and views named in the from clause. Be sure to qualify column names if there is ambiguity about the table or view to which they belong. For example:

where authors.city = publishers.city 

This where clause gives the names of the columns to be joined, qualified by table names if necessary, and the join operator--often equality, sometimes "greater than" or "less than." For details of where clause syntax, see Chapter 2, "Queries: Selecting Data from a Table".

You will get unexpected results if you omit the where clause of a join. Without a where clause, any of the join queries discussed so far will produce 69 rows instead of 2. "How joins are processed" explains this unexpected result.

The where clause of a join statement can include other conditions in addition to the one that links columns from different tables. In other words, you can include a join operation and a select operation in the same SQL statement. See "How joins are processed" for an example.

Joins that match columns on the basis of equality are called equijoins. A more precise definition of an equijoin is given under "Equijoins and natural joins", along with examples of joins not based on equality.

Equijoins use the following comparison operators:

Joins that use the relational operators are collectively called theta joins. Another set of join operators is used for outer joins, also discussed in detail later in this chapter. The outer join operators are Transact-SQL extensions, as shown in Table 4-2:

The columns being joined must have the same or compatible datatypes. Use the convert function when comparing columns whose datatypes cannot be implicitly converted. Columns being joined need not have the same name, although they often do.

If the datatypes used in the join are compatible, Adaptive Server automatically converts them. For example, Adaptive Server converts among any of the numeric type columns--int, smallint, tinyint, decimal, or float, and among any of the character type and date columns--char, varchar, unichar, univarchar, nchar, nvarchar, and datetime. For details on datatype conversion, see Chapter 10, "Using the Built-In Functions in Queries," and the "Datatype Conversion Functions" section of the Reference Manual.

You cannot use joins for columns containing text or image values. You can, however, compare the lengths of text columns from two tables with a where clause. For example:

where datalength(textab_1.textcol) >
datalength(textab_2.textcol)