MariaDB 10.3

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


10.3.36-MariaDB-1:10.3.36+maria~deb10-log


create table paddocks (pd char(1) primary key, sz enum('Small', 'Big'), key (sz));
create table locks (pd char(1), lk char(2), key (pd,lk));
insert into paddocks values ('A', 'Small'), ('B', 'Small'),('C', 'Small'), ('D', 'Small'),('E', 'Small'), ('F', 'Big')

Records: 6  Duplicates: 0  Warnings: 0


insert into locks values ('A', 'KL'),('A', 'OK'),('A', 'CZ'),('A', 'CZ'),
('B', 'OK'),('B', 'OK'),
('C', 'OK'),('C', 'CZ'), ('C', 'KL'),
('D', 'RO'),('D', 'CZ'), ('D', 'CZ'),
('E', 'OK'),('E', 'OK'), ('E', 'KL'), ('E', 'KL'),
('F', 'OK'),('F', 'OK'), ('F', 'CZ'), ('F', 'KL');

Records: 20  Duplicates: 0  Warnings: 0


create temporary table ky (lk char(2), key (lk));
insert into ky values ('KL'),('OK'),('CZ'),('OK'),('CZ')

Records: 5  Duplicates: 0  Warnings: 0


select pd, locks.lk, count(locks.lk)
from paddocks
join locks using (pd)
where sz = 'Small'
group by pd, locks.lk


pd
lk
count(locks.lk)


A
CZ
2

A
KL
1

A
OK
1

B
OK
2

C
CZ
1

C
KL
1

C
OK
1

D
CZ
2

D
RO
1

E
KL
2

E
OK
2


select pd, p.lk, p.c,count(ky.lk) as kc

  from
  (select pd, locks.lk as lk, count(locks.lk) as c
from paddocks
join locks using (pd)
where sz = 'Small'
group by pd, locks.lk) p 
left join ky using (lk)
group by pd,lk


pd
lk
c
kc


A
CZ
2
2

A
KL
1
1

A
OK
1
2

B
OK
2
2

C
CZ
1
2

C
KL
1
1

C
OK
1
2

D
CZ
2
2

D
RO
1
0

E
KL
2
1

E
OK
2
2


 select pd
  from
  (
select pd, p.lk, p.c,count(ky.lk) as kc

  from
  (select pd, locks.lk as lk, count(locks.lk) as c
from paddocks
join locks using (pd)
where sz = 'Small'
group by pd, locks.lk) p 
left join ky using (lk)
group by pd,lk) p
group by pd
having count(pd) = sum(c <= kc)


pd


A

B

C


analyze format=json select pd
  from
  (
select pd, p.lk, p.c,count(ky.lk) as kc

  from
  (select pd, locks.lk as lk, count(locks.lk) as c
from paddocks
join locks using (pd)
where sz = 'Small'
group by pd, locks.lk) p 
left join ky using (lk)
group by pd,lk) p
group by pd
having count(pd) = sum(c <= kc)


ANALYZE


