SELECT文でJOIN部分とWHERE句に条件を書いた場合の違い

やりたいこと

MySQLのSELECT文で、JOIN部分(LEFT OUTER JOIN)に条件を記述した場合とWHERE句に条件を記述した場合の結果の違いを知りたい。

データ

tbl_order(受注テーブル)

tbl_user(顧客テーブル)

DROP TABLE IF EXISTS tbl_order;
CREATE TABLE IF NOT EXISTS tbl_order (
  order_id int(11) NOT NULL COMMENT '受注ID',
  order_user_id int(11) NOT NULL COMMENT '受注顧客ID',
  payment int(11) NOT NULL COMMENT '受注金額',
  order_datetime datetime NOT NULL COMMENT '受注日時',
  order_del_flg tinyint(4) NOT NULL DEFAULT 0 COMMENT '受注削除フラグ 0:有効な受注 1:削除された受注',
  PRIMARY KEY (order_id),
  KEY order_del_flg (order_del_flg),
  KEY order_user_id (order_user_id),
  KEY order_user_id_del_flg (order_user_id, order_del_flg)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO tbl_order (order_id, order_user_id, payment, order_datetime, order_del_flg) VALUES
(1,  101, 146651, '2016-01-08 12:12', 0),
(2,  101, 791258, '2016-01-17 12:41', 0),
(3,  101, 270415, '2016-01-19 15:04', 0),
(4,  101,  30758, '2016-01-27 13:33', 0),
(5,  101,  53186, '2016-01-22 15:31', 0),
(6,  102, 926059, '2016-01-31 15:44', 0),
(7,  102, 367294, '2016-01-31 19:04', 0),
(8,  102, 899585, '2016-02-07 13:28', 1),
(9,  102, 998365, '2016-02-08 19:08', 1),
(10, 103, 771798, '2016-02-09 14:11', 1);

DROP TABLE IF EXISTS tbl_user;
CREATE TABLE IF NOT EXISTS tbl_user (
  user_id int(11) NOT NULL COMMENT '顧客ID',
  user_name text NOT NULL COMMENT '顧客名',
  user_del_flg tinyint(4) NOT NULL DEFAULT 0 COMMENT '顧客削除フラグ 0:有効な顧客 1:削除された顧客',
  PRIMARY KEY (user_id),
  KEY user_del_flg (user_del_flg)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO tbl_user (user_id, user_name, user_del_flg) VALUES
(101, '注文 太郎', 0),
(102, '受注 浩二', 1),
(103, '購入 花子', 0);

DROP TABLE IF EXISTS tbl_order;

CREATE TABLE IF NOT EXISTS tbl_order (

order_id int(11) NOT NULL COMMENT '受注ID',

order_user_id int(11) NOT NULL COMMENT '受注顧客ID',

payment int(11) NOT NULL COMMENT '受注金額',

order_datetime datetime NOT NULL COMMENT '受注日時',

order_del_flg tinyint(4) NOT NULL DEFAULT 0 COMMENT '受注削除フラグ 0:有効な受注 1:削除された受注',

PRIMARY KEY (order_id),

KEY order_del_flg (order_del_flg),

KEY order_user_id (order_user_id),

KEY order_user_id_del_flg (order_user_id, order_del_flg)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO tbl_order (order_id, order_user_id, payment, order_datetime, order_del_flg) VALUES

(1, 101, 146651, '2016-01-08 12:12', 0),

(2, 101, 791258, '2016-01-17 12:41', 0),

(3, 101, 270415, '2016-01-19 15:04', 0),

(4, 101, 30758, '2016-01-27 13:33', 0),

(5, 101, 53186, '2016-01-22 15:31', 0),

(6, 102, 926059, '2016-01-31 15:44', 0),

(7, 102, 367294, '2016-01-31 19:04', 0),

(8, 102, 899585, '2016-02-07 13:28', 1),

(9, 102, 998365, '2016-02-08 19:08', 1),

(10, 103, 771798, '2016-02-09 14:11', 1);

DROP TABLE IF EXISTS tbl_user;

CREATE TABLE IF NOT EXISTS tbl_user (

user_id int(11) NOT NULL COMMENT '顧客ID',

user_name text NOT NULL COMMENT '顧客名',

user_del_flg tinyint(4) NOT NULL DEFAULT 0 COMMENT '顧客削除フラグ 0:有効な顧客 1:削除された顧客',

PRIMARY KEY (user_id),

KEY user_del_flg (user_del_flg)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO tbl_user (user_id, user_name, user_del_flg) VALUES

(101, '注文太郎', 0),

(102, '受注浩二', 1),

(103, '購入花子', 0);

受注テーブルと顧客テーブルがあり、受注テーブルの受注顧客IDをキーに「LEFT OUTER JOIN」で顧客テーブルをくっつける。
その際、JOIN内に「受注削除フラグ = 0」を指定した場合と、WHERE句で「受注削除フラグ = 0」を指定した場合の結果の違いを知りたい。

結果

order_del_flg = 0

-- order_del_flg = 0 をJOINに
SELECT
    order_id,
    order_del_flg,
    order_user_id,
    user_id,
    user_name,
    user_del_flg
FROM
    tbl_order
        LEFT OUTER JOIN tbl_user ON
            tbl_order.order_user_id = tbl_user.user_id AND
            tbl_order.order_del_flg = 0
;

-- order_del_flg = 0 をWHEREに
SELECT
    order_id,
    order_del_flg,
    order_user_id,
    user_id,
    user_name,
    user_del_flg
FROM
    tbl_order
        LEFT OUTER JOIN tbl_user ON
            tbl_order.order_user_id = tbl_user.user_id
WHERE
    tbl_order.order_del_flg = 0
;

-- order_del_flg = 0 をJOINに

SELECT

order_id,

order_del_flg,

order_user_id,

user_id,

user_name,

user_del_flg

FROM

tbl_order

LEFT OUTER JOIN tbl_user ON

tbl_order.order_user_id = tbl_user.user_id AND

tbl_order.order_del_flg = 0

;

-- order_del_flg = 0 をWHEREに

SELECT

order_id,

order_del_flg,

order_user_id,

user_id,

user_name,

user_del_flg

FROM

tbl_order

LEFT OUTER JOIN tbl_user ON

tbl_order.order_user_id = tbl_user.user_id

WHERE

tbl_order.order_del_flg = 0

;

order_del_flg = 0

user_del_flg = 0

-- user_del_flg = 0をJOINに
SELECT
    order_id,
    order_del_flg,
    order_user_id,
    user_id,
    user_name,
    user_del_flg
FROM
    tbl_order
        LEFT OUTER JOIN tbl_user ON
            tbl_order.order_user_id = tbl_user.user_id AND
            tbl_user.user_del_flg = 0
;

-- user_del_flg = 0をWHEREに
SELECT
    order_id,
    order_del_flg,
    order_user_id,
    user_id,
    user_name,
    user_del_flg
FROM
    tbl_order
        LEFT OUTER JOIN tbl_user ON
            tbl_order.order_user_id = tbl_user.user_id
WHERE
    tbl_user.user_del_flg = 0
;

-- user_del_flg = 0をJOINに

SELECT

order_id,

order_del_flg,

order_user_id,

user_id,

user_name,

user_del_flg

FROM

tbl_order

LEFT OUTER JOIN tbl_user ON

tbl_order.order_user_id = tbl_user.user_id AND

tbl_user.user_del_flg = 0

;

-- user_del_flg = 0をWHEREに

SELECT

order_id,

order_del_flg,

order_user_id,

user_id,

user_name,

user_del_flg

FROM

tbl_order

LEFT OUTER JOIN tbl_user ON

tbl_order.order_user_id = tbl_user.user_id

WHERE

tbl_user.user_del_flg = 0

;

user_del_flg = 0

order_del_flg = 0 AND user_del_flg = 0

-- 両方ともJOINに
SELECT
    order_id,
    order_del_flg,
    order_user_id,
    user_id,
    user_name,
    user_del_flg
FROM
    tbl_order
        LEFT OUTER JOIN tbl_user ON
            tbl_order.order_user_id = tbl_user.user_id AND
            tbl_order.order_del_flg = 0 AND
            tbl_user.user_del_flg = 0
;

-- 両方ともWHEREに
SELECT
    order_id,
    order_del_flg,
    order_user_id,
    user_id,
    user_name,
    user_del_flg
FROM
    tbl_order
        LEFT OUTER JOIN tbl_user ON
            tbl_order.order_user_id = tbl_user.user_id
WHERE
    tbl_order.order_del_flg = 0 AND
    tbl_user.user_del_flg = 0
;

-- 両方ともJOINに

SELECT

order_id,

order_del_flg,

order_user_id,

user_id,

user_name,

user_del_flg

FROM

tbl_order

LEFT OUTER JOIN tbl_user ON

tbl_order.order_user_id = tbl_user.user_id AND

tbl_order.order_del_flg = 0 AND

tbl_user.user_del_flg = 0

;

-- 両方ともWHEREに

SELECT

order_id,

order_del_flg,

order_user_id,

user_id,

user_name,

user_del_flg

FROM

tbl_order

LEFT OUTER JOIN tbl_user ON

tbl_order.order_user_id = tbl_user.user_id

WHERE

tbl_order.order_del_flg = 0 AND

tbl_user.user_del_flg = 0

;

order_del_flg, user_del_flg = 0

そもそも

ごくごくフツーのアプリケーションなら「両方ともWHEREに」の結果が必要なハズだし、取得件数も少なく済むので幸せ。
Butしかし、
細かい中身は違うが、処理的には"結果セットをループ処理することが仕様っぽい状況で、「両方ともJOINに」のSQLを発行している"的な実装を見かけた。しかもレコードは数十万件単位。
とてもモヤモヤしたのでこの記事を書こうと思った。
INNER JOINならJOINの部分に条件書いてもいいような。