Transact-SQL User's Guide

Home

Transact-SQL User's Guide

Chapter 4 Joins: Retrieving Data from Several Tables

Outer joins

Joins that include all rows, regardless of whether there is a matching row, are called outer joins. Adaptive Server supports both left and right outer joins. For example, the following query joins the titles and the titleauthor tables on their title_id column:

select * 
from titles, titleauthor
where titles.title_id *= titleauthor.title_id

Sybase supports both Transact-SQL and ANSI outer joins. Transact-SQL outer joins (shown in the previous example) use the *= command to indicate a left outer join and the =* command to indicate a right outer join. Transact-SQL outer joins were created by Sybase as part of the Transact-SQL language. See "Transact-SQL outer joins" for more information about Transact-SQL outer joins.

ANSI outer joins use the keywords left join and right join to indicate a left and right join, respectively. Sybase implemented the ANSI outer join syntax to fully comply with the ANSI standard. See "ANSI Inner and outer joins" for more information about ANSI outer joins. This is the previous example rewritten as an ANSI outer join:

select * 
from titles left join titleauthor
on titles.title_id = titleauthor.title_id

Inner and outer tables

Outer join restrictions

Views used with outer joins

ANSI Inner and outer joins

Correlation name and column referencing rules for ANSI joins

ANSI inner joins

ANSI outer joins

Should the predicate be in the on or where clause?

Nested ANSI outer joins

Converting outer joins with join-order dependency

Transact-SQL outer joins


	Expand Search