INNER JOINからEXISTSに書き換えたい

Question

前提・実現したいこと

SQL初学者です。
MySQLのサンプルデータベースのひとつであるEmployeesを用いて勉強をしています。

exists文を使って部署(dept_name)毎に現在(to_date="9999-01-01")titleがStaffである人数を集計せよ、という問題を解きたいのですが、existsの使い方がいまいちわかっていないこともあってどのようなSQL文を書けばよいのかわかりません。
内部結合を用いて集計することはできたのですが…（試したことの欄で後述します）

使うテーブル

titlesテーブルとdept_empテーブルはそれぞれ443308レコード、331603レコードあってすべてのレコードをINSERT文の中に記述することはできないため、5レコードのみ記載します。

#####titles

CREATE TABLE "titles"
(
    "emp_no" INT(11) PRIMARY KEY,
    "title" VARCHAR(50) PRIMARY KEY,
    "from_date" DATE PRIMARY KEY,
    "to_date" DATE
)

INSERT INTO "titles"
    ("emp_no", "title", "from_date", "to_date")
VALUES
    (10001, "Senior Engineer", "1986-06-26", "9999-01-01"),
    (10002, "Staff", "1996-08-03", "9999-01-01"),
    (10003, "Senior Engineer", "1995-12-03", "9999-01-01"),
    (10004, "Engineer", "1986-12-01", "1995-12-01"),
    (10004, "Staff", "1995-12-01", "9999-01-01")

dept_emp

CREATE TABLE "dept_emp"
(
    "emp_no" INT(11) PRIMARY KEY,
    "dept_no" CHAR(4) PRIMARY KEY,
    "from_date" DATE,
    "to_date" DATE
)

INSERT INTO "dept_emp"
    ("emp_no", "dept_no", "from_date", "to_date")
VALUES
    (10001, "d005", "1986-06-26", "9999-01-01"),
    (10002, "d007", "1996-08-03", "9999-01-01"),
    (10003, "d004", "1995-12-03", "9999-01-01"),
    (10004, "d004", "1986-12-01", "1995-12-01"),
    (10005, "d003", "1989-09-12", "9999-01-01");

departments

CREATE TABLE "departments"
(
    "dept_no" CHAR(4) PRIMARY KEY,
    "dept_name" VARCHAR(40) UNIQUE
)

INSERT INTO "departments"
    ("dept_no","dept_name")
VALUES
    ("d001", "Marketing"),
    ("d002", "Finance"),
    ("d003", "Human Resources"),
    ("d004", "Production"),
    ("d005", "Development"),
    ("d006", "Quality Management"),
    ("d007", "Sales"),
    ("d008", "Research"),
    ("d009", "Customer Service");

試したこと

内部結合を用いた集計を行うことには（おそらく）成功しました。

SELECT dept_name, COUNT(*) AS num
FROM
    (SELECT a.emp_no, a.title, a.to_date, c.dept_name
    FROM titles a
        INNER JOIN dept_emp b
        ON a.emp_no=b.emp_no
        INNER JOIN departments c
        ON b.dept_no=c.dept_no) joined
WHERE title="Staff" AND to_date="9999-01-01"
GROUP BY dept_name
ORDER BY dept_name;

実行結果

dept_name	num
Customer Service	3902
Development	315
Finance	3199
Human Resources	3416
Marketing	3880
Production	349
Research	3206
Sales	9967

補足情報（FW/ツールのバージョンなど）

-> % mysql --version
mysql Ver 8.0.13 for osx10.14 on x86_64 (Homebrew)

初質問なので不足している情報等あってご迷惑をおかけするかもしれませんが、よろしくお願いします。

Comments

[Orlofsky]

テーブルの情報は https://teratail.com/questions/167224 のように CREATE TABLE と INSERT で提示するとSQLをすぐ実行できるので、コメントが付き易いかと。

[sendai]

的確なご指摘ありがとうございます。
レコード数が多いデータだったのでSQL文で提示するのは向いてないのかな、と思っていましたが、それでもあるに越したことはないですね。
修正致しましたのでぜひご確認ください。よろしくお願いします。

Answer 1

こんなところでしょうか。

SELECT 
    c.dept_name, COUNT(*) AS num
FROM
    departments c
        INNER JOIN
    dept_emp b ON b.dept_no = c.dept_no
WHERE
    EXISTS( SELECT 
            *
        FROM
            titles a
        WHERE
            a.emp_no = b.emp_no
                AND a.title = 'Staff'
                AND a.to_date = '9999-01-01')
GROUP BY c.dept_name
ORDER BY c.dept_name;

あと、余談ですが、内部結合のクエリは以下のようにも書き換えられます。
派生テーブルを使わないバージョンです。実際に計測してみたところ、このサイズだとあまり変わらないようでしたが・・・w

SELECT c.dept_name, COUNT(*) AS num
    FROM titles a
        INNER JOIN dept_emp b
        ON a.emp_no=b.emp_no
        INNER JOIN departments c
        ON b.dept_no=c.dept_no
WHERE a.title="Staff" AND a.to_date="9999-01-01"
GROUP BY c.dept_name
ORDER BY c.dept_name;

Comments

[sendai]

素早い回答ありがとうございます。
INNER JOINとEXISTSを併用すればよかったんですね…ひとつ勉強になりました。
内部結合に関してもアドバイス非常に助かります！ほぼ独学で勉強しているので、こういったフィードバックはとても新鮮でありがたいです。参考にさせていただきます！