September 20, 2022 at 7:07 pm
Another way to visualize this:
WITH racesIn2009
AS (
SELECT *
FROM dbo.races
WHERE race_year = 2009
)
SELECT circuits.name AS circuit_name
, circuits.location
, circuits.country
, r.name AS race_name
, r.round
FROM dbo.circuits
LEFT OUTER JOIN racesIn2009 r ON r.circuit_id = circuits.circuit_id
WHERE circuits.circuit_id < 70
This could also be done using a derived table:
SELECT circuits.name AS circuit_name
, circuits.location
, circuits.country
, r.name AS race_name
, r.round
FROM dbo.circuits
LEFT OUTER JOIN (SELECT *
FROM dbo.races
WHERE race_year = 2009
) r ON r.circuit_id = circuits.circuit_id
WHERE circuits.circuit_id < 70
Perfect Ed
For my educational purposes, can you let me know where the following doesn't work:
SELECT
circuits.name AS circuit_name
,circuits.location
,circuits.country
,races.name AS race_name
,races.round
FROM dbo.circuits
LEFT OUTER JOIN dbo.races
ON circuits.circuit_id = races.circuit_id
AND races.race_year = 2009
AND circuits.circuit_id < 70The only difference is
WHERE circuits.circuit_id < 70
In this - you changed the logic of the query. This query is asking for all circuits - and show the races in 2009 where the associated circuit is less than 70.
The original query was asking for the circuits that are less than 70 - and show the associated races for those circuits that occurred in 2009.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 20, 2022 at 7:17 pm
Hi Jeff,
I do like this:
SELECT circuits.name AS circuit_name
, circuits.location
, circuits.country
, r.name AS race_name
, r.round
FROM dbo.circuits
LEFT OUTER JOIN (SELECT *
FROM dbo.races
WHERE race_year = 2009
) r ON r.circuit_id = circuits.circuit_id
WHERE circuits.circuit_id < 70
September 21, 2022 at 5:25 am
Grant, sorry to be going on about this.
But just to be clear, are you saying that if not for the fact that I'm working with a OUTER JOIN everything would be fine?
That is to say, I would not have any problems if working with INNER JOIN?
Yes.
There's nothing "wrong" with outer joins. In fact, they are vital parts of how we retrieve data. Let me be 100% clear on that. I use outer joins. You should use outer joins. Outer joins are good. Hopefully we've now avoided any issues there.
However...
Outer joins can be a little bit easy to mess up. You're right there. Putting some filtering criteria in the WHERE clause can actually make the filter create an INNER join, because the requirements of the filter will force that kind of behavior. When filtering an outer join, you do have to examine the criteria with a different set of eyes. If the filter is only meant to be on the outer part of the table, then that filter must be a part of the ON clause in the join definition. It's just how you have to do things, logically.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 21, 2022 at 5:29 am
Grant Fritchey wrote:To a degree, the difference between ON & WHERE, can be, but isn't entirely, syntactic sugar. However, what I say is, for clarity sake, put filter criteria in the WHERE clause, and JOIN criteria in the ON clause. The exception is, where you are now, with the OUTER join.
I would argue that this is not an exception - the condition AND races.race_year = 2009 is part of the join criteria and not part of the filtering criteria. With that said, I agree that filtering (WHERE) should be done in the where clause - and not in the JOIN. I have seen many queries where the filtering was also done in the join to such a degree that the same filtering was applied to many joins. That just causes the code to be much harder to read, manage and maintain.
Honestly, it is best to think of this way, because it makes it easier to write the code correctly. However, I would argue that the races.race_year = 2009 is filtering criteria, not join criteria. Doesn't matter though, it must be done in the ON clause if you don't want it to be turned into an INNER JOIN or, force you to add a WHERE IS NULL in there (which is horrific) for an OUTER JOIN to work correctly.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply