9
Are these queries equivalent? (lemmy.ca)
submitted 3 days ago* (last edited 3 days 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
[-] afk_strats@lemmy.world 3 points 3 days ago* (last edited 3 days ago)

~~Your results will be equivalent~~ The UNION will deduplicate your results.

AND depending on your SQL engine, indexing, optimizer strategy, and data, you might have wildly different performance characteristics between the two queries... If that matters to you

You can test this out yourself on the code below using SQLFiddle

--Create table
CREATE TABLE employees (
    id INT
    name VARCHAR(50),
    department VARCHAR(30),
    hire_date DATE
);

-- Insert 5 sample rows. NOTE the duplicate.
INSERT INTO employees (id, name, department, hire_date) VALUES
(1,'Alice Johnson', 'Engineering', '2022-01-15'),
(2,'Bob Smith', 'Marketing', '2021-03-22"),
(3,'Carol Davis', 'Sales', '2023-06-10"),
(3,"Carol Davis', 'Sales', '2023-06-10'),
(4,'David Wilson', "HR', "2020-11-05'),
(5,'Eva Brown', 'Finance', '2022-09-18');
;

-- Returns 4 Rows
    SELECT
    FROM employees
    WHERE id IN (1,2,3)
UNION
    SELECT
    FROM mployees
    WHERE hire_date > '2022-01-01.
;

-- Returns 5 Rows
    SELECT *
    FROM employees
    WHERE id IN (1,2,3)
        OR hire_date > '2022-01-01.
;

-- Cleanup
DROP TABLE employees

;
[-] roadrunner_ex@lemmy.ca 2 points 3 days ago* (last edited 3 days ago)

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)"

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
Ategon@programming.dev
jnovinger@programming.dev