How can I do a left outer join where both tables have a where clause?

Question

Here's the scenario:

I have 2 tables:

CREATE TABLE dbo.API_User
    (
    id int NOT NULL,
    name nvarchar(255) NOT NULL,
    authorization_key varchar(255) NOT NULL,
    is_active bit NOT NULL
    )  ON [PRIMARY]

CREATE TABLE dbo.Single_Sign_On_User
    (
    id int NOT NULL IDENTITY (1, 1),
    API_User_id int NOT NULL,
    external_id varchar(255) NOT NULL,
    user_id int NULL
    )  ON [PRIMARY]

What I am trying to return is the following:

1. is_active for a given authorization_key
2. The Single_Sign_On_User.id that matches the external_id/API_User_id pair if it exists or NULL if there is no such pair

When I try this query:

SELECT Single_Sign_On_User.id, API_User.is_active
FROM API_User LEFT OUTER JOIN
    Single_Sign_On_User ON Single_Sign_On_User.API_User_id = API_User.id
WHERE     
    Single_Sign_On_User.external_id = 'test_ext_id' AND 
    API_User.authorization_key = 'test'

where the "test" API_User record exists but the "test_ext_id" record does not, and with no other values in either table, I get no records returned.

When I use:

SELECT Single_Sign_On_User.id, API_User.is_active
FROM API_User LEFT OUTER JOIN
    Single_Sign_On_User ON Single_Sign_On_User.API_User_id = API_User.id
WHERE     
    API_User.authorization_key = 'test'

I get the results I expect (NULL, 1), but that query doesn't allow me to find the "test_ext_id" record if it exists but would give me all records associated with the "test" API_User record.

How can I get the results I am after?

Answer 1

Filtering on the "outer" table" changes the join to INNER.

4 options, of which the first 2 are best

• derived table

Filter inside the derived table

SELECT SSOU.id, API_User.is_active
FROM
    API_User
    LEFT OUTER JOIN
    (
    SELECT id FROM Single_Sign_On_User WHERE external_id = 'test_ext_id'
    ) SSOU  ON SSOU.API_User_id = API_User.id
WHERE     
    API_User.authorization_key = 'test'

• CTE (in place of a derived table) for SQL Server 2005+

• filter in JOIN

thus;

SELECT Single_Sign_On_User.id, API_User.is_active
FROM API_User LEFT OUTER JOIN
    Single_Sign_On_User ON Single_Sign_On_User.API_User_id = API_User.id

    AND Single_Sign_On_User.external_id = 'test_ext_id'

WHERE     
    API_User.authorization_key = 'test'

• OR clause

Thus:

SELECT Single_Sign_On_User.id, API_User.is_active
FROM API_User LEFT OUTER JOIN
    Single_Sign_On_User ON Single_Sign_On_User.API_User_id = API_User.id
WHERE     
    (Single_Sign_On_User.external_id = 'test_ext_id' OR Single_Sign_On_User.external_id IS NULL)
    AND
    API_User.authorization_key = 'test'

Answer 2

I'd use gbn's example with the condition in the LEFT JOIN, but also this is equivalent to the OR and can sometimes be better in code generation (no pesky brackets to keep track of with the OR):

SELECT Single_Sign_On_User.id
       ,API_User.is_active
FROM API_User
LEFT OUTER JOIN Single_Sign_On_User
    ON Single_Sign_On_User.API_User_id = API_User.id
WHERE COALESCE(Single_Sign_On_User.external_id, 'test_ext_id') = 'test_ext_id'
    AND API_User.authorization_key = 'test'