Help us understand the problem. What is going on with this article?

複数の表をjoinして検索

More than 1 year has passed since last update.

MySQLなどのデータベースでSQLのjoinを使って複数の表からのデータを検索します

1. 表を準備します

  • たとえば下記のようなSQLを使って3つの表を用意します
3つの表を用意
CREATE TABLE table_main (
    hinban VARCHAR(10) PRIMARY KEY,
    color VARCHAR(10),
    price INT
);

INSERT INTO table_main (hinban, color, price) VALUES('1000', 'blue', 100);
INSERT INTO table_main (hinban, color, price) VALUES('2000', 'red', 200);

CREATE TABLE table_souko (
    hinban VARCHAR(10) PRIMARY KEY,
    zaiko INT
);

INSERT INTO table_souko (hinban, zaiko) VALUES('1000', 10);
INSERT INTO table_souko (hinban, zaiko) VALUES('2000', 20);

CREATE TABLE table_other (
    hinban VARCHAR(10) PRIMARY KEY,
    nickname VARCHAR(10)
);

INSERT INTO table_other (hinban, nickname) VALUES('1000', 'ao');
INSERT INTO table_other (hinban, nickname) VALUES('2000', 'aka');

2. joinを使って複数の表から検索します

  • joinで複数の表から検索します
  • 下記ではJOIN table_souko AS T2table_soukoT2と呼ぶことを宣言し、 ON T1.hinban = T2.hinbanT1hinban列とT2hinban列を関連付けることを指定しています
Joinを使って複数の表から検索
SELECT 
    T1.hinban, 
    T1.color,
    T1.price, 
    T2.zaiko,
    T3.nickname
FROM
    table_main AS T1
    JOIN table_souko AS T2
        ON T1.hinban = T2.hinban 
    JOIN table_other AS T3
        ON T2.hinban = T3.hinban 
WHERE
    T1.hinban = 1000;

下記の結果が返されます

mysql> SELECT  T1.hinban,  T1.color, T1.price,  T2.zaiko, T3.nickname FROM table_main AS T1 JOIN table_souko AS T2 ON T1.hinban = T2.hinban  JOIN table_other AS T3 ON T2.hinban = T3.hinban  WHERE T1.hinban = 2000;
+--------+-------+-------+-------+----------+
| hinban | color | price | zaiko | nickname |
+--------+-------+-------+-------+----------+
| 2000   | red   |   200 |    20 | aka      |
+--------+-------+-------+-------+----------+
1 row in set (0.00 sec)

mysql> 

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away