SQL#4-JOIN利用
1. INNER JOIN
INNER JOIN
:SELECT*FROM表1,表2 WHERE表1.表2.コラム
ANSI SQL(標準)(ORACLE、MYSQL、MSSQL...)
:SELECT*FROM表1 INNERJOIN表2 ON表1.表2.コラム
1.ロシア語文法
SELECT * FROM 테이블1, 테이블2 WHERE 테이블1.컬럼 = 테이블2.컬럼;
ITEM 1テーブルとORDER 1テーブルをJOINに変換
:条件は
ITEM1.ITEMNO = ORDER1.ORDITEM
1.で作成したテーブルとMEMBER 1テーブルをJOINに変換
: 1.で作成したテーブルをITEMORDER 1に設定します.
:条件は
MEMBER1.USERID = ITEMORDER1.ORDID
2.で作ったテーブルをVIEWにします.
:VIEW閲覧のみ
:作成したSQL文を使用した仮想テーブルの作成
:INSERT、UPDATEなどは使用できません
:
CREATE OR REPLACE VIEW ORDER1VIEW AS
-- 1. ITEM1 X ORDER1 = 새로운 테이블
SELECT ITEM1.ITEMNAME, ITEM1.ITEMPRICE, ORDER1.*
FROM
ITEM1, ORDER1
WHERE
ITEM1.ITEMNO = ORDER1.ORDITEM;
-- 2. 새로운 테이블 X MEMBER1
SELECT ITEMORDER1.*, MEMBER1.USERNAME, MEMBER1.USERADDR
FROM MEMBER1, (
SELECT
ITEM1.ITEMNAME, ITEM1.ITEMPRICE, ORDER1.*
FROM
ITEM1, ORDER1
WHERE
ITEM1.ITEMNO = ORDER1.ORDITEM) ITEMORDER1
WHERE MEMBER1.USERID = ITEMORDER1.ORDID;
-- 3. 결과를 가상의 테이블(VIEW)로 만듦
CREATE OR REPLACE VIEW ORDER1VIEW AS
SELECT ITEMORDER1.*, MEMBER1.USERNAME, MEMBER1.USERADDR
FROM MEMBER1, (
SELECT
ITEM1.ITEMNAME, ITEM1.ITEMPRICE, ORDER1.*
FROM
ITEM1, ORDER1
WHERE
ITEM1.ITEMNO = ORDER1.ORDITEM) ITEMORDER1
WHERE MEMBER1.USERID = ITEMORDER1.ORDID;
-- 4. 생성된 VIEW 조회 (ITEM1, MEMBER1, ORDER1)
SELECT * FROM ORDER1VIEW;
DELETE FROM ORDER1 WHERE ORDNO = 10021;
-- VIEW는 조회만 가능
-- 내가 만든 SQL문으로 가상의 테이블을 생성하는 것
-- INSERT, UPDATE등은 불가
2.ANSI SQL(標準文法)(ORACLE、MYSQL、MSSQL...)
SELECT * FROM 테이블1 INNER JOIN 테이블2 ON 테이블1.컬럼 = 테이블2.컬럼;
-- 1. 단순조회
SELECT * FROM ITEM1 INNER JOIN ORDER1 ON ITEM1.ITEMNO = order1.orditem;
-- 2. 프로젝션
SELECT ORDER1.*, item1.itemname, ITEM1.ITEMPRICE, ITEM1.ITEMQTY
FROM ITEM1 INNER JOIN ORDER1 ON ITEM1.ITEMNO = order1.orditem;
-- 3. 결과물 다시 JOIN
SELECT ITEMORDER1.*, member1.username, member1.useraddr FROM MEMBER1
INNER JOIN
(SELECT ORDER1.*, item1.itemname, ITEM1.ITEMPRICE, ITEM1.ITEMQTY
FROM ITEM1 INNER JOIN ORDER1 ON ITEM1.ITEMNO = order1.orditem) ITEMORDER1
ON ITEMORDER1.ORDID = member1.userid;
-- 4. 3.번의 결과물을 VIEW로 만들기
CREATE OR REPLACE VIEW ORDERVIEW1 AS
SELECT ITEMORDER1.*, member1.username, member1.useraddr FROM MEMBER1
INNER JOIN
(SELECT ORDER1.*, item1.itemname, ITEM1.ITEMPRICE, ITEM1.ITEMQTY
FROM ITEM1 INNER JOIN ORDER1 ON ITEM1.ITEMNO = order1.orditem) ITEMORDER1
ON ITEMORDER1.ORDID = member1.userid;
-- 5. ORDERVIEW1 조회
SELECT * FROM ORDERVIEW1;
2. LEFT OUTER JOIN
1.ロシア語文法
SELECT * FROM 테이블1, 테이블2 WHERE 테이블1.컬럼 = 테이블2.컬럼(+);
SELECT *
FROM
ITEM1, ORDER1
WHERE
ITEM1.ITEMNO = ORDER1.ORDITEM(+);
2.ANSI SQL規格
SELECT * FROM 테이블1 LEFT OUTER JOIN 테이블2 ON 테이블1.컬럼 = 테이블2.컬럼;
SELECT * FROM ITEM1 LEFT OUTER JOIN ORDER1
ON item1.itemno = order1.orditem;
3. RIGHT OUTER JOIN
1.ロシア語文法
SELECT * FROM 테이블1, 테이블2 WHERE 테이블1.컬럼(+)=테이블2.컬럼;
SELECT * FROM ORDER1, MEMBER1 WHERE order1.ordid(+)=member1.userid;
2.ANSI SQL規格
SELECT * FROM 테이블1 RIGHT OUTER JOIN 테이블2 WHERE 조건;
SELECT * FROM ORDER1 RIGHT OUTER JOIN MEMBER1
ON order1.ordid=member1.userid;
4. FULL OUTER JOIN
1.ANSI SQLのみ
SELECT * FROM 테이블1 FULL OUTER JOIN 테이블2 ON 테이블1.컬럼(+)=테이블2.컬럼;
SELECT * FROM ORDER1 FULL OUTER JOIN MEMBER1
ON order1.ordid=member1.userid;
5.条件
1.ロシア語文法
-- 조건
SELECT
ITEM1.ITEMNAME, ITEM1.ITEMPRICE, ORDER1.*
FROM
ITEM1, ORDER1
WHERE
ITEM1.ITEMNO = ORDER1.ORDITEM AND item1.itemprice >= 2;
2.ANSI SQL規格
-- ANSI
SELECT
ITEM1.ITEMNAME, ITEM1.ITEMPRICE, ORDER1.*
FROM
ITEM1 INNER JOIN ORDER1
ON
ITEM1.ITEMNO = ORDER1.ORDITEM
WHERE
item1.itemprice >= 2;
Reference
この問題について(SQL#4-JOIN利用), 我々は、より多くの情報をここで見つけました https://velog.io/@aro9515/SQL활용-4-JOINテキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。
Collection and Share based on the CC Protocol