Sybase Technical Library - Product Manuals Home
[Search Forms] [Previous Section with Hits] [Next Section with Hits] [Clear Search] Expand Search

more than two tables [Table of Contents] How
null values affect joins

Transact-SQL User's Guide

[-] Chapter 4 Joins: Retrieving Data from Several Tables
[-] Outer joins

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

more than two tables [Table of Contents] Inner
and outer tables