-- see https://www.percona.com/blog/2020/04/16/sql-optimizations-in-postgresql-in-vs-exists-vs-any-all-vs-join/
--
-- Au total 13 variantes du calcul de la différence de deux relations :
-- - 3 variantes sur EXCEPT
-- - 4 variantes NOT IN
-- - 2 variantes OUTER JOIN
-- - 2 variantes NOT EXISTS
-- - 2 variantes ANY/ALL
--
\echo '---------------- Variante 1A : EXCEPT [DISTINCT] ------------------'
\echo '----------- /!\\ sémantique ensembliste "DISTINCT" /!\\ -------------'
EXPLAIN ANALYZE
SELECT
    id_1
FROM
    table_1
    -- le mot clef DISTINCT est optionnel ici
EXCEPT
DISTINCT
SELECT
    id_2
FROM
    table_2;

\echo '---------------- Variante 1B : DISTINCT EXCEPT DISTINCT -----------'
EXPLAIN ANALYZE SELECT DISTINCT
    id_1
FROM
    table_1
EXCEPT
DISTINCT SELECT DISTINCT
    id_2
FROM
    table_2;

\echo '---------------- Variante 1C : EXCEPT ALL -------------------------'
\echo '---------- /!\\ sémantique MULTI ensemeble ALL /!\\ -----------------'
EXPLAIN ANALYZE
SELECT
    id_1
FROM
    table_1
EXCEPT ALL
SELECT
    id_2
FROM
    table_2;

\echo '---------------- Variante 2A : NOT IN -----------------------------'
\echo '------------ /!\\ sémantique NI set NI multiset  /!\\ ---------------'
-- le nombre de tuples pour les requêtes qui suivent est entre EXCEPT et EXCEPT ALL  
EXPLAIN ANALYZE
SELECT
    id_1
FROM
    table_1
WHERE
    id_1 NOT IN (
        SELECT
            id_2
        FROM
            table_2);

\echo '---------------- Variante 2B : NOT IN DISTINCT --------------------'
EXPLAIN ANALYZE
SELECT
    id_1
FROM
    table_1
WHERE
    id_1 NOT IN ( SELECT DISTINCT
            id_2
        FROM
            table_2);

\echo '---------------- Variante 2C : IN NOT -----------------------------'
EXPLAIN ANALYZE
SELECT
    id_1
FROM
    table_1
WHERE
    NOT id_1 IN (
        SELECT
            id_2
        FROM
            table_2);

\echo '---------------- Variante 2D : NOT IN + SUB -----------------------'
EXPLAIN ANALYZE
SELECT
    id_1
FROM
    table_1
WHERE
    id_1 NOT IN (
        SELECT
            id_2
        FROM
            table_2
        WHERE
            id_2 = id_1);

\echo '---------------- Variante 3A : LEFT OUTER JOIN --------------------'
EXPLAIN ANALYZE
SELECT
    id_1
FROM
    table_1
    LEFT JOIN table_2 ON id_1 = id_2
WHERE
    id_2 IS NULL;

\echo '---------------- Variante 3B : RIGHT OUTER ------------------------'
EXPLAIN ANALYZE
SELECT
    id_1
FROM
    table_2
    RIGHT JOIN table_1 ON id_1 = id_2
WHERE
    id_2 IS NULL;

\echo '---------------- Variante 4A : NOT EXISTS -------------------------'
EXPLAIN ANALYZE
SELECT
    id_1
FROM
    table_1
WHERE
    NOT EXISTS (
        SELECT
            id_2
        FROM
            table_2
        WHERE
            id_2 = id_1);

\echo '---------------- Variante 4B : NOT EXISTS DISTINCT ----------------'
EXPLAIN ANALYZE
SELECT
    id_1
FROM
    table_1
WHERE
    NOT EXISTS ( SELECT DISTINCT
            id_2
        FROM
            table_2
        WHERE
            id_2 = id_1);

\echo '---------------- Variante 5A : NOT ANY ----------------------------'
EXPLAIN ANALYZE
SELECT
    id_1
FROM
    table_1
WHERE
    NOT id_1 = ANY ( SELECT DISTINCT
            id_2
        FROM
            table_2
        WHERE
            id_2 = id_1);

\echo '---------------- Variante 5B : ALL NOT ----------------------------'
EXPLAIN ANALYZE
SELECT
    id_1
FROM
    table_1
WHERE
    id_1 <> ALL ( SELECT DISTINCT
            id_2
        FROM
            table_2
        WHERE
            id_2 = id_1);

