psql.samples

gistfile1.txt

SELECT device.id, device.name, device.created_at FROM devices as device 
WHERE (
  (
    SELECT device_id FROM device_zones AS zones
    WHERE (zones.id IS NOT NULL AND zones.device_id = device.id)
    LIMIT 1
  ) IS NOT NULL
) LIMIT 4;

---

WITH uniq_devices AS (
 SELECT DISTINCT ON (device.id) device.id FROM devices as device JOIN device_zones as zones ON device.id = zones.device_id
)
SELECT id, name FROM devices WHERE id IN (SELECT id FROM uniq_devices);

---

SELECT id, name FROM devices WHERE id IN (SELECT DISTINCT ON (device.id) device.id FROM devices as device JOIN device_zones as zones ON device.id = zones.device_id);