February 18, 2009 at 1:52 pm
I'll admit when I am wrong. I am wrong. It must be the ANSI standard that is supported, FULL OUTER JOIN.
February 18, 2009 at 1:54 pm
Michael Valentine Jones (2/18/2009)
Lynn Pettis (2/18/2009)
Adi Cohn (2/18/2009)
If I remember correctly, SQL Server did support left and right join using the old syntax (*= or =*), but it didn’t support full outer join using the old syntax.Adi
I'm pretty sure SQL Server suported the *=* full outer join, and I can see if the SQL Server 6.5 book at home will answer that question. I do know, however, that ACCESS for a long time only supported the *= and =* syntax for joins. If you wanted to do a full outer join, you had to trick it (and sorry, I don't remember how that was accomplished).
*=* was not supported in SQL Server 6.5. See results below in SQL Server 6.5
select a.id,b.id
from
sysobjects a,
sysobjects b
where
a.id *=* b.id
order by
a.id,b.id
Results:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '*'.
Do you happen to have version of SQL Server 7.0/7.5 floating around?
February 18, 2009 at 1:56 pm
Michael Valentine Jones (2/18/2009)
Lynn Pettis (2/18/2009)
Adi Cohn (2/18/2009)
If I remember correctly, SQL Server did support left and right join using the old syntax (*= or =*), but it didn’t support full outer join using the old syntax.Adi
I'm pretty sure SQL Server suported the *=* full outer join, and I can see if the SQL Server 6.5 book at home will answer that question. I do know, however, that ACCESS for a long time only supported the *= and =* syntax for joins. If you wanted to do a full outer join, you had to trick it (and sorry, I don't remember how that was accomplished).
*=* was not supported in SQL Server 6.5. See results below in SQL Server 6.5
select a.id,b.id
from
sysobjects a,
sysobjects b
where
a.id *=* b.id
order by
a.id,b.id
Results:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '*'.
I also have a copy of SQL Server 6.5 Unleashed at home, I'll sell it cheap, $5.00 plus shipping.
February 18, 2009 at 1:58 pm
Lynn Pettis (2/18/2009)
I'm pretty sure SQL Server suported the *=* full outer join,
At least it is not in SQL Server docs
http://msdn.microsoft.com/en-us/library/ms177634(SQL.90).aspx
The outer join operators (*= and =*) are not supported when the compatibility level of the database is set to 90.
If you wanted to do a full outer join, you had to trick it (and sorry, I don't remember how that was accomplished).
Probably as usual emulate full outer join using left and right join with union.
Gints Plivna
http://www.gplivna.eu
February 18, 2009 at 2:04 pm
Lynn Pettis (2/18/2009)
Michael Valentine Jones (2/18/2009)
Lynn Pettis (2/18/2009)
Adi Cohn (2/18/2009)
If I remember correctly, SQL Server did support left and right join using the old syntax (*= or =*), but it didn’t support full outer join using the old syntax.Adi
I'm pretty sure SQL Server suported the *=* full outer join, and I can see if the SQL Server 6.5 book at home will answer that question. I do know, however, that ACCESS for a long time only supported the *= and =* syntax for joins. If you wanted to do a full outer join, you had to trick it (and sorry, I don't remember how that was accomplished).
*=* was not supported in SQL Server 6.5. See results below in SQL Server 6.5
select a.id,b.id
from
sysobjects a,
sysobjects b
where
a.id *=* b.id
order by
a.id,b.id
Results:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '*'.
Do you happen to have version of SQL Server 7.0/7.5 floating around?
I do have some SQL Server 7.0 servers.
I never heard of SQL 7.5.
February 18, 2009 at 2:07 pm
gints.plivna (2/18/2009)
Lynn Pettis (2/18/2009)
I'm pretty sure SQL Server suported the *=* full outer join,At least it is not in SQL Server docs
http://msdn.microsoft.com/en-us/library/ms177634(SQL.90).aspx
The outer join operators (*= and =*) are not supported when the compatibility level of the database is set to 90.
If you wanted to do a full outer join, you had to trick it (and sorry, I don't remember how that was accomplished).
Probably as usual emulate full outer join using left and right join with union.
Actually, we were talking about SQL Server 6.5. I never actually used the old style joins, equi or otherwise. When I started with SQL Server 6.5, I started using the ANSI standard joins, which is why it takes me a bit whenever I see code with old style joins. Trying to figure out what is the join criteria and what is the filter criteria can be confusing at times.
February 18, 2009 at 2:10 pm
Michael Valentine Jones (2/18/2009)
Lynn Pettis (2/18/2009)
Michael Valentine Jones (2/18/2009)
Lynn Pettis (2/18/2009)
Adi Cohn (2/18/2009)
If I remember correctly, SQL Server did support left and right join using the old syntax (*= or =*), but it didn’t support full outer join using the old syntax.Adi
I'm pretty sure SQL Server suported the *=* full outer join, and I can see if the SQL Server 6.5 book at home will answer that question. I do know, however, that ACCESS for a long time only supported the *= and =* syntax for joins. If you wanted to do a full outer join, you had to trick it (and sorry, I don't remember how that was accomplished).
*=* was not supported in SQL Server 6.5. See results below in SQL Server 6.5
select a.id,b.id
from
sysobjects a,
sysobjects b
where
a.id *=* b.id
order by
a.id,b.id
Results:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '*'.
Do you happen to have version of SQL Server 7.0/7.5 floating around?
I do have some SQL Server 7.0 servers.
I never heard of SQL 7.5.
There was a release between 7.0 and 2000. I remember it being called 7.5 even if unofficially. It could have just been a fairly major SP to 7.0.
February 18, 2009 at 2:16 pm
Just to complete the discussion, FULL OUTER JOIN was also supported in SQL 6.5
Just ran this in SQL 6.5:
select a.id,b.id
from
( select id from sysobjects where id > 4 ) a
full outer join
( select id from sysobjects where id < 10 ) b
on a.id = b.id
order by
a.id,b.id
Results:
id id
----------- -----------
NULL 1
NULL 2
NULL 3
NULL 4
5 5
6 6
7 7
8 8
9 9
10 NULL
11 NULL
12 NULL
13 NULL
14 NULL
15 NULL
16 NULL
17 NULL
18 NULL
(18 row(s) affected)
Viewing 8 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply