JOIN, EXISTS, IN使い分け

言語化しておく🐱

主表と従属表でどちらのカラムも必要な場合

JOIN

LEFT JOIN

従属表のカラムの列にnullが必要な場合
条件によってnullで表示させたい場合はON句を使う
ON A.id = B.a_id AND B.delete_flag = 0

INNER JOIN

主表と従属表の共通行を出したい場合

主表のカラムのみで良い場合

EXISTS +サブクエリ or IN + サブクエリ

主表の選択性が高い場合

EXISTS + サブクエリ

従属表の選択性が高い場合

IN + サブクエリ

主表、従属表どちらも選択性が低い場合

EXISTS + サブクエリ

JOINをすることでdistinct()を使ってる場合

主表を反転させて、where exists +サブクエリで対応しよう。

数千件のレコードをdistinct()して圧縮して性能問題が発生する可能性がある

Laravel 例

app/Repositories/Company/CompanyRepository.php

    public function getCompanyIdsBy(int $section_id): array
    {
        return $this->resource
            ->select('company.id')
            ->whereExists(function ($query) { // ● リレーション先があるものを探してくれる
                $query->select(DB::raw(1))
                    ->from('staffs')
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　->whereRaw('company.staff_id = staffs.id'); // ● フルスキャン防止
                    ->whereNotNull('staffs.actived_at') // ●追加where条件
                    ->limit(1); // ● 必須
            })
            ->where('staffs.section_id', $section_id)
            ->get()->pluck('id')->all();

// LaravelのwhereExists()はリレーション先があるものを探してくれるけど、フルスキャン防止にリレーションのidを指定した方が良い

        return $this->resource
            ->select('company.id')
            ->whereExists(function ($query) { // ● リレーション先があるものを探してくれる
                $query->select(DB::raw(1))
                    ->from('staffs')
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　->whereRaw('company.staff_id = staffs.id'); // ● フルスキャン防止
                    ->limit(1); // ● 必須
            })
            ->get()

公式通り、結合条件は書きましょう

DB::table('users')
            ->whereExists(function ($query) {
                $query->select(DB::raw(1))
                      ->from('orders')
                      ->whereRaw('orders.user_id = users.id') // ●大事
                      ->limit(1);  // ●大事
            })
            ->get();

結合条件ないとフルスキャンになる；；

existsというのは条件式になるので
１行毎に比較用のクエリが実行されるのと同じ扱いになるんだけど（だから容易にするのも違う）
比較する時にフルスキャンだと１行毎に、existsしているテーブルをフルスキャンするので激重プンプン丸になる
なので、existsで使うクエリは、INDEXで絞り込めている必要がでてくる（そうしないと重くなるので）

同僚O氏