Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Fun with Outer Joins Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, January 20, 2014 4:38 AM
 Grasshopper Group: General Forum Members Last Login: Monday, April 20, 2015 1:28 AM Points: 19, Visits: 142
 rhydian (1/20/2014)Surely the following is an easier way to accomplish this...SELECT PR.id, PR.ProfessorName, ClassName, ClassYear, ClassSemester FROM Professor PR LEFT JOIN ( SELECT ProfessorID, ClassName, ClassYear, ClassSemester FROM Class WHERE ClassYear>=2011 ) SQ ON SQ.ProfessorID = PR.Id WHERE PR.HasTenure = 1It is equivalent (probably even for performance) to the proposed solution, but 2 SELECTs add visual clutter, IMO: you are repeating the list of fields 2 times. And, in complex queries, you could be tempted to use *, God forbid ;)For many people, your solution could probably be easier to understand, which has value.I would prefer to work with people that understands (and writes) the proposed solution, instead of this one.
Post #1532565
 Posted Monday, January 20, 2014 5:18 AM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, April 21, 2015 2:09 AM Points: 3, Visits: 56
 Oh I understand what the author is doing and it's a good article.But when it takes a less time, is probably more efficient and in my opinion is easier to visually understand, then I'll go for my suggestion. That would be my driving force. I'd change it if I found the author's method was more efficient.
Post #1532574
 Posted Monday, January 20, 2014 7:39 AM
 Grasshopper Group: General Forum Members Last Login: Friday, May 30, 2014 10:02 AM Points: 14, Visits: 49
Post #1532644
 Posted Monday, January 20, 2014 9:29 AM
 SSC Eights! Group: General Forum Members Last Login: 2 days ago @ 11:58 AM Points: 921, Visits: 2,258
 Mr. fisher you are correct. You can't eliminate the WHERE all the time. If I gave that impression then I apologize for a misstatement.I'm not against any clause in SQL, so avoiding any of them is not a thing for me. Des the query return the correct data and use resources as wisely as possible? Then it's good. If part of that turns out that there is no WHERE then fine.I find that pre-filtering using things like CTEs to be helpful but there are still folks, including me, that have SQL 2000 instances. ATBCharles Kincaid
Post #1532727
 Posted Monday, April 20, 2015 11:59 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Today @ 7:20 AM Points: 197, Visits: 1,150
 Itzik Ben-Gan saves the day once again!I was bothered that the join clause for the right table of a left outer join was being ignored. Little did I know...There are three logical steps to a join:1. cartesian product2. ON filter3. Add Outer rowsThe physical steps must produce the output that the logical steps would produce. Specifically, the right table filter on the left outer join was not being ignored in this article...the outer rows were being added back in on step 3.
Post #1678636

 Permissions