Conditional outer join question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Conditional outer join question

Author

Topic

waveform
Yak Posting Veteran

93 Posts

Posted - 2011-10-10 : 21:53:06

Hope everyone's having a splendid day (or night).

I'm battling with this query. I have 2 tables, Authors and Books. I'd like to pull out a list of Authors' names and any books they've written with > 400 pages. Easy for a simple inner join - but I want to show *all* authors, even if they don't have books which meet the criteria. That is, the book fields would be null.


CREATE TABLE #testAuthors (
	AuthorId int NOT NULL PRIMARY KEY CLUSTERED,
	AuthorName varchar(100) NOT NULL)

CREATE TABLE #testBooks (
	BookId int NOT NULL PRIMARY KEY CLUSTERED,
	AuthorId int NOT NULL, --REFERENCES #testAuthors (AuthorId),
	BookTitle varchar(100) NOT NULL,
	TotalPages int NOT NULL)
GO

INSERT INTO #testAuthors (AuthorId, AuthorName)
SELECT 1, 'Douglas Adams' UNION ALL
SELECT 2, 'Neil Gaiman' UNION ALL
SELECT 3, 'Another Author'
GO

INSERT INTO #testBooks (BookId, AuthorId, BookTitle, TotalPages)
SELECT 1, 1, 'Salmon of Doubt', 336 UNION ALL
SELECT 2, 1, 'Mostly Harmless', 240 UNION ALL
SELECT 3, 2, 'American Gods', 624 UNION ALL
SELECT 4, 2, 'Anansi Boys', 416
GO

SELECT a.AuthorName, b.BookTitle, b.TotalPages
FROM #testAuthors a LEFT OUTER JOIN #testBooks b ON b.AuthorId = a.AuthorId
WHERE b.TotalPages > 400
ORDER BY a.AuthorName, b.BookTitle, b.TotalPages DESC
GO

The query above gets only the matching author:


Neil Gaiman | American Gods | 624
Neil Gaiman | Anansi Boys   | 416

However I'd like to retrieve *all* authors, with NULL book fields if no books match (or none exist). Desired outcome is this:


Another Author | NULL          | NULL
Douglas Adams  | NULL          | NULL
Neil Gaiman    | American Gods | 624
Neil Gaiman    | Anansi Boys   | 416

How would I go about getting this result, in the most efficient way?

Many thanks!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-10 : 22:53:52

By placing the a reference to your LEFT table in the WHERE clause you've essentially turned your outer join into an inner join. You need to include the "b.TotalPages > 400" in your ON criteria.

Be One with the Optimizer
TG

waveform
Yak Posting Veteran

93 Posts

Posted - 2011-10-11 : 00:26:04

Ahh! Thank you kind sir.

Is putting clauses in the join criteria a best-practice thing to do? It's not inefficient or anything?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-11 : 08:44:42

You're welcome.
No, it is not a best practice thing. In this case there was no alternative - the two variations resulted in different queries with different answers. When there is no functional difference between criteria in the WHERE clause vs. within the JOIN correlations then sql server will usually come up with the best execution plan no matter which way you organize it. However it would be to your advantage to look at the execution plan and statistics for both options and answer those questions for yourself.

Be One with the Optimizer
TG

waveform
Yak Posting Veteran

93 Posts

Posted - 2011-10-11 : 11:07:39

Thanks TG. However not sure what you mean by "both options". The only option I see at the moment is putting the clause in the join. Is there another way to achieve the same result?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources