Is this a shortcut for an outer join and if so, why can't I find any info on it?
select id, name
from table1, table2
where id *= id_tbl2
instead of
select tb1.id, tb1.name
from table1 as tb1
outer join table2 as tb2
where tb1.id = tb2.id_tbl2
and what happens in the first example if you try to join more tables?http://en.wikipedia.org/wiki/Join_(SQL)
the first is an inner join.
the 2nd would show all result (left outer join) for table1 and only combine the result from table2 that matched. Of course I could be wrong on this if it is something MS SQL specific. Does it allow for just 'outer join'?|||http://en.wikipedia.org/wiki/Join_(SQL)
the first is an inner join.
the 2nd would show all result (left outer join) for table1 and only combine the result from table2 that matched. Of course I could be wrong on this if it is something MS SQL specific. Does it allow for just 'outer join'?
according to sql server, the first is an outer join.
you can verify that by attempting
select id
from table1
where id *= 1
and the error message is
Msg 150, Level 15, State 1, Line 5
Both terms of an outer join must contain columns.|||*= is indeed a left outer join, just as =* is a right outer join. It is a T_SQL extension and was valid up thru S2K. It is no longer available in S2K5.
It is not in the ANSI Standard, so if you want your code to run in SQL Server later than 2000, do not use it!|||oops, I am sorry. that was something ms sql specific. I should not have answered that. I just didn't see the little * :/
sorry about that. well.. at least I learned something :)|||*= is indeed a left outer join, just as =* is a right outer join. It is a T_SQL extension and was valid up thru S2K. It is no longer available in S2K5.
It is not in the ANSI Standard, so if you want your code to run in SQL Server later than 2000, do not use it!
Code I inherited.
I don't think the db admin knew exactly what he was doing, other than taking a shortcut to write less code.|||Its not a shortcut. Its an "old cut". It used to be the standard coding syntax, so far back in the mists of time that only wise men such as Pat Phelan know the origins.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment