I'm trying to trace some SQL in Microsoft Server. I came across a join that is using a convention unfamiliar to me. What does "=*" mean?
WHERE table1.yr =* table2.yr -1
views:
1799answers:
11Somebody is rep hungry...
victor hugo
2009-09-15 16:46:50
@victor hugo I'm still a newbie at this, please be kind. Honestly, I one-lined this one first, merely trying to help. The funny part, and I discovered this accidentally, is that we get more reps for these no-brainers than for much tougher questions. BTW why you'd tease _me_ on this, compared to later responders with higher rep / longer SO membership ?
mjv
2009-09-15 16:57:09
@mjv: I think Vitor was teasing me actually.
OMG Ponies
2009-09-15 16:58:36
@mjv: I think getting rep for no-brainers are easier because it's clear to more people than something esoteric. Bigger audience, better chance of more votes.
OMG Ponies
2009-09-15 17:00:49
@rexem/victor. Bingo, I get it now. I didn't see rexem's first comment on lack of specificity... LOL, funny how we get drawn into this funny race ;-)
mjv
2009-09-15 17:03:10
+14
A:
I believe that is old syntax indicating an outer join condition from table1 to table2
Old style:
SELECT * FROM table1, table2
WHERE table1.yr =* table2.yr -1
New style (SQL92):
SELECT * FROM table2
LEFT OUTER JOIN table1 ON table1.yr = table2.yr - 1
Eric Petroelje
2009-09-15 16:17:14
Actually your example is not correct. If you really want to use a left outer join it would be SELECT * FROM **table2** LEFT OUTER JOIN **table1** ON table1.yr = table2.yr-1
Turismo
2009-09-16 06:57:37
+1
A:
A ??? outer join is specified using the symbol =* in place of = in the WHERE clause.
alex
2009-09-15 16:17:45
Stop downvoting me, star on the RIGHT means that this side can contain nulls. It is LEFT outer join.
alex
2009-09-15 16:50:56
The question "Is *= a LEFT or RIGHT outer join" can't be answered, because you can write eitherFROM A, B ON A.c *= B.cor FROM A, B ON B.c *= A.cSame operator (*=), but one is a LEFT outer join and one is a RIGHT outer join.Nevertheless, I think you're wrong, and the * goes on the side of the column from the outer table, not the inner table.The moral of the argument is that this is horrid syntax. I like HLGEM's answer best.
Steve Kass
2009-09-15 17:44:14
I can tell you from actually trying it (and boy was it hard to write that syntax, shudder) that *= is the left join.Thanks for the nice words Steve.
HLGEM
2009-09-15 17:54:27
@HLGEM: You say "\*= is the left join. So are you telling me that these are all LEFT joins? (1) SELECT * FROM A JOIN B ON A.k \*= B.k; (2) SELECT * FROM A JOIN B ON B.k \*= A.k; (3) SELECT * FROM B JOIN A ON A.k \*= B.k; (2) SELECT * FROM B JOIN A ON B.k \*= A.k; ?? I don't think so. Are you saying that for each, if you take away the \* and add LEFT OUTER before JOIN (because \*= means LEFT JOIN), that will update them to the new syntax?
Steve Kass
2009-09-16 23:26:18
+2
A:
This is the old style of joins which were deprecated in ANSI SQL92. New syntax uses INNER and OUTER JOIN which join tables based on expressions rather than equality
Sailing Judo
2009-09-15 16:19:24
A:
yeap, it's another syntax for a left outer join
from table1 left outer join table2 on table1.yr = table2.yr - 1
opensas
2009-09-15 16:21:28
and it's also another syntax for a right outer join:from table2 right outer join table1 on table1.yr = table2.yr - 1
Steve Kass
2009-09-16 23:27:20
+33
A:
This:
WHERE t.column =* s.column
...is old TSQL (pre SQL Server 2005) outer join syntax, and is not an ANSI JOIN.
Reference: SQL Server 2005 Outer Join Gotcha
OMG Ponies
2009-09-15 16:21:28
+4
A:
That is the ANSI SQL 1989 syntax for RIGHT OUTER JOIN, where *= would be the LEFT OUTER JOIN.
You should note also that putting the join syntax in the WHERE clause is depricated in SQL 2008. http://scarydba.wordpress.com/2009/09/15/no-join-predicate/ <== A timely article on this.
Bryan S.
2009-09-15 16:37:29
+5
A:
It means the code needs to be replaced immediately! This style join is supposed to be a right join. Unfortunately it will sometimes be interpreted as a cross join, so the results of using this join may not be correct. Also, this syntax is deprecated and cannot be used inteh next version of SQl server.
HLGEM
2009-09-15 16:46:49
To whoever downvoted this, I said this because the current code is by any standard incorrect as the correct results cannot be guaranteed even as far back as SQL Server 2000. It even states that this code has inconsistent results in Books Online for SQL Server 2000. To leave it is the code base would be risky and unprofessional.
HLGEM
2009-09-15 21:15:36
A:
There are a lot of silly answers here. You didn't give the FROM clause, so there's no way to tell if your *= represents a LEFT or a RIGHT outer join.
WHERE table1.yr =* table2.yr -1
is old syntax for an outer join, for sure. But anyone who claims to know whether it's a LEFT or RIGHT outer join is mistaken. It depends on the order in which table1 and table2 are named in the FROM clause, and that's not given.
Steve Kass
2009-09-15 17:46:58
No. The * indicates which table is preserved, so in this example table2 is preserved.
JosephStyons
2009-09-15 20:24:13
Yes, table2 is preserved. I didn't say otherwise. But you still can't tell me whether this is a LEFT join or a RIGHT join, can you?
Steve Kass
2009-09-15 21:27:05
In the old syntax one usually uses the join condition to determine which table is considered LEFT and which is RIGHT. The ordering in the FROM clause is not relevant.
Turismo
2009-09-16 06:54:22
If you actually try it you will find that the from order is irrelevant and that *= is a left join and =* is a right join.
HLGEM
2009-09-16 15:20:44
@Turismo and HLGEM: The ordering in the FROM clause is not relevant to what the old-style \*= and =\* operators do. No argument there. But here's my point: what do you mean when you say "... is a LEFT JOIN" ? Consider these queries:select a.i, b.j from a left outer join b on a.k = b.k;select a.i, b.j from a right outer join b on a.k = b.k;select a.i, b.j from a left outer join b on b.k = a.k;select a.i, b.j from a right outer join b on b.k = a.k;Which ones are LEFT joins? Which can you write with =\*? Please don't tell me a query containing the keyword RIGHT is a "left join."
Steve Kass
2009-09-16 23:02:00
@Steve Kass: when using the new syntax you explicitly say which joins are left joins and which are right joins. But what you really do is specifying which table is the one that is preserved. So ...from a left outer join b on a.k = b.k is the same as ...from b right outer join a on a.k = b.k. The exact same thing is done with the old syntax where you specify which table is the preserved one by placing the \*. So ...where a.k \*= b.k is the same as b.k =\* a.k. The only question is how you name those joins. If in the old syntax the outer table is on the left hand I call it a left outer join.
Turismo
2009-09-17 08:36:29
A:
SELECT *
FROM table1, table2
WHERE table1.yr =* table2.yr -1
Means the same thing as this:
SELECT *
FROM
table2
LEFT OUTER JOIN
table1
ON table1.yr = (table2.yr - 1)
The * syntax is considered outdated, and is not in line with the ANSI standards.
Oracle has a similar construct like this:
WHERE table1.yr (+)= table2.yr
JosephStyons
2009-09-15 20:20:01
Actually the Oracle syntax is the other way around. The (+) is on the side of the inner (or optional) table, but the star is on the side of the outer table.So table1.yr =* table2.yr would be table1.yr(+) = table2.yr
Turismo
2009-09-16 06:45:51
A:
To be plain and simple. This is a SQL-92 outer join operator ( more info )
Don't use it, its very old school, but its similar to LEFT JOIN, and RIGHT JOIN. All its doing is telling which side of the join is the "Parent" side, so rows on that side will be considered first.
If you try to run this on SQL 2005, it will throw an error, saying that you need to run this in compatibility mode.
Oakcool
2010-02-19 23:53:01