CODEWITHSUNDEEP
sqlsql-server
Chad
Chad

Reputation: 24679

LEFT OUTER JOINs not acting as expected

QUERY #1

SELECT     
     dbo.CLIENT.CLIENT_ID, dbo.CLIENT.GOC, dbo.SALES_UW_REGION.SALES_UNDERWRITING
FROM         dbo.CLIENT LEFT OUTER JOIN
                      dbo.SALES_UW_REGION ON dbo.CLIENT.GOC = dbo.SALES_UW_REGION.GOC
WHERE     (dbo.CLIENT.CLIENT_ID = 23721)

CLIENT_ID, GOC, SALES_UNDERWRITING
23721   332 Underwriting
23721   332 Sales

I can understand why this would return only one row, the reason being that despite the LEFT outer join which ensures both CLIENT records are returned even if they are unmatched, the FILTER is applied AFTER the join, so the resultset only has one row.

Query #2

SELECT     
     dbo.CLIENT.CLIENT_ID, dbo.CLIENT.GOC, dbo.SALES_UW_REGION.SALES_UNDERWRITING
FROM         dbo.CLIENT LEFT OUTER JOIN
                      dbo.SALES_UW_REGION ON dbo.CLIENT.GOC = dbo.SALES_UW_REGION.GOC                      
WHERE     (dbo.CLIENT.CLIENT_ID = 23721)
and SALES_UW_REGION.SALES_UNDERWRITING = 'Sales '

CLIENT_ID   GOC SALES_UNDERWRITING
23721   332 Sales

However, if I move the 'SALES' filter to the JOIN clause, I was susprised to see that still one row is returned.

Query #3

SELECT     
     dbo.CLIENT.CLIENT_ID, dbo.CLIENT.GOC, dbo.SALES_UW_REGION.SALES_UNDERWRITING
FROM         dbo.CLIENT LEFT OUTER JOIN
                      dbo.SALES_UW_REGION ON dbo.CLIENT.GOC = dbo.SALES_UW_REGION.GOC                      
                      and SALES_UW_REGION.SALES_UNDERWRITING = 'Sales '
WHERE     (dbo.CLIENT.CLIENT_ID = 23721)

CLIENT_ID   GOC SALES_UNDERWRITING
23721   332 Sales

I expected that since it was part of the JOIN clause and the join was a LEFT OUTER, that I would have gotten 2 rows. In general, if the JOIN condition which involves an equality match on two columns, one from the LEFT table and one from teh RIGHT table is not met, in a LEFT OUTER JOIN the LEFT table would be returned with NULL values for values selected from the RIGHT. Why should it be any different if we are matching a value from the RIGHT table to a literal? Shouldn't the row from teh LEFT table still be returned?

Man, I thought this was basic stuff that I had down....

Here's what you need to recreate what I did:

CREATE TABLE [dbo].[CLIENT](
    [CLIENT_ID] [bigint] NOT NULL,
    [GOC] [char](3) NULL
) 
go

CREATE TABLE [dbo].[SALES_UW_REGION](
    [GOC] [char](3) NOT NULL,
    [SALES_UNDERWRITING] [varchar](12) NULL
) 
go


INSERT INTO [dbo].[CLIENT]([CLIENT_ID], [GOC])
SELECT 23721, N'332'
go

INSERT INTO [dbo].[SALES_UW_REGION]([GOC], [SALES_UNDERWRITING])
SELECT N'332', N'Underwriting' UNION ALL
SELECT N'332', N'Sales'
go

Upvotes: 1

Views: 215

Answers (3)

Joel Coehoorn
Joel Coehoorn

Reputation: 415735

I think your confusion involves a misunderstanding of the first result set:

CLIENT_ID, GOC, SALES_UNDERWRITING
23721   332 Underwriting
23721   332 Sales

Though there are two rows in this result set, the results represent data from only one row in the Client table. The join condition allowed the single row from the Client table to match two rows in the Sales_UW_Region table, and so the data for that row from the Client table is duplicated in the result set. There is only one Client record here in the first place, evidenced by the fact that there is only one Client_ID, but the data for the record is shown twice: once for each matching record in Sales_UW_Region.

Later, when you include the and SALES_UW_REGION.SALES_UNDERWRITING = 'Sales ' condition as part of the join's ON clause, the original single record in the Client table only matches one record from the Sales_UW_Region table. The data for the row no longer needs to be duplicated, and so only one row is returned.

Upvotes: 2

Hart CO
Hart CO

Reputation: 34774

You have one row in your CLIENT table for CLIENT_ID = 23721.

You're left joining the other table which has multiple rows for CLIENT_ID = 23721 (when joined with criteria in your first query), but when the joining criteria is altered it excludes one of those rows from the RIGHT table.

You're getting all records from your LEFT table regardless of whether they join to records in the RIGHT table, just as expected.

Upvotes: 3

David
David

Reputation: 34563

Your understanding is correct. If the filter is within the JOIN's ON clause, it will not remove rows from the first table.

I think you need to look elsewhere for your problem... Do you really mean to have a trailing space in your 'Sales ' constant?

Here's a JSFiddle to test this: http://sqlfiddle.com/#!2/bfe32/3/0

Upvotes: 0

Related Questions