CODEWITHSUNDEEP
sqldatabasepostgresql
AmirReza
AmirReza

Reputation: 365

How to find out if the result of two separate queries match?

I'm trying to find out if the result of the two queries match. We can write queries in different shapes, but is there a way to find out that they do match? for example the below queries don't match, but is there any function that returns false if they don't? the queries are as follows:

SELECT * FROM orders JOIN (
    SELECT * FROM customers WHERE state IN ('OH', 'NY', 'OR')
) AS ord USING (customerid)
ORDER BY orderid

and

SELECT c.firstname, c.lastname, o.orderid 
FROM orders AS o, (
    SELECT customerid, state, firstname, lastname
    FROM customers
) AS c
WHERE  o.customerid = c.customerid AND 
c.state IN ('NY', 'OH', 'OR')
ORDER BY o.orderid;

I'm using Postgresql and I need to know if these queries are identical in the results

Upvotes: 0

Views: 39

Answers (1)

Anwesh Mohapatra
Anwesh Mohapatra

Reputation: 127

Use the EXISTS operator combined with full outer join. 

SELECT
  CASE
    WHEN EXISTS (
      SELECT
        *
      FROM (QUERY1) a NATURAL FULL
      JOIN (QUERY2)
    ) THEN 'different'
    ELSE 'same'
  END AS result;

Replace QUERY1 and QUERY2 with those select statements.

Source

Upvotes: 1

Related Questions