MySQLコマンドについての備忘録（INNER JOIN句・AS句）

2018/12/14 2021/12/14

Laravelでサイトの構築の開発を進めていると、場合によって複雑なSQL文を書くケースが出てきます。

特にテーブルの結合処理とかそうですね。

Eloquent では便利なリレーション機能がついていますが、複雑なSQL文だったら、生のSQL文を直接書いたほうがわかりやすかったりします。

生の SQL を Laravel で直接書く場合は、クエリビルダー（DBファサード）のSELECTメソッドを使います。


# 例
public function index()
{
  $users = DB::select('select * from users where active = ?', [1]);
  return view('user.index', ['users' => $users]);
}

SQL文を直接書くならSQLの文法をきちんと理解しておく必要があります。

今回は実践でよく使うSQLの文法についてエントリーします。

この記事の内容

内部結合（INNER JOIN句）
内部結合 + 条件
３つのテーブル結合
カラムに別名をつける（AS句）
INNER JOIN句 + AS句
LaravelのDBファサードで書いてみる

内部結合（INNER JOIN句）

２つのテーブルを結合してデータを取得する方法です。

指定したそれぞれのテーブルのカラムの値が一致するデータだけを取得します。
（※内部結合の場合、ベースとなるテーブルから、条件にマッチするレコードがないものは削除されます）

書式


SELECT テーブル名.カラム名, ... FROM テーブル名１ 
INNER JOIN テーブル名２
 ON テーブル名１.カラム名１ = テーブル名２.カラム名２;

-- 簡略化
SELECT （取得するカラム）FROM テーブル名１ 
INNER JOIN テーブル名２
 ON （結合条件）;

ポイント

取得するデータは２つのテーブルのカラムが混合したものとなります。どのテーブルのカラムなのかを表すために「テーブル名.カラム名」の形で指定します。

書式例


SELECT * FROM order_tbl 
INNER JOIN detail_tbl
 ON order_tbl.id = detail_tbl.order_id;

実行結果

テーブル結合 + 条件の場合

内部結合のあとにWHERE句をつけてあげます。


SELECT * From order_tbl 
INNER JOIN detail_tbl
 ON order_tbl.id = detail_tbl.order_id 
WHERE detail_tbl.product_price > 3000;

３つのテーブル結合

書式


SELECT テーブル名.カラム名, ... (取得したいカラム名)
FROM テーブル名１ 
INNER JOIN テーブル名２
 ON テーブル名１.カラム名１ = テーブル名２.カラム名２ 
INNER JOIN テーブル名３
 ON テーブル名１.カラム名１ = テーブル名３.カラム名３

書式例


SELECT * FROM detail_tbl 
INNER JOIN order_tbl
 ON detail_tbl.order_id = order_tbl.id 
INNER JOIN product_tbl
 ON detail_tbl.product_id = product_tbl.id;

実行結果

LaravelでJOINメソッドの使用方法

join メソッドを使うとINNER JOIN句と同じになります。


$users = DB::table('detail_tbl')
  ->join('order_tbl','detail_tbl.order_id', '=', 'order_tbl.id') // detail_tblテーブルのorder_idとorder_tblテーブルのidを紐づける
  ->join('product','detail_tbl.product_id', '=', 'product.id') // detail_tblテーブルのproduct_idとproduct_tblテーブルのidを紐づける
  ->select('users.*','contacts.phone', 'orders.price')
  ->get();

カラムに別名をつける（AS句）

SELECT文を使ってテーブルからデータを取得したとき、AS句を使用することで元のテーブルのカラム名とは異なるカラム名をつけてデータを取得することができます。

書式


SELECT カラム名 AS 別名, ... FROM テーブル名;

書式例


SELECT id, user_id AS customer, amount AS volume FROM order_tbl;

実行結果

INNER JOIN句 + AS句

INNER JOIN句で内部結合すると結合先のカラム名とよくかぶってしまうので、合わせ技としてAS句をよく使います。

書式例


SELECT
 o.id AS order_id, -- 注文テーブルの id に order_id と列名を付ける 
 o.user_id AS user_id, --注文テーブルの user_id に user_id と列名をつける
 o.amount AS amount, --注文テーブルの amount に amount と列名をつける
 o.order_time AS order_time, --注文テーブルの order_time に order_time と列名をつける
 d.id AS detail_id, --詳細テーブルの id に detail_id と列名をつける
 d.product_qty AS qty --詳細テーブルの product_qty に qty と列名をつける
FROM
 order_tbl AS o --注文テーブル order_tble に o とテーブル名をつける
INNER JOIN
 detail_tbl AS d --注文テーブル detail_tble に d とテーブル名をつける
ON o.id = d.order_id
INNER JOIN
 product_tbl AS p  --注文テーブルproduct_tb lに p とテーブル名をつける
ON d.product_id = p.id;

実行結果

LaravelのDBファサードで書いてみる

複雑なSQLならDBファサードのSELECTメソッドで直接SQLを実行したほうがいいかもしれません。

書式例

例えば、Ajax でURLを叩いて getAllOrder メソッドを実行し、JSON形式で返す処理を書く場合


public function getAllOrder(Request $request) {

$list = DB::select(
"SELECT to.id AS order_id, p.name AS product_name, p.price AS product_price from order_tbl as o ".
"INNER JOIN detail_tbl AS d on o.id = d.order_id ".
"WHERE d.order_id = ? and o.order_time > ? ORDER BY o.order_time ASC", [$request['order_id'], $request['order_time']]
);

return response()->json(['orders' => $list]);
}