SQL

1051 readers

1 users here now

Related Fediverse communities:

#sql on Mastodon
#postgresql on Mastodon
c/PostgreSQL on programming.dev

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

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

[–] Deebster@programming.dev 1 points 1 week ago

That won't show if results are equivalent, only if the query plans are matching, which they won't be (at least before the SQL engine's optimisations).