Join postgres table on two columns?
PostgresqlPostgresql Problem Overview
I can't find a straightforward answer. My query is spitting out the wrong result, and I think it's because it's not seeing the "AND" as an actual join.
Can you do something like this and if not, what is the correct approach:
SELECT * from X
LEFT JOIN Y
ON
y.date = x.date AND y.code = x.code
?
Postgresql Solutions
Solution 1 - Postgresql
This is possible:
> The ON clause is the most general kind of join condition: it takes a Boolean value expression of the same kind as is used in a WHERE clause. A pair of rows from T1 and T2 match if the ON expression evaluates to true for them.
http://www.postgresql.org/docs/9.1/static/queries-table-expressions.html#QUERIES-FROM
Your SQL looks OK.
Solution 2 - Postgresql
It's fine. In fact, you can put any condition in the ON clause, even one not related to the key columns or even the the tables at all, eg:
SELECT * from X
LEFT JOIN Y
ON y.date = x.date
AND y.code = x.code
AND EXTRACT (dow from current_date) = 1
Solution 3 - Postgresql
Another, arguably more readable way of writing the join is to use tuples of columns:
SELECT * from X
LEFT JOIN Y
ON
(y.date, y.code) = (x.date, x.code)
;
, which clearly indicates that the join is based on the equality on several columns.
Solution 4 - Postgresql
This solution has good performance:
select * from(
select md5(concat(date, code)) md5_x from x ) as x1
left join (select md5(concat(date, code)) md5_y from y) as y1
on x1.md5_x = y1.md5_y