MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE my_table (
    `id` INT PRIMARY KEY,
    `day` TINYINT NOT NULL,
    `date` DATE,
    INDEX idx_date_day(date, day)
);
INSERT INTO my_table VALUES
    ( 1, 1, NULL),
    ( 2, 2, NULL),
    ( 3, 3, NULL),
    ( 4, 4, NULL),
    ( 5, 5, NULL),
    ( 6, 6, NULL),
    ( 7, 7, NULL),
    ( 8, 3, '2024-05-29'),
    ( 9, 5, '2024-05-31'),
    (10, 1, '2024-06-17'),
    (11, 6, '2024-07-27'),
    (12, 6, '2024-08-10'),
    (13, 7, '2024-08-25'),
    (14, 5, '2024-09-06'),
    (15, 5, '2024-09-27'),
    (16, 7, '2024-09-29'),
    (17, 5, '2024-10-11'),
    (18, 6, '2024-10-19'),
    (19, 1, '2024-10-21');
ANALYZE TABLE my_table;
SELECT * FROM my_table;

Records: 19  Duplicates: 0  Warnings: 0




Table
Op
Msg_type
Msg_text


fiddle.my_table
analyze
status
OK




id
day
date


1
1
null

2
2
null

3
3
null

4
4
null

5
5
null

6
6
null

7
7
null

8
3
2024-05-29

9
5
2024-05-31

10
1
2024-06-17

11
6
2024-07-27

12
6
2024-08-10

13
7
2024-08-25

14
5
2024-09-06

15
5
2024-09-27

16
7
2024-09-29

17
5
2024-10-11

18
6
2024-10-19

19
1
2024-10-21


SELECT
    COALESCE(t2.id, t1.id) AS id,
    t1.day,
    COALESCE(t2.date, t1.date) AS date
FROM my_table t1
LEFT JOIN my_table t2 ON t1.day = t2.day AND t2.date = '2024-05-29'
WHERE t1.date IS NULL;


id
day
date


1
1
null

2
2
null

8
3
2024-05-29

4
4
null

5
5
null

6
6
null

7
7
null


EXPLAIN
SELECT
    COALESCE(t2.id, t1.id) AS id,
    t1.day,
    COALESCE(t2.date, t1.date) AS date
FROM my_table t1
LEFT JOIN my_table t2 ON t1.day = t2.day AND t2.date = '2024-05-29'
WHERE t1.date IS NULL;


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
t1
null
ref
idx_date_day
idx_date_day
4
const
7
100.00
Using where; Using index

1
SIMPLE
t2
null
ref
idx_date_day
idx_date_day
5
const,fiddle.t1.day
1
100.00
Using where; Using index


SELECT s1.*
FROM my_table as s1
INNER JOIN (
    SELECT day, MAX(date) as date_max
    FROM my_table
    WHERE my_table.date = '2024-05-29' OR my_table.date is null
    GROUP BY my_table.day
) as s2 ON s1.day = s2.day AND ( s1.date = s2.date_max or ( s1.date IS NULL AND s2.date_max IS NULL ) )
ORDER BY day ASC;


id
day
date


1
1
null

2
2
null

8
3
2024-05-29

4
4
null

5
5
null

6
6
null

7
7
null


EXPLAIN
SELECT s1.*
FROM my_table as s1
INNER JOIN (
    SELECT day, MAX(date) as date_max
    FROM my_table
    WHERE my_table.date = '2024-05-29' OR my_table.date is null
    GROUP BY my_table.day
) as s2 ON s1.day = s2.day AND ( s1.date = s2.date_max or ( s1.date IS NULL AND s2.date_max IS NULL ) )
ORDER BY day ASC;


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
PRIMARY
<derived2>
null
ALL
null
null
null
null
8
100.00
Using temporary; Using filesort

1
PRIMARY
s1
null
ref_or_null
idx_date_day
idx_date_day
5
s2.date_max,s2.day
2
100.00
Using where; Using index

2
DERIVED
my_table
null
ref_or_null
idx_date_day
idx_date_day
4
const
8
100.00
Using where; Using index; Using temporary


SELECT s1.*
FROM my_table as s1
INNER JOIN (
    SELECT day, MAX(date) as date_max
    FROM my_table
    WHERE my_table.date = '2024-05-29' OR my_table.date is null
    GROUP BY my_table.day
) as s2 ON s1.day = s2.day AND s1.date <=> s2.date_max
ORDER BY day ASC;


id
day
date


1
1
null

2
2
null

8
3
2024-05-29

4
4
null

5
5
null

6
6
null

7
7
null


EXPLAIN
SELECT s1.*
FROM my_table as s1
INNER JOIN (
    SELECT day, MAX(date) as date_max
    FROM my_table
    WHERE my_table.date = '2024-05-29' OR my_table.date is null
    GROUP BY my_table.day
) as s2 ON s1.day = s2.day AND s1.date <=> s2.date_max
ORDER BY day ASC;


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
PRIMARY
<derived2>
null
ALL
null
null
null
null
8
100.00
Using temporary; Using filesort

1
PRIMARY
s1
null
ref
idx_date_day
idx_date_day
5
s2.date_max,s2.day
1
100.00
Using where; Using index

2
DERIVED
my_table
null
ref_or_null
idx_date_day
idx_date_day
4
const
8
100.00
Using where; Using index; Using temporary

id	day	date
1	1	null
2	2	null
8	3	2024-05-29
4	4	null
5	5	null
6	6	null
7	7	null

id	day	date
1	1	null
2	2	null
8	3	2024-05-29
4	4	null
5	5	null
6	6	null
7	7	null

id	day	date
1	1	null
2	2	null
8	3	2024-05-29
4	4	null
5	5	null
6	6	null
7	7	null

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	null	ref	idx_date_day	idx_date_day	4	const	7	100.00	Using where; Using index
1	SIMPLE	t2	null	ref	idx_date_day	idx_date_day	5	const,fiddle.t1.day	1	100.00	Using where; Using index

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	null	ALL	null	null	null	null	8	100.00	Using temporary; Using filesort
1	PRIMARY	s1	null	ref_or_null	idx_date_day	idx_date_day	5	s2.date_max,s2.day	2	100.00	Using where; Using index
2	DERIVED	my_table	null	ref_or_null	idx_date_day	idx_date_day	4	const	8	100.00	Using where; Using index; Using temporary