Since version 8.0.31, MySQL supports INTERSECT and EXCEPT. I don't know how I missed that, but finally. It is a part of MySQL now.

To test it I ran MySQL docker container.

Here are the schema and data:

CREATE TABLE `counters` (
  `name` varchar(32) DEFAULT NULL,
  `unread_messages` int DEFAULT NULL,
  `orders` int DEFAULT NULL
) ENGINE=InnoDB

INSERT INTO test.counters (name, unread_messages, orders) VALUES ('John Doe', null, 10);
INSERT INTO test.counters (name, unread_messages, orders) VALUES ('James Bond', 5, 1);
INSERT INTO test.counters (name, unread_messages, orders) VALUES ('Alex', 4, 5);
INSERT INTO test.counters (name, unread_messages, orders) VALUES ('Foo', null, 10);
INSERT INTO test.counters (name, unread_messages, orders) VALUES ('Bar', 10, null);
INSERT INTO test.counters (name, unread_messages, orders) VALUES ('Zip', null, null);

mysql> SELECT * FROM counters;
+------------+-----------------+--------+
| name       | unread_messages | orders |
+------------+-----------------+--------+
| John Doe   |            NULL |     10 |
| James Bond |               5 |      1 |
| Alex       |               4 |      5 |
| Foo        |            NULL |     10 |
| Bar        |              10 |   NULL |
| Zip        |            NULL |   NULL |
+------------+-----------------+--------+
6 rows in set (0.01 sec)

And let's try to intersect :)

mysql> SELECT * FROM counters WHERE unread_messages > 0
    -> INTERSECT
    -> SELECT * FROM counters WHERE orders > 0;
+------------+-----------------+--------+
| name       | unread_messages | orders |
+------------+-----------------+--------+
| James Bond |               5 |      1 |
| Alex       |               4 |      5 |
+------------+-----------------+--------+
2 rows in set (0.00 sec)

Nice! It is exactly what I expected. Then EXCEPT :

mysql> SELECT * FROM counters where unread_messages > 0 EXCEPT SELECT * FROM counters WHERE orders > 0;
+------+-----------------+--------+
| name | unread_messages | orders |
+------+-----------------+--------+
| Bar  |              10 |   NULL |
+------+-----------------+--------+
1 row in set (0.00 sec)

Wow! Finally. Also interesting to see the execution plan

mysql> EXPLAIN SELECT * FROM counters where unread_messages > 0 INTERSECT SELECT * FROM counters WHERE orders > 0\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: counters
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 33.33
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: INTERSECT
        table: counters
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 33.33
        Extra: Using where
*************************** 3. row ***************************
           id: 3
  select_type: INTERSECT RESULT
        table: <intersect1,2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Using temporary

let's add two indexes on unread_messages and orders

CREATE INDEX counters_orders_index
    on counters (orders);

CREATE INDEX counters_unread_messages_index
    on counters (unread_messages);

and let's see the difference

mysql> EXPLAIN SELECT * FROM counters WHERE unread_messages > 0 INTERSECT SELECT * FROM counters WHERE orders > 0 \G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: counters
   partitions: NULL
         type: range
possible_keys: counters_unread_messages_index
          key: counters_unread_messages_index
      key_len: 5
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index condition
*************************** 2. row ***************************
           id: 2
  select_type: INTERSECT
        table: counters
   partitions: NULL
         type: range
possible_keys: counters_orders_index
          key: counters_orders_index
      key_len: 5
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using index condition
*************************** 3. row ***************************
           id: 3
  select_type: INTERSECT RESULT
        table: <intersect1,2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Using temporary

And for EXCEPT :

mysql> EXPLAIN SELECT * FROM counters where unread_messages > 0 EXCEPT SELECT * FROM counters WHERE orders > 0 \G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: counters
   partitions: NULL
         type: range
possible_keys: counters_unread_messages_index
          key: counters_unread_messages_index
      key_len: 5
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index condition
*************************** 2. row ***************************
           id: 2
  select_type: EXCEPT
        table: counters
   partitions: NULL
         type: range
possible_keys: counters_orders_index
          key: counters_orders_index
      key_len: 5
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using index condition
*************************** 3. row ***************************
           id: 3
  select_type: EXCEPT RESULT
        table: <except1,2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Using temporary

It is great that MySQL finally supports it. To be honest, I am really happy that I will be able to rewrite my INNER JOIN hacks :)