NOT EXISTS、EXISTS、LEFT JOIN、IN演算子：外部キーと行選択

外部キーを持つテーブルの行を選択する方法

NOT EXISTS を使用すると、別のテーブルに一致するエントリがない行を選択できます。 構文は以下のようになります。

SELECT
    *
FROM
    table1
WHERE
    NOT EXISTS (
        SELECT
            *
        FROM
            table2
        WHERE
            table1.foreign_key_column = table2.primary_key_column
    );

この例では、table1 から table2 に一致するエントリがない行を選択します。 table1.foreign_key_column は table2.primary_key_column と一致する必要があります。

例:

SELECT
    *
FROM
    customers
WHERE
    NOT EXISTS (
        SELECT
            *
        FROM
            orders
        WHERE
            customers.customer_id = orders.customer_id
    );

このクエリは、注文したことがない顧客を選択します。

SELECT
    *
FROM
    table1
LEFT JOIN
    table2
ON
    table1.foreign_key_column = table2.primary_key_column
WHERE
    table2.primary_key_column IS NULL;

SELECT
    *
FROM
    customers
LEFT JOIN
    orders
ON
    customers.customer_id = orders.customer_id
WHERE
    orders.order_id IS NULL;

SELECT
    *
FROM
    table1
WHERE
    table1.foreign_key_column NOT IN (
        SELECT
            primary_key_column
        FROM
            table2
    );

この例では、table2 に存在しない値を持つ table1 の行を選択します。

SELECT
    *
FROM
    customers
WHERE
    customers.customer_id NOT IN (
        SELECT
            order_id
        FROM
            orders
    );

上記のいずれの方法を使用しても、外部キーを持つテーブルから、別のテーブルに一致するエントリがない行を選択できます。 どの方法を使用するかは、状況によって異なります。

NOT EXISTS を使用する方法

SELECT
    *
FROM
    customers
WHERE
    NOT EXISTS (
        SELECT
            *
        FROM
            orders
        WHERE
            customers.customer_id = orders.customer_id
    );

LEFT JOIN を使用する方法

SELECT
    *
FROM
    customers
LEFT JOIN
    orders
ON
    customers.customer_id = orders.customer_id
WHERE
    orders.order_id IS NULL;

IN 演算子を使用する方法

SELECT
    *
FROM
    customers
WHERE
    customers.customer_id NOT IN (
        SELECT
            order_id
        FROM
            orders
    );

このコードを実行するには、次のテーブルが必要です。

• customers

  • customer_id (INT, PRIMARY KEY)
  • name (VARCHAR(255))

• orders

  • customer_id (INT)

このコードを実行すると、次の結果が得られます。

customer_id | name
-----------+--------
1           | John Doe
2           | Jane Doe

この例では、customers テーブルには 2 つの行があり、orders テーブルには 1 つの行のみあります。 customer_id 1 の顧客は注文していますが、customer_id 2 の顧客は注文していません。

• CASE ステートメントを使用する方法

これらの方法は、上記の 3 つの方法よりも複雑なので、ここでは説明しません。

外部キーを持つテーブルから、別のテーブルに一致するエントリがない行を選択するその他の方法

SELECT
    *
FROM
    table1
WHERE
    EXISTS (
        SELECT
            *
        FROM
            table2
        WHERE
            table1.foreign_key_column = table2.primary_key_column
    );

SELECT
    *
FROM
    customers
WHERE
    EXISTS (
        SELECT
            *
        FROM
            orders
        WHERE
            customers.customer_id = orders.customer_id
    );

FULL JOIN を使用すると、両方のテーブルに存在する行、一方のテーブルのみに存在する行、両方のテーブルに存在しない行を選択できます。 構文は以下のようになります。

SELECT
    *
FROM
    table1
FULL JOIN
    table2
ON
    table1.foreign_key_column = table2.primary_key_column
WHERE
    table2.primary_key_column IS NULL;

SELECT
    *
FROM
    customers
FULL JOIN
    orders
ON
    customers.customer_id = orders.customer_id
WHERE
    orders.order_id IS NULL;

SELECT
    *
FROM
    table1
WHERE
    CASE
        WHEN EXISTS (
            SELECT
                *
            FROM
                table2
            WHERE
                table1.foreign_key_column = table2.primary_key_column
        ) THEN
            'Has match'
        ELSE
            'No match'
    END = 'No match';

SELECT
    *
FROM
    customers
WHERE
    CASE
        WHEN EXISTS (
            SELECT
                *
            FROM
                orders
            WHERE
                customers.customer_id = orders.customer_id
        ) THEN
            'Has match'
        ELSE
            'No match'
    END = 'No match';