Full join order by volume yields varying numbers of rows of results. Why?
I am working with two tables A
and B
. The table A
contains equity securities, and the table B
contains information about security.
For example, when B.Item = 5301
, the string indicates the price for a given security. When B.Item = 9999
, the line indicates the dividend for a given security. I am trying to get both price and dividend on the same line. For this, I FULL JOINed
table B
twice into a 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 rest of the fields in the join clause, such as Year_
, Freq
and Seq
, just make sure that the price and dividend dates are the same. A.Code
simply identifies a single security.
My problem is that when I reverse the order of all the joins, I get a different number of results. So if B.Item = 9999
preceded b.Item 2501
, I get one result. On the other hand, I am getting 2 results. I realized that the table B
has zero entries for security 122514
for dividends, but has two entries for price.
When the price is listed first, I get both prices and dividend fields null
. However, when the dividend is determined first, I get NULLs
for the dividend fields as well as NULLs
for the price fields.
Why are two price items not displayed? I expect them to do it inFULL JOIN
source to share
This is because your second FULL OUTER JOIN refers to your first FULL OUTER JOIN. This means that changing their order will fundamentally change the request.
Here's 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;
source to share