February 18, 2009 at 8:46 am
I'm aware ofthe functional equivalences between RIGHT and LEFT joins, I just always thought '*=' was really RIGHT OUTER JOIN and '=*' was LEFT OUTER JOIN is all, opposite of what is being said in this post. Mostly due to the way I'm used to ordering the tables in relation to the operators.
Cheers
maddog
February 18, 2009 at 8:51 am
J (2/13/2009)
FWIW,I tend to avoid *= like the plague.
Although more verbose, "LEFT OUTER JOIN" is, in my opinion, much easier to follow when reading a long script.
I would say, if you have to debug one such script, it would be worthwhile to replace the "*="statements.
Of course, if you are stuck with legacy code, yeah, some time ago there were people using the "clever" short notation. And there were people writing write-only, unreadable APL programs which performed complex calculations in just two or three lines of codes.
I wouldn't say "clever" code. IIRC SQL 7 introduced the Ansi join. before that, *= was all you had. Myself, I still think where clause equi-joins to be more clear, but then I'm a dinosaur.;) I do bow to the ansi convention now.
February 18, 2009 at 9:13 am
I realize the OP hasn't posted anything yet, but I put together the following code to demonstrate what is happening. The key thing to remember from this is when you are converting the *=, =*, *=* style joins to ANSI standard joins is to be sure to test your queries to ensure that you get back what is expected.
For some reason I can't post my code, IE throws an error saying I am not connected to the internet, so I have attached my code in the text file attached to this post.
The code also includes comments regarding what is going on with the queries.
February 18, 2009 at 11:31 am
Okay, I have checked and can see that some people have checked out my code. Anyone have any feedback regarding my small test suite?
February 18, 2009 at 12:12 pm
jgrubb (2/18/2009)
I wouldn't say "clever" code. IIRC SQL 7 introduced the Ansi join. before that, *= was all you had. Myself, I still think where clause equi-joins to be more clear, but then I'm a dinosaur.;) I do bow to the ansi convention now.
SQL Server has supported ANSI joins since at least version 6.0. I never worked with earlier versions, so it might have supported it before that.
Also, there are some slight differences in the way that the left/right outer joins work vs. *=/=* joins, so *=/=* should be avoided.
I just ran the following code on SQL Server 6.5 and it worked fine:
select a.id,b.id
from
sysobjects a
left outer join
( select id from sysobjects where id < 10 ) b
on a.id = b.id
order by
a.id,b.id
Results:
id id
----------- -----------
1 1
2 2
3 3
4 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)
February 18, 2009 at 12:19 pm
I actually have to agree with Michael. I started with MS SQL Server 6.5 and used the ANSI style INNER and OUTER JOIN syntax from the start. It just made more sense to me as I started working with SQL Server.
February 18, 2009 at 12:19 pm
You got me there. Did some quick checking and it was Sybase 10 that didn't support them until later (Syb 12). I was porting apps between Sybase/SQL Server server and Informix, Oracle and DB/2 at the time (95-97). So I tried to get lowest common denominator for everything.
Ansi standards were a utopian dream then.............
February 18, 2009 at 12:32 pm
Michael Valentine Jones (2/18/2009)
jgrubb (2/18/2009)
I just ran the following code on SQL Server 6.5 and it worked fine:
I have to admit that I’m amazed that you have SQL Server 6.5 up and running. Last time that I’ve seen one in production environment was at 2003.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 18, 2009 at 12:36 pm
Adi Cohn (2/18/2009)
Michael Valentine Jones (2/18/2009)
jgrubb (2/18/2009)
I just ran the following code on SQL Server 6.5 and it worked fine:
I have to admit that I’m amazed that you have SQL Server 6.5 up and running. Last time that I’ve seen one in production environment was at 2003.
Adi
Me too, actually. The SQL Server 6.5 installations (we had 2) went the way of the Dodo as soon as SQL Server 7.0 went RTM. That's how fast we converted.
February 18, 2009 at 1:01 pm
Lynn Pettis (2/18/2009)
Okay, I have checked and can see that some people have checked out my code. Anyone have any feedback regarding my small test suite?
Hi Lynn, if you were referrering to me, and not the OP, and my apparent confusion of whether '*=' means LEFT or RIGHT outer join :), first let me say your example makes perfect sense and thanks for taking the time to lay it out. As one poster said basically it all depends on what side of the operator you place the table references in the case of an implicit join and I get that part of it.
My mistake stemmed from the situation that when looking at code that uses the older implicit join syntax I am going into it preconditioned with my own habits of placement of table references in relation to the operator or to the JOIN clause. So when I see code that looks like:
SELECT a.column1
FROM tableA a, tableB b
WHERE b.column1 =* a.column1
Where table A is the primary table for which I want all occurrences regardless of whether or not there is a match in the secondary table B, since as a habit (for better or worse) I always place the secondary table first on the left side of any operator as the above example does. I would thus translate the equivalent syntax using a JOIN as:
SELECT a.column1
FROM tableA a
LEFT OUTER JOIN tableB b ON b.column1 = a.column1
Note the table references (as in table B) are on the same side of the operator in both examples - this results in the first option using an implicit RIGHT OUTER JOIN and the second option as a LEFT OUTER JOIN. So in my ignorance just because the table references are on the same side of the operator, with one a LEFT OUTER JOIN and one a '=*', it appears to me as if LEFT OUTER JOIN is the same as '=*'.
Cheers,
maddog
February 18, 2009 at 1:22 pm
Lynn Pettis (2/18/2009)
Adi Cohn (2/18/2009)
Michael Valentine Jones (2/18/2009)
jgrubb (2/18/2009)
I just ran the following code on SQL Server 6.5 and it worked fine:
I have to admit that I’m amazed that you have SQL Server 6.5 up and running. Last time that I’ve seen one in production environment was at 2003.
Adi
Me too, actually. The SQL Server 6.5 installations (we had 2) went the way of the Dodo as soon as SQL Server 7.0 went RTM. That's how fast we converted.
The company I work for has been assembled over time from 30+ mergers, so there are many surprises out there in the IT backwoods. :crying:
I only recently discovered this server, and had to scramble to find a copy of the 6.5 install disk to be able to use the client tools. Of course the app vendor is long gone and no one knows if we could get it to run on a later SQL version.
As least I had some 6.5 experience. I can't wait till I find that SQL Server 4.2 is supporting some critical revenue generating app.
February 18, 2009 at 1:26 pm
Technically, the following is true (and I had to have it explained several times before it sunk in to me):
*= left outer join
=* right outer join
*=* full outer join
The left, right, or full being determined by where the '*' is located to the '=' sign.
In the ANSI style joins, I don't think in matters which side of the '=' the columns names are located, it is the postion of the tables names in relation to the LEFT OUTER JOIN and RIGHT OUTER JOIN clauses (or operators).
The OP's original question was why is one query returning more rows than the other. That is what I was trying to determine and explain with my code.
February 18, 2009 at 1:32 pm
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
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 18, 2009 at 1:36 pm
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).
February 18, 2009 at 1:49 pm
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 '*'.
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply