I’ve been working with Linq in .NET lately and there have been a few posts I would have liked to add but since I said I was going to blog SQL tricks I’m putting other topics to one side for the moment.

So… another situation where it would be useful to have an SQL trick is the case of the filtered outer join. Suppose I have a chain of high street charity shops. Every shop should have a manager, who may be the only staff member (OK, not a fully-developed scenario). I want to find the manager’s name for each branch as well as listing any branches which have no manager, and new branches which have no staff at all.

The SQL coding style I prefer puts equi-joins between columns in the JOIN clause. All other types of column matching and filters go into the WHERE clause. Unfortunately, this doesn’t work with a Left Outer join as you can see in the example below (written in SQL Server 2008 Transact SQL). There’s an answer though: put the filter in the JOIN itself. Since this breaks my self-imposed style guide I usually comment it as shown to draw attention to the fact.

/*PLEASE REPLACE “SMART” SINGLE QUOTES BEFORE RUNNING THIS*/

SET NOCOUNT ON

CREATE TABLE Branch
(
BranchId int NOT NULL
)
INSERT INTO Branch
(
BranchId
)
VALUES(1),(2),(3)

CREATE TABLE BranchStaff
(
BranchId int NOT NULL
,Name nvarchar(20) NOT NULL
,Position nvarchar(20) NOT NULL)

INSERT INTO BranchStaff
(
BranchId
,Name
,Position
)
VALUES(1, ‘John Smith‘, ‘Branch Manager’),(2, ‘Arthur Jones’, ‘Sales Assistant’)

PRINT ‘***INNER JOIN:’

SELECT
B.BranchId

,Name
,Position
,‘Includes only rows matched in both tables.’ As Notes
FROM
Branch B
INNER JOIN BranchStaff S
ON B.BranchId = S.BranchId

PRINT ‘***Unfiltered LEFT OUTER JOIN:’

SELECT
B.BranchId
,Name
,Position
,CASE WHEN S.BranchId Is Not Null THEN ” ELSE ‘No match for this row in the main table so joined table columns padded with nulls.’ END As Notes
FROM
Branch B
LEFT OUTER JOIN BranchStaff S
ON B.BranchId = S.BranchId

PRINT ‘First Attempt – LEFT OUTER JOIN with filter in WHERE clause:’
SELECT
B.BranchId
,Name
,Position
,‘Acts like an Inner Join because filter excludes unmatched rows in joined table.’ As Notes
FROM
Branch B
LEFT OUTER JOIN BranchStaff S
ON B.BranchId = S.BranchId
WHERE S.Position = ‘Branch Manager’

PRINT ‘Second Attempt – Filter in the JOIN:’
SELECT *
,‘Correct! Includes all rows from main table plus filtered rows in joined table’ As Notes
FROM
Branch B
LEFT OUTER JOIN BranchStaff S
ON B.BranchId = S.BranchId
/*not equijoin*/
And S.Position = ‘Branch Manager’

DROP TABLE Branch
DROP TABLE BranchStaff