Sybase outer joins are not supported in MS SQL Server. Use the ANSI outer join instead.

Examples

create table Dealer (name varchar(10), i int)

create table Car (i int, brand varchar(10))

insert into Dealer values ('Jackson', 1)

insert into Dealer values ('Jackson', 2)

insert into Dealer values ('Peterson', 1)

insert into Dealer values ('McDonald', 0)

insert into Car values (1, 'Dodge')

insert into Car values (2, 'Buick')

insert into Car values (3, 'Mercury')

go

-- These statements will not work on MS SQL Server

select * from Dealer, Car where Dealer.i *= Car.i order by 1, 4

select * from Dealer, Car where Dealer.i =* Car.i order by 1, 4

go

-- Use the following instead (ANSI joins will also work in ASE)

select *

from Dealer left outer join Car on (Dealer.i = Car.i)

order by 1, 4

select *

from Dealer right outer join Car on (Dealer.i = Car.i)

order by 1, 4

go

drop table Dealer

drop table Car

Remarks

ASE does not support the ‘full outer join’, but MS SQL Server does.

select *

from Dealer full outer join Car on (Dealer.i = Car.i)

order by 1, 4

Extra conditions on a table in a ‘Sybase outer join’ are interpreted as belonging to the ‘ON’ clause instead of to the ‘WHERE’ clause.

create table A (i int, s varchar(30))

create table B (i int, s varchar(30))

insert into A values (1, 'string 1')

insert into A values (2, 'string 2')

insert into B values (2, 'string 2')

insert into B values (4, 'string 4')

go

select A.i, A.s, B.s from A, B where A.i =* B.i and A.s like 's%'

go

select A.i, A.s, B.s from A right outer join B on A.i = B.i where A.s like 's%'

select A.i, A.s, B.s from A right outer join B on A.i = B.i and A.s like 's%'

go

drop table A

drop table B

go