Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fun with Outer Joins


Fun with Outer Joins

Author
Message
chiesa.alberto
chiesa.alberto
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 169
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 = 1


It 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 Wink
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.
rhydian
rhydian
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 57
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.
Atradius
Atradius
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 53
First, it is actually not true that an expression containing NULL evaluates to FALSE.
It evaluates to UNKNOWN.
A WHERE clause filters out all UKNOWN values, and that is why you get this behaviour in your first query.

But not all SQL constructs filter out unknown values so it will give confusion to people if you tell them that expressions with NULL in them evaluate to FALSE.

If I had to teach your students, I would also tell them to never add any condition into the ON clause which is not a direct join relation between the tables (i.e. table1.column1 = table2.column2). This helps them understand the proper purposes of the ON versus the WHERE clauses. (In your final "correct" query you added such a condition).

Rather have them understand why the WHERE clause does this (filtering UNKNOWN values).

Otherwise I recommend the book from Itzik Ben-Gan "T-SQL Fundamentals" which covers the above.)
(Interestingly, he writes in that book that it is the most difficult task to write about the "simple" basics - and it is ...)

Second, I would add that there is another confusion which specifically arises with LEFT OUTER joins, which I believe is even more common with SQL beginners:

If you, in your example, simply wanted to get a list of professors, and also list if they had any class at all, you "naturally" would think "we'll use a left outer join, this will return a list of all professors, and it will list the class he has and if he has no class it will be null on the right side".
And then they are confused as to why it does not return a single line with each professors, but there are several rows for a single professor (if he has more than one class).

So the description of a left outer join as "it returns all rows of the left-hand table, even if there is no row in the right-hand table" is very misleading - it can be understood to imply it would return exactly the rows of the left-hand table, plus data from a matching row if there is one.
But that is not what it does.
In fact, what it does is: join all matching rows from both tables (inner join), resulting in potentially many "duplicate" rows from the left-hand table, and then add a single row for all left-hand table rows which have no matching equivalent in the right-hand table.
Charles Kincaid
Charles Kincaid
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1049 Visits: 2383
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
Bill Talada
Bill Talada
Old Hand
Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)

Group: General Forum Members
Points: 385 Visits: 1818
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 product
2. ON filter
3. Add Outer rows

The 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search