M
M
mkone1122021-06-07 16:14:39
PostgreSQL
mkone112, 2021-06-07 16:14:39

What is the difference between a join of two tables and a join with a subquery?

There are two requests:

Original request:
SELECT *
FROM main_thinghistory
JOIN (
    SELECT main_thingownershiphistory.thing_id AS thing_id,
           date(main_thingownershiphistory.date_start) AS dt
    FROM main_thingownershiphistory
    JOIN main_thing
      ON main_thing.id = main_thingownershiphistory.thing_id
     AND main_thing.status IN (9, 4, 5)
     AND main_thingownershiphistory.is_deleted = false
     AND main_thingownershiphistory.date_start < 2020-06-02 00:00:00
     AND main_thingownershiphistory.old_owner_id IS NOT NULL
    JOIN main_legalperson
      ON main_legalperson.id = main_thingownershiphistory.owner_id
     AND main_legalperson.is_service_company = true
    WHERE main_thing.office_id != 210
    ) AS anon_1
ON main_thinghistory.thing_id = anon_1.thing_id
   AND main_thinghistory.history_date = anon_1.dt
   AND main_thinghistory.history_date >= 2012-01-01
   AND main_thinghistory.history_date <= 2020-06-01
   AND main_thinghistory.is_deleted = false

Rewritten query:

SELECT *
FROM main_thingownershiphistory
INNER JOIN main_thing
   ON (main_thingownershiphistory.thing_id = main_thing.id)
INNER JOIN main_legalperson ON (main_thingownershiphistory.owner_id = main_legalperson.id)
INNER JOIN main_thinghistory ON (main_thing.id = main_thinghistory.thing_id)
WHERE NOT (main_thingownershiphistory.is_deleted = True)
  AND main_thing.status IN (9, 4, 5)
  AND main_thingownershiphistory.date_start < 2020-06-02 00:00:00
  AND main_legalperson.is_service_company = True
  AND NOT main_thingownershiphistory.old_owner_id IS NULL
  AND NOT main_thing.office_id = 210
  AND main_thinghistory.history_date = main_thingownershiphistory.date_start
  AND main_thinghistory.history_date >= 2012-01-01
  AND main_thinghistory.history_date <= 2020-06-01
  AND main_thinghistory.is_deleted = False

The question is why these queries are not equivalent?

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question