9
submitted 3 days ago* (last edited 3 days ago) by roadrunner_ex@lemmy.ca to c/sql@programming.dev

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
[-] RobertTableaux@programming.dev 9 points 3 days ago

To answer the question asked, yes, these will “out the same data”. Anyone telling you they’re not the same isn’t looking at what you care about being the same - the rows output (regardless of order). From that perspective, and disregarding performance (as you also mentioned in your question) these queries will give you the same results.

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

SQL

1034 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