CODEWITHSUNDEEP
sqlsql-serverouter-join
user2353644
user2353644

Reputation: 55

Order of Full Outer Joins Yields Different Number of Result Rows..Why?

I'm working with two tables A and B. Table A identifies equity securities and Table B has a number of details on the security.

For example, when B.Item = 5301, the row specifies price for a given security. When B.Item = 9999, the row specifies dividends for a given security. I am trying to get both price and dividends in the same row. In order to achieve this, I FULL JOINed table B twice to table A. 

SELECT * 
FROM   a a 
       FULL JOIN (SELECT * 
                  FROM   b) b 
              ON b.code = a.code 
                 AND b.item = 3501 
       FULL JOIN (SELECT * 
                  FROM   b) b2 
              ON b2.code = a.code 
                 AND b.item = 9999 
                 AND b2.year_ = b.year_ 
                 AND b.freq = b2.freq 
                 AND b2.seq = b.seq 
WHERE  a.code IN ( 122514 )

The remaining fields in the join clause like Year_, Freq, and Seq just make sure the dates of the price and dividends match. A.Code simply identifies a single security.

My issue is that when I flip the order of the full joins I get a different number of results. So if b.Item = 9999 comes before b.Item 2501, I get one result. The other way around I get 2 results. I realized the table B has zero entries for security 122514 for dividend, but has two entries for price.

When price is specified first, I get both prices and dividend fields are null. However, when dividend is specified first, I get NULLs for the dividend fields and also nulls for the prices fields.

Why aren't the two price entries showing up? I would expect them to do so in a FULL JOIN

Upvotes: 3

Views: 70

Answers (1)

Richard Hansell
Richard Hansell

Reputation: 5403

It's because your second FULL OUTER JOIN refers to your first FULL OUTER JOIN. This means changing the order of them is making a fundamental change to the query.

Here is some pseudo-SQL that demonstrates how this works:

DECLARE @a TABLE (Id INT, Name VARCHAR(50));
INSERT INTO @a VALUES (1, 'Dog Trades');
INSERT INTO @a VALUES (2, 'Cat Trades');
DECLARE @b TABLE (Id INT, ItemCode VARCHAR(1), PriceDate DATE, Price INT, DividendDate DATE, Dividend INT);
INSERT INTO @b VALUES (1, 'p', '20141001', 100, '20140101', 1000);
INSERT INTO @b VALUES (1, 'p', '20141002', 50, NULL, NULL);
INSERT INTO @b VALUES (2, 'c', '20141001', 10, '20141001', 500);
INSERT INTO @b VALUES (2, 'c', NULL, NULL, '20141002', 300);

--Same results
SELECT a.*, b1.*, b2.* FROM @a a FULL OUTER JOIN @b b1 ON b1.Id = a.Id AND b1.ItemCode = 'p' FULL OUTER JOIN @b b2 ON b2.Id = a.Id AND b2.ItemCode = 'c';
SELECT a.*, b2.*, b1.* FROM @a a FULL OUTER JOIN @b b1 ON b1.Id = a.Id AND b1.ItemCode = 'c' FULL OUTER JOIN @b b2 ON b2.Id = a.Id AND b2.ItemCode = 'p';

--Different results
SELECT a.*, b1.*, b2.* FROM @a a FULL OUTER JOIN @b b1 ON b1.Id = a.Id AND b1.ItemCode = 'p' FULL OUTER JOIN @b b2 ON b2.Id = a.Id AND b2.ItemCode = 'c' AND b2.DividendDate = b1.PriceDate;
SELECT a.*, b2.*, b1.* FROM @a a FULL OUTER JOIN @b b1 ON b1.Id = a.Id AND b1.ItemCode = 'c' FULL OUTER JOIN @b b2 ON b2.Id = a.Id AND b2.ItemCode = 'p' AND b2.DividendDate = b1.PriceDate;

Upvotes: 3

Related Questions