事前準備
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 |
田中 |