Self-Join versus my way...

Question

Self-Join versus my way...

darth.pathos

Ten Centuries

Points: 1084
More actions
December 8, 2009 at 11:16 am

#214565

Hi All,
We have finally been given the go-ahead to migrate up to SQL Server 2008 (YAH!) and I'm reading the Books Online trying to get a handle on all the new cool features. I'm playing around with basic joins in SQL 2000, and this one has me a little confused. The first query is taken from BOL, the second is the way I've been doing it all along.
Both execution plans are identical, so is there in fact an improvement using the Self-Join as in BOL? Or is mine in reality a self-join, just done differently?
Will SQL Server 2008 be able to handle my query, or should I get used to doing it the BOL-way?
Lastly, why would the Emp.LastName+', '+Emp.Firstname information be sorted differently? Under some managers there's not a huge difference, but under others not one employee is listed in the same spot.
Appreciate your time! 😀
Chris
select Mgr.LastName, Emp.LastName+', '+Emp.Firstname
from employees as emp join employees as mgr
on emp.manageremployeeid = mgr.employeeid
order by mgr.lastname
select e2.lastname, e.lastname+', '+e.firstname
from employees e, employees e2
where e.manageremployeeid = e2.employeeid
order by e2.lastname

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

darth.pathos (12/8/2009)
Hi All,
We have finally been given the go-ahead to migrate up to SQL Server 2008 (YAH!) and I'm reading the Books Online trying to get a handle on all the new cool features. I'm playing around with basic joins in SQL 2000, and this one has me a little confused. The first query is taken from BOL, the second is the way I've been doing it all along.
Both execution plans are identical, so is there in fact an improvement using the Self-Join as in BOL? Or is mine in reality a self-join, just done differently?
Will SQL Server 2008 be able to handle my query, or should I get used to doing it the BOL-way?
Lastly, why would the Emp.LastName+', '+Emp.Firstname information be sorted differently? Under some managers there's not a huge difference, but under others not one employee is listed in the same spot.
Appreciate your time! 😀
Chris
select Mgr.LastName, Emp.LastName+', '+Emp.Firstname
from employees as emp join employees as mgr
on emp.manageremployeeid = mgr.employeeid
order by mgr.lastname
select e2.lastname, e.lastname+', '+e.firstname
from employees e, employees e2
where e.manageremployeeid = e2.employeeid
order by e2.lastname

Both are self joins. First query is an ANSI Stanard join, and the second is what we call an old style join. As far as equi-joins, both work. What you need to look for are the OUTER joins using the old style join syntax *= (left outer join), =* (right outer join), *=* (full outer join) as those no longer work as os SQL Server 2005.

Personally, I look at writing all new T-SQL using ANSI Standard joins and modify the old-style equi-joins as you can. Your main priority right now should be rewriting all the T-SQL that uses old-style outer joins.

Regarding the ordering of employees by manager, no guaranttees how they will be returned as you are only ordering the result set by the mangers last name. If you want the employees names ordered as well, you need to add that to the ORDER BY clause as well.

darth.pathos Ten Centuries Points: 1084 More actions · Answer 2

Thanks Lynn! 😀

We don't have many self-joins so I may as well update them all, I was just curious.

Now, *= and =* joins are going to be another matter for me entirely....anyone want to come to a TSQL Updating party?? lol

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

darth.pathos (12/8/2009)
Thanks Lynn! 😀
We don't have many self-joins so I may as well update them all, I was just curious.
Now, *= and =* joins are going to be another matter for me entirely....anyone want to come to a TSQL Updating party?? lol

Remember, what you showed was a self join. That is also how inner joins between tables are written. You don't need to write those now, but you should consider moving to the ANSI Standard in future code, and look at rewriting existing code whenever you have to make modifications to the existing code.

I find using the ANSI Standard makes the code more readable. It separates the JOIN criteria (in the FROM CLAUSE) from the FILTER criteria (in the WHERE CLAUSE).

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 4

I take Lynn's point of view. I used to write all my joins as you did, Chris and that's how I learned. However in the 2000/2001 timeframe when Microsoft started to move more to ANSI standards, I switched most of mine to the new syntax and it definitely became easier to figure out those complex queries. Not the simple 2-3 tables ones, but the ones were you have 8-12 tables involved were much more readable.

Definitely look to change for the outer joins.

darth.pathos Ten Centuries Points: 1084 More actions · Answer 5

Thanks Steve and Lynn, I'm going to be taking your advice - I'm working on a plan right now of our "high priority" queries and will start on those.

thanks again for taking time to reply!

Chris