January 7, 2010 at 10:42 am
I'm looking at a view that use the *= syntax (see snippet below).
Does *= represent Right Outer Join? Haven't used this syntax before and need to convert it.
A.ID *= NP.ID AND
A.ID *= NS.ID
Thanks.
Rog
January 7, 2010 at 10:54 am
IIRC, *= is a left outer join, =* is a right outer join, *=* is a full outer outer join.
January 7, 2010 at 10:56 am
I just tested it to be sure. *= is left, =* is right.
I created a compat-80 database, and tested this:
create table dbo.L (
LNum int primary key);
go
create table dbo.R (
RNum int primary key);
go
insert into dbo.L (LNum)
select 1 union all
select 2 union all
select 3;
go
insert into dbo.R (RNum)
select 3 union all
select 4 union all
select 5;
go
select *
from L, R
where LNum *= RNum;
select *
from L
left outer join R
on LNum = RNum;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2010 at 10:59 am
left outer join, right outer join would have the asterisk on the right side of the =, this is not ansi standard SQL
January 7, 2010 at 11:16 am
Thanks everyone! Yes, now I have to convert it.
Roger
January 7, 2010 at 11:57 am
jcdyntek (1/7/2010)
left outer join, right outer join would have the asterisk on the right side of the =, this is not ansi standard SQL
I believe that indeed it was ANSI-89 standard SQL. ANSI-92 introduced LEFT/RIGHT/INNER/OUTER JOINs (to the grateful sighs of happiness of many many people).
In fact the book I used to teach Database Theory with (back in 1999) taught the SQL-89 standard.
Of course it also mentioned the ANSI-92 standard... but prior editions listed that as proprietary (all with publication dates after 1992, typically between 1996 and 2000).
January 7, 2010 at 1:13 pm
Why in the world would we be referencing a standard from 89?
I was of course referring to current ansi compliance
As of ansi-92 the *= in sql server and the (+) in oracle have been removed and replaced with the explicit outer join syntax.
Also when you try to use this structure in sql server today you get this message
Error Message:
Msg 4147, Level 15, State 1, Line 3
The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.
January 8, 2010 at 1:15 am
jcdyntek (1/7/2010)
Why in the world would we be referencing a standard from 89?
Because the OP asked a question about syntax from that standard so that he could convert the view to use the left/right outer join syntax?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 8, 2010 at 7:24 am
jcdyntek (1/7/2010)
Why in the world would we be referencing a standard from 89?
You write from left to write, right? Any idea how old that standard is?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 8, 2010 at 7:36 am
jcdyntek (1/7/2010)
Why in the world would we be referencing a standard from 89?
As Gail said, the OP is converting code using the *= style joins to ANSI-92 joins.
January 8, 2010 at 9:56 am
There's a relatively common pitfall that you can encounter when converting the old-style (*= / =*) outer join syntax to the LEFT / RIGHT OUTER JOIN syntax. This occurs when there is a WHERE clause search condition on a column in the right-hand table of a left outer join (or similarly, the left-hand table of a right outer join).
For example, consider the following:
DECLARE @a TABLE (
id int NOT NULL PRIMARY KEY,
name char(10) NOT NULL
)
DECLARE @b-2 TABLE (
id int NOT NULL,
stage char(1) NULL
)
INSERT INTO @a (id, name)
SELECT 1, 'aaa' UNION ALL
SELECT 2, 'bbb' UNION ALL
SELECT 3, 'ccc'
INSERT INTO @b-2 (id, stage)
SELECT 1, 'X' UNION ALL
SELECT 1, 'Y' UNION ALL
SELECT 1, 'Z' UNION ALL
SELECT 2, 'X' UNION ALL
SELECT 2, 'Y' UNION ALL
SELECT 3, 'X'
--old syntax
SELECT L.id, L.name, R.stage
WHERE (L.id *= R.id)
AND (L.name <> 'aaa')
AND (R.stage = 'Y')
--new syntax (version 1)
SELECT L.id, L.name, R.stage
FROM @a AS L LEFT OUTER JOIN @b-2 AS R
ON (L.id = R.id)
WHERE (L.name <> 'aaa')
AND (R.stage = 'Y')
--new syntax (version 2)
SELECT L.id, L.name, R.stage
FROM @a AS L LEFT OUTER JOIN @b-2 AS R
ON (L.id = R.id AND R.stage = 'Y')
WHERE (L.name <> 'aaa')
You might expect the query with the old-style outer join syntax to be equivalent to the version 1 query in the new syntax, but to get the same results as the original query, you need to move the (R.stage = 'Y') search condition from the WHERE clause into the LEFT OUTER JOIN, i.e. new syntax version 2 above.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply