Question 89337 in PostgreSQL Question posted in
The official documentation can be found here.

How can I test whether these two queries are equivalent? – Postgresql

I’m wondering if the two queries below are equivalent to each other:

SELECT 
    messages1.id, messages1.channel 
FROM 
    (SELECT * 
     FROM messages 
     WHERE channel IN ('U560')) messages1 
LEFT JOIN 
    read_messages ON messages1.id = read_messages.message_id 
WHERE 
    read_messages.id IS NULL;

SELECT 
    "messages"."id", "messages"."channel" 
FROM 
    "messages" 
LEFT OUTER JOIN 
    "read_messages" ON ("messages"."id" = "read_messages"."message_id") 
WHERE 
    ("messages"."channel" IN ('U560') 
     AND "read_messages"."id" IS NULL)

How can I test that?

Tags:

2

Answers


  1. 0

    You can check the execution plan using the EXPLAIN keyword.

    https://www.postgresql.org/docs/current/sql-explain.html

    If the execution plans are the same, the queries are the same.

    Login or Signup to reply.
  2. 0

    You can take the suggestion by @MikeOrganek a step further. UNION those queries. See documentation for Combining Queries. If the result is empty set (no rows) then your original queries produce the same result and are therefore equivalent. If any rows are returned use the individual except queries to determine differences. HINT: Make [query 1] and [query 2] CTEs.

    with query_1 as 
     ( select messages1.id, messages1.channel 
         from (select * 
                 from messages 
                where channel in ('U560')
              ) messages1 
         left join read_messages
                on messages1.id = read_messages.message_id 
        where  read_messages.id is null
     )
   , with query_2
         ( select messages.id, messages.channel 
             from messages 
             left outer join  read_messages 
                     on (messages.id = read_messages.message_id) 
            where (messages.channel in ('U560') 
              and read_messages.id is null)
         )
select query_1 except query_2 
union all
select query_2 except query_1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search