CODEWITHSUNDEEP
postgresql
GrahamB
GrahamB

Reputation: 569

Postgres join not respecting outer where clause

In SQL Server, I know for sure that the following query;

SELECT things.*
FROM things
LEFT OUTER JOIN (
    SELECT  thingreadings.thingid, reading 
    FROM thingreadings 
    INNER JOIN things on thingreadings.thingid = things.id 
    ORDER BY reading DESC LIMIT 1) AS readings

ON things.id = readings.thingid 
WHERE things.id = '1'

Would join against thingreadings only once the WHERE id = 1 had restricted the record set down. It left joins against just one row. However in order for performance to be acceptable in postgres, I have to add the WHERE id= 1 to the INNER JOIN things on thingreadings.thingid = things.id line too.

This isn't ideal; is it possible to force postgres to know that what I am joining against is only one row without explicitly adding the WHERE clauses everywhere?

An example of this problem can be seen here;

I am trying to recreate the following query in a more efficient way;

SELECT things.id, things.name,
(SELECT thingreadings.id      FROM thingreadings WHERE thingid = things.id ORDER BY id DESC LIMIT 1),
(SELECT thingreadings.reading FROM thingreadings WHERE thingid = things.id ORDER BY id DESC LIMIT 1)

FROM things
WHERE id IN (1,2)

http://sqlfiddle.com/#!15/a172c/2

Upvotes: 2

Views: 150

Answers (2)

Filipe Silva
Filipe Silva

Reputation: 21657

Not really sure why you did all that work. Isn't the inner query enough?

SELECT  t.* 
FROM thingreadings tr 
INNER JOIN things t on tr.thingid = t.id AND t.id = '1'
ORDER BY tr.reading DESC 
LIMIT 1;

sqlfiddle demo

When you want to select the latest value for each thingID, you can do:

SELECT t.*,a.reading 
FROM things t
INNER JOIN (
SELECT t1.*
  FROM thingreadings t1
    LEFT JOIN thingreadings t2
      ON (t1.thingid = t2.thingid AND t1.reading < t2.reading)
  WHERE t2.thingid IS NULL
) a ON a.thingid = t.id

sqlfiddle demo

The derived table gets you the record with the most recent reading, then the JOIN gets you the information from things table for that record.

Upvotes: 1

John Chrysostom
John Chrysostom

Reputation: 3973

The where clause in SQL applies to the result set you're requesting, NOT to the join.

What your code is NOT saying: "do this join only for the ID of 1"...

What your code IS saying: "do this join, then pull records out of it where the ID is 1"...

This is why you need the inner where clause. Incidentally, I also think Filipe is right about the unnecessary code.

Upvotes: 0

Related Questions