{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 1.1581,
    "having_condition": "count(p.pd) = sum(p.c <= p.kc)",
    "filesort": {
      "sort_key": "p.pd",
      "r_loops": 1,
      "r_total_time_ms": 0.0037,
      "r_used_priority_queue": false,
      "r_output_rows": 3,
      "r_buffer_size": "255",
      "temporary_table": {
        "table": {
          "table_name": "<derived2>",
          "access_type": "ALL",
          "r_loops": 1,
          "rows": 5,
          "r_rows": 11,
          "r_total_time_ms": 0.0068,
          "filtered": 100,
          "r_filtered": 100,
          "materialized": {
            "query_block": {
              "select_id": 2,
              "r_loops": 1,
              "r_total_time_ms": 0.1335,
              "const_condition": "1",
              "filesort": {
                "sort_key": "p.pd, p.lk",
                "r_loops": 1,
                "r_total_time_ms": 0.0062,
                "r_used_priority_queue": false,
                "r_output_rows": 11,
                "r_buffer_size": "420",
                "temporary_table": {
                  "table": {
                    "table_name": "<derived3>",
                    "access_type": "ALL",
                    "r_loops": 1,
                    "rows": 5,
                    "r_rows": 11,
                    "r_total_time_ms": 0.0029,
                    "filtered": 100,
                    "r_filtered": 100,
                    "materialized": {
                      "query_block": {
                        "select_id": 3,
                        "r_loops": 1,
                        "r_total_time_ms": 0.0656,
                        "filesort": {
                          "sort_key": "paddocks.pd, `locks`.lk",
                          "r_loops": 1,
                          "r_total_time_ms": 0.0072,
                          "r_used_priority_queue": false,
                          "r_output_rows": 11,
                          "r_buffer_size": "420",
                          "temporary_table": {
                            "table": {
                              "table_name": "paddocks",
                              "access_type": "ref",
                              "possible_keys": ["PRIMARY", "sz"],
                              "key": "sz",
                              "key_length": "2",
                              "used_key_parts": ["sz"],
                              "ref": ["const"],
                              "r_loops": 1,
                              "rows": 5,
                              "r_rows": 5,
                              "r_total_time_ms": 0.0071,
                              "filtered": 100,
                              "r_filtered": 100,
                              "attached_condition": "paddocks.sz = 'Small'",
                              "using_index": true
                            },
                            "table": {
                              "table_name": "locks",
                              "access_type": "ref",
                              "possible_keys": ["pd"],
                              "key": "pd",
                              "key_length": "2",
                              "used_key_parts": ["pd"],
                              "ref": ["fiddle.paddocks.pd"],
                              "r_loops": 5,
                              "rows": 1,
                              "r_rows": 3.2,
                              "r_total_time_ms": 0.0164,
                              "filtered": 100,
                              "r_filtered": 100,
                              "using_index": true
                            }
                          }
                        }
                      }
                    }
                  },
                  "table": {
                    "table_name": "ky",
                    "access_type": "ref",
                    "possible_keys": ["lk"],
                    "key": "lk",
                    "key_length": "3",
                    "used_key_parts": ["lk"],
                    "ref": ["p.lk"],
                    "r_loops": 11,
                    "rows": 1,
                    "r_rows": 1.5455,
                    "r_total_time_ms": 0.0232,
                    "filtered": 100,
                    "r_filtered": 100,
                    "attached_condition": "trigcond(trigcond(p.lk is not null))",
                    "using_index": true
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}


explain extended select pd
  from
  (
select pd, p.lk, p.c,count(ky.lk) as kc

  from
  (select pd, locks.lk as lk, count(locks.lk) as c
from paddocks
join locks using (pd)
where sz = 'Small'
group by pd, locks.lk) p 
left join ky using (lk)
group by pd,lk) p
group by pd
having count(pd) = sum(c <= kc)


id
select_type
table
type
possible_keys
key
key_len
ref
rows
filtered
Extra


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

2
DERIVED
<derived3>
ALL
null
null
null
null
5
100.00
Using temporary; Using filesort

2
DERIVED
ky
ref
lk
lk
3
p.lk
1
100.00
Using where; Using index

3
DERIVED
paddocks
ref
PRIMARY,sz
sz
2
const
5
100.00
Using where; Using index; Using temporary; Using filesort

3
DERIVED
locks
ref
pd
pd
2
fiddle.paddocks.pd
1
100.00
Using index
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	ALL	null	null	null	null	5	100.00	Using temporary; Using filesort
2	DERIVED	<derived3>	ALL	null	null	null	null	5	100.00	Using temporary; Using filesort
2	DERIVED	ky	ref	lk	lk	3	p.lk	1	100.00	Using where; Using index
3	DERIVED	paddocks	ref	PRIMARY,sz	sz	2	const	5	100.00	Using where; Using index; Using temporary; Using filesort
3	DERIVED	locks	ref	pd	pd	2	fiddle.paddocks.pd	1	100.00	Using index