this post was submitted on 10 Aug 2025
9 points (100.0% liked)

SQL

1051 readers
1 users here now

Related Fediverse communities:

Icon base by Delapouite under CC BY 3.0 with modifications to add a gradient

founded 2 years ago
MODERATORS
Ategon@programming.dev
jnovinger@programming.dev
9
Are these queries equivalent? (lemmy.ca)
submitted 1 week ago* (last edited 1 week ago) by roadrunner_ex@lemmy.ca to c/sql@programming.dev
14 comments fedilink hide all child comments
 

Putting aside any opinions on performance, I've been trying to test a notion about whether a couple queries would output the same data (ordering doesn't matter).

SELECT *
FROM articles
WHERE (
  last_updated >= %s
  OR id IN (1, 2, 3)
  )
  AND created_at IS NOT NULL

SELECT *
FROM articles
WHERE last_updated >= %s
  AND created_at IS NOT NULL
UNION
SELECT *
FROM articles
WHERE id IN (1, 2, 3)
  AND created_at IS NOT NULL

I think they're equivalent, but I can't prove it to myself.

Edit: Aye, looking at the replies, I'm becoming aware that I left out a couple key assumptions I've made. Assuming:

a) id is a PRIMARY KEY (or otherwise UNIQUE)

b) I mean equivalent insofar as "the rows returned will contain equivalent data same (though maybe ordered differently)"

you are viewing a single comment's thread
view the rest of the comments
[–] hex123456@sh.itjust.works 3 points 1 week ago* (last edited 1 week ago)

You can prove equivalence for yourself using the minus operator. Replace “select 1” and “select 2” with your queries.

Select * from (
(Select 1)
Minus
(Select 2)
)

——

Select * from (
(Select 2)
Minus
(Select 1)
)