PostgreSQL: Outer and self join

Question

I want to do an outer join on the same table, but I'm getting unexpected results.

This is what my "stock" table looks like:

price_timestamp,            security,   price_type, open,  high, low,  close
"2014-05-01 00:00:00-07";"SPY US EQUITY";"ASK";     188.54;188. 57;188.54;188.57
"2014-05-01 07:59:00-07";"SPY US EQUITY";"ASK";     188.72;188. 72;188.72;188.72
"2014-05-01 08:01:00-07";"SPY US EQUITY";"ASK";     188.71;188. 72;188.71;188.72
"2014-05-01 13:30:00-07";"SPY US EQUITY";"TRADE";   188.22;188. 27;188.21;188.26
"2014-05-01 13:31:00-07";"SPY US EQUITY";"TRADE";   188.27;188. 35;188.26;188.35
...

price_type can be BID, ASK or TRADE. I want a query that returns price_timestamp, security, bid price (which would be close price where price_type = 'BID'), and trade price (which would be close price where price_type = 'TRADE).

This is what I have:

SELECT b.price_timestamp, b.security, b.close AS bid, t.close AS trade
  FROM stock b
  FULL OUTER JOIN stock t
    ON b.price_timestamp = t.price_timestamp
   AND b.security = t.security
 WHERE b.price_type = 'BID'
   AND t.price_type = 'TRADE'

This returns 19370 records. There are 40147 bid prices, 19399 trade prices, so I'm expecting at least max(40147, 19399) = 40147 records. At a glance, it looks it's returning an INNER JOIN.

I've also tried moving "b.security = t.security" in the ON cluse to the WHERE clause -- no luck.

Although I don't really understand what you're trying to do, I doubt you want to use a full outer join, if anything just on the basis that for rows of t where not in b, the only column that would have values returned in that query is the 'trade' column, which would seemingly not communicate anything, I imagine. Can you post a few rows of data and the expected result based on those rows? (as an example) — Brian DeMilia
– Brian DeMilia, Commented Jul 20, 2014 at 0:21
Yes I wanted a full outer join so I don't omit a row just because ask (for example) had missing data. — Agrim Pathak
– Agrim Pathak, Commented Jul 20, 2014 at 1:45

Haleemur Ali · Accepted Answer · 2014-07-20 00:19:13Z

3

You're getting fewer records because on the where clause you're referring both aliases 'b'& 't'. So, it is filtering only those joined records where b.price_type = 'BID' and t.price_type = 'TRADE'

Try moving the where conditions into the joins

For example:

SELECT b.price_timestamp, b.security, b.close AS bid, t.close AS trade
  FROM stock b
  FULL OUTER JOIN stock t
    ON b.price_timestamp = t.price_timestamp
   AND b.security = t.security
   AND t.price_type = 'TRADE'
 WHERE b.price_type = 'BID'

This would return all records from alias 'b' where b.price_type = 'BID', and at least one record from alias 't'. If no matching records are found from 't', a null records will be returned.

answered Jul 20, 2014 at 0:19

Haleemur Ali

28.6k6 gold badges67 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: Outer and self join

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related