How do code this for SQL Server
-
In Oracle I could write this sql
select e.employee, u.user_name from employee e, user_name u where e.employee = u.employee(+) and e.employee = 123456
It would include the employee if they did not exist in the user_name table because of the "(+)" after the u.employee.
How would I do this for SQL Server 2000?
-
The (+) is Oracle specific. To code this in a more portable, ANSI SQL compliant way, you'd use LEFT OUTER JOIN:
select e.employee, u.user_name from employee e LEFT OUTER JOIN user_name ON (e.employee = u.employee ) WHERE e.employee = 123456
-
Or you can use the SQLServer specific notation (which is not encouraged any more because MS could discontinue to use it in the future version).
The MS specific notation looks like this (assuming you want to get all the records from the user_name table) :
select e.employee, u.user_name from employee e, user_name u where e.employee =* u.employee and e.employee = 123456
Bye
Gabor -
Actually it's no question of if, but rather when MS will discontinue. See if this helps explaining: http://www.microsoft.com/sql/techinfo/tips/development/July23.asp
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
That will most likely be the day I stop using SQL Server and make the move to Oracle. I've been weaning my developers from using constructs such as
T1 left outer join T2 on..., left outer join T3 on..,
and getting them to use
T1,
T2,
T3
where
T1.Key *= T2. Key and
T1,Key *= T3. Key.
It's much more user friendly and easier to read and debug.
And since they primarily rely on the query builder built into EM, that's what I get from them.
-
>>That will most likely be the day I stop using SQL Server and make the move to Oracle.
Hopefully by then, Oracle won't have moved completely to ANSI compliance and dropped the non-standard (+) notation.
>>I've been weaning my developers from using constructs such as
I feel for your developers, encouraging use of an archaic, Sybase 4.x (or earlier) derived notation that is a decade past retirement. Doesn't make their future job search any easier if they can't provide ANSI compliant SQL solutions to future interviewers. Good for you, I guess, if they can't find jobs elsewhere because you've locked them into old technology.
-
That will most likely be the day I stop using SQL Server and make the move to Oracle.
...
where
T1.Key *= T2. Key and
T1,Key *= T3. Key.
Do a search in any SQL Server online community for the problems with this old-style syntax. You should find endless threads on this.
It's much more user friendly and easier to read and debug.
One might be inclined to say, that this is highly subjective. But I know. old habits are hard to break. And IT folks are among th most conservatives around.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply