どこかに向かうらしい話

迷走エンジニアの放浪記

JOIN on SQL

事前準備

Salesテーブル

sales_id sales_date kokyaku_id
0001 2016-10-01 C001
0002 2016-10-02 C002
0003 2016-10-03 C003
0004 2016-10-04 AAAA
0005 2016-10-05
0006 2016-10-06 C001
CREATE TABLE Sales
(sales_id     VARCHAR(4),
 sales_date  DATE,
 kokyaku_id VARCHAR(4));

INSERT INTO Sales VALUES ('S001', '2016-10-01', 'C001');
INSERT INTO Sales VALUES ('S002', '2016-10-02', 'C002');
INSERT INTO Sales VALUES ('S003', '2016-10-03', 'C003');
INSERT INTO Sales VALUES ('S004', '2016-10-04', 'AAAA');
INSERT INTO Sales VALUES ('S005', '2016-10-05', NULL);
INSERT INTO Sales VALUES ('S006', '2016-10-06', 'C001');

Kokyakuテーブル

kokyaku_id kokyaku_name
C001 田中
C002 鈴木
CREATE TABLE Kokyaku
(kokyaku_id   VARCHAR(4),
 kokyaku_name VARCHAR(30));

INSERT INTO kokyaku VALUES ('C001', '田中');
INSERT INTO kokyaku VALUES ('C002', '鈴木');

本題

INNER JOIN

SELECT *
  FROM Sales INNER JOIN Kokyaku 
    ON Sales.kokyaku_id = Kokyaku.kokyaku_id;
sales_id sales_date kokyaku_id kokyaku_id kokyaku_name
S001 2016-10-01 C001 C001 田中
S002 2016-10-02 C002 C002 鈴木
S006 2016-10-06 C001 C001 田中

LEFT OUTER JOIN

SELECT * 
  FROM Sales LEFT OUTER JOIN Kokyaku
    ON Sales.kokyaku_cd = Kokyaku.kokyaku_cd
 ORDER BY sales_id;
sales_id sales_date kokyaku_id kokyaku_id kokyaku_name
S001 2016-10-01 C001 C001 田中
S002 2016-10-02 C002 C002 鈴木
S003 2016-10-03 C003
S004 2016-10-04 AAAA
S005 2016-10-05
S006 2016-10-06 C001 C001 田中

RIGHT OUTER JOIN

SELECT * 
  FROM Sales RIGHT OUTER JOIN Kokyaku
    ON Sales.kokyaku_id = Kokyaku.kokyaku_id
 ORDER BY sales_id;
sales_id sales_date kokyaku_id kokyaku_id kokyaku_name
S001 2016-10-01 C001 C001 田中
S002 2016-10-02 C002 C002 鈴木
S006 2016-10-06 C001 C001 田中