[SQL] SQL에서 JOIN 예제

JOIN : 테이블끼리 연관관계를 맺는 행위

-----------------SELECT 예제 <여러테이블 조인검색 예제>
 
--※여러테이블에대한조인검색※
--조인검색 : 여러개의 테이블을연결하여 데이터를 검색하는 것
--조인속성 : 조인검색을위해 테이블을 연결해주는 속성
--FROM 절에 검색에 필요한 모든 테이블을 나열
--WHERE 절에 조인속성의 값이 같아야함을 의미하는 조인 조건을 제시

--예제7-37 [이너조인=교집합]
--*판매데이터베이스에서 banana고객이 주문한 제품의이름을 검색해보자
SELECT 제품.제품명 FROM 제품, 주문 
WHERE 주문.주문고객 = 'banana' AND 제품.제품번호 = 주문.주문제품;

--예제7-38
--*판매데이터베이스에서 나이가30세 이상인 고객이주문한 제품의 번호와 주문일자를 검색해보자
SELECT 주문.주문제품, 주문.주문일자
FROM 고객,주문
WHERE 고객.나이 >=30 AND 고객.고객아이디 = 주문.주문고객;

--예제7-39
--*판매데이터베이스에서 고명석고객이 주문한 제품의 제품명을 검색해보자
SELECT 제품.제품명
FROM 고객, 제품, 주문
WHERE 고객.고객이름 = '고명석' AND 고객.고객아이디 = 주문.주문고객 
AND 제품.제품번호 = 주문.주문제품;

--※표준 SQL에서는 INNER JOIN과 ON 키워드를 이용해 작성하는 방법도 제공※
SELECT 속성_리스트
FROM 테이블1 INNER JOIN 테이블2 ON 조인조건
[WHERE 검색조건]

예제 7-37

 

예제 7-38

예제 7-39

---------------------------------- <여러테이블 조인검색 예제 추가설명>
--예제7-38 추가설명
--*30대 이상인 melon , pear 라는 튜플(고객테이블)과 주문테이블을 대조
--데이터를 join 해서 표현 
SELECT *
FROM 고객, 주문
WHERE 고객.나이 >= 30;
--inner join 
--B조건 : 고객 아이디와 주문고객의 아이디가 동일한 데이터집합(10개)중에
--A조건인 melon 과 pear고객아이디가 성립하는 튜플을 찾는 행위


--예제7-39 추가설명
--1.조건을 하나씩 분석한다
--2.조건에 해당되는 도출결과의 컬럼을 매칭한다
--3.만일 대칭관계가 1:1이거 n:1이아닐경우 1:n일경우를 구분해서 기억한다.
--*판매데이터베이스 위치를 확인해보자
SELECT * FROM 고객 WHERE 고객이름 = '고명석';
SELECT * FROM 고객, 주문 WHERE 고객.고객아이디 = 주문.주문고객;
SELECT * FROM 제품, 주문 WHERE 제품.제품번호 = 주문.주문제품;

---

이너조인 예제

---------------------------------- 이너조인 예제
--ansi query <81p>
SELECT * 
FROM 고객 --LEFT table
INNER JOIN 주문 --RIGHT table
ON 고객.고객아이디 = 주문.주문고객
WHERE 고객.고객이름 = '고명석';

--예제7-38 -inner join 적용
SELECT 주문.주문제품, 주문.주문일자
FROM 고객 INNER JOIN 주문 ON 고객.고객아이디 = 주문.주문고객
WHERE 고객.나이 >= 30;

예제7-38 INNER JOIN 적용

---

아우터조인 예제

---------------------------------- 아우터조인 예제
--outer join <83p>

--만일 inner join 외에 데이터를 join 할 경우 없는 튜플 데이터는 null표시 
--*주문하지않은 고객도 포함해서 고객이름,주문제품,주문일자를 검색
--★LEFT OUTER JOIN
SELECT 고객.고객이름, 주문.주문제품, 주문.주문일자
FROM 고객 LEFT OUTER JOIN 주문 ON 고객.고객아이디 = 주문.주문고객;

--*고객테이블의 모든 튜플을 검색대상으로 하기 때문에 결과는 같음 (위와 아래 같음)
--★RIGHT OUTER JOIN 
--INNER JOIN(교집합)+교집합에 포함되지못한 고객 테이블의 데이터
SELECT 고객.고객이름, 주문.주문제품, 주문.주문일자 
FROM 주문 RIGHT OUTER JOIN 고객 ON 주문.주문고객 = 고객.고객아이디;

--★FULL OUTER JOIN
--INNER JOIN(교집합) + 교집합에 포함되지못한 고객,주문테이블의 데이터
SELECT * FROM 고객 FULL OUTER JOIN 주문 ON 고객.고객아이디 = 주문.주문고객;

LEFT OUTER JOIN, RIGHT OUTER JOIN

---

이너,아우터 조인예제

CREATE TABLE basket_a (
id 			VARCHAR(20) NOT NULL,
fruit		VARCHAR(10) NOT NULL,
id_category VARCHAR(20)
);

CREATE TABLE basket_b (
id 			VARCHAR(20) NOT NULL,
fruit		VARCHAR(10) NOT NULL,
id_category VARCHAR(20)
);

CREATE TABLE basket_c (
id 			VARCHAR(20) NOT NULL,
recipe		VARCHAR(10) NOT NULL
);

--INSERT 
INSERT INTO basket_a VALUES ('1', 'Apple', null);
INSERT INTO basket_a VALUES ('2', 'Orange', null);
INSERT INTO basket_a VALUES ('3', 'Banana', 1);
INSERT INTO basket_a VALUES ('4', 'Cucumber', 1);

INSERT INTO basket_b VALUES ('1', 'Apple', null);
INSERT INTO basket_b VALUES ('2', 'Orange', null);
INSERT INTO basket_b VALUES ('3', 'Watermelon', 1);
INSERT INTO basket_b VALUES ('4', 'Pear', 1);

INSERT INTO basket_c VALUES ('1', '쥬스');
INSERT INTO basket_c VALUES ('2', '생과일');

-------------------------------------------------JOIN의 여러종류들 예제
--INNER JOIN (교집합)
SELECT * FROM basket_a BA  --별명 BA로 짓는다
INNER JOIN basket_b BB  --basket_a와 JOIN 할 테이블명
ON BA.fruit = BB.fruit; --JOIN을 엮을 기준(조건)
--basket_a의 fruit 컬럼의 데이터와 basket_b의 fruit 컬럼의 데이터가 같은 튜플(row)을 찾아라 

--LEFT OUTER JOIN (inner join + 왼쪽 테이블의 나머지 튜플(row))
SELECT * FROM basket_a BA
LEFT OUTER JOIN basket_b BB
ON BA.fruit = BB.fruit;

--RIGHT OUTER JOIN
SELECT * FROM basket_a BA
RIGHT OUTER JOIN basket_b BB
ON BA.fruit = BB.fruit;

--FULL OUTER JOIN
--inner join 튜플(row) + 왼쪽테이블의 나머지 튜풀(row) 더한 후에
--오른쪽 테이블의 나머지 튜플(row)
SELECT * FROM basket_a BA
FULL OUTER JOIN basket_b BB
ON BA.fruit = BB.fruit


--self join (자기자신의 컬럼중 동일한 값을 가진 컬럼을 매칭해서 조회)
SELECT BA.id, BA.id_category, BB.id, BB.id_category
FROM basket_a BA
INNER JOIN basket_a BB 
ON BA.id = BB.id_category; 
--cross join (뻥튀기(곱셈) = N*N)
--a테이블의 각 튜플마다 b테이블 각각 튜플을 연결해서 출력 
SELECT * FROM basket_a ba
CROSS JOIN basket_b bb;
--natural join *잘사용하지않음
--쓰는이유: 하나의 튜플에 여러컬럼데이터를 첨가(튜플늘리기) 
SELECT id, fruit, id_category, recipe
FROM basket_c NATURAL JOIN basket_a;

원본 링크 : https://programmerpsy.tistory.com/m/100

[SQL] 2. 데이터 조인

---

부속질의문 예제

------------------------------------ 부속질의문을 이용한 검색 (SELECT문)
--예제7-40
SELECT 제품명, 단가 
FROM 제품 
WHERE 제조업체 = 
(
	SELECT 제조업체
	FROM 제품
	WHERE 제품명 = '달콤비스킷'
);
--먼저 검색하는 순서로 분석
-- *달콤비스킷의 제조업체는 한빛제과만 존재 -> 단일행부속질의문 (비교연산자 = 이용)
SELECT 제품명, 단가 FROM 제품  WHERE 제조업체 = '한빛제과';


--예제7-41
SELECT 고객이름, 적립금 
FROM 고객 
WHERE 적립금 = 
(
	SELECT MAX(적립금) FROM 고객
);
-- *최대적립금은 단일값이므로 -> 단일행부속질의문 (비교연산자 = 이용)
SELECT 고객이름, 적립금
FROM 고객
WHERE 적립금 = 5000;


--예제7-42 
--[IN : 부속질의문의 결과값중 일치하는 것이 있으면 검색조건이 참]
SELECT 제품명, 제조업체
FROM 제품
WHERE 제품번호 IN 
(
	SELECT 주문제품
	FROM 주문
	WHERE 주문고객 = 'banana'
);
--*'banana'고객이 주문한 제품은 여러개이므로 -> 다중행부속질의문 (IN 연산자이용) 
-- 결과값이 1개이면 =, 결과값이 1개 이상이면 IN
SELECT 제품명, 제조업체
FROM 제품
WHERE 제품번호 IN ('p01','p04','p06');


--예제7-43
--[NOT IN : 부속질의문의 결과값중 일치하는 것이 없으면 검색조건이 참]
--NOT IN : IN이 아닌것을 출력 
SELECT 제품명, 제조업체
FROM 제품
WHERE 제품번호 NOT IN 
(
	SELECT 주문제품
	FROM 주문
	WHERE 주문고객 = 'banana'
);


--예제7-44
--[ALL : 부속질의문의 결과값 모두와 비교한 결과가 참이면 검색조건을 만족(비교연산자와 함께사용)]
--*판매데이터베이스에서 대한식품이 제조한 모든 제품의 단가보다 비싼 제품의 제품명, 단가, 제조업체를 검색해보자
--*대한식품이 제조한 제품은 단가가 4,500원인 그냥만두와 1,200원인 얼큰라면
SELECT 제품명, 단가, 제조업체 
FROM 제품 WHERE 단가 > ALL 
(
	SELECT 단가
	FROM 제품
	WHERE 제조업체 = '대한식품'
);


--예제7-45
--[NOT EXISTS : 부속질의문의 결과값이 하나도 존재하지 않으면 검색조건이 참]
SELECT 고객이름 FROM 고객 WHERE EXISTS 
(
	SELECT *
	FROM 주문
	WHERE 주문일자 = '2022-03-15'
	AND 주문.주문고객 = 고객.고객아이디
);
--FULL OUTER JOIN 
SELECT *
FROM 고객, 주문 
WHERE 주문일자 = '2022-03-15';

예제7-40

예제7-42

---

회원가입 문제

--------------------------------------------------------------------------------<회원가입문제>
----------------------------------------------------------1.회원가입 테이블
--INDEX_AUTO, 회원ID, 회원PW, 삭제YN, 기타사항, 생성날짜, 수정날짜
CREATE TABLE 회원가입 (
IDEX_AUTO INT GENERATED ALWAYS AS IDENTITY,--AUTO숫자증가
회원ID 	  	VARCHAR(30) NOT NULL,
회원PW		VARCHAR(30)	NOT NULL,	
삭제YN		CHAR(1) NOT NULL, --삭제 Y / N
기타사항		VARCHAR2(300),
생성날짜		DATE NOT NULL,
수정날짜		DATE NOT NULL,
PRIMARY KEY (회원ID)
);
DROP TABLE 회원가입;
SELECT * FROM 회원가입;
--INSERT 
INSERT INTO 회원가입(회원ID,회원PW,삭제YN,기타사항,생성날짜,수정날짜) VALUES(
	'kim001', '001kim', 'N', '과일을자주산다', to_date('2022/01/01', 'YYYY/MM/DD') , to_date('2022/01/01', 'YYYY/MM/DD') 
);
INSERT INTO 회원가입(회원ID,회원PW,삭제YN,기타사항,생성날짜,수정날짜) VALUES(
	'bark001', '001park', 'N', '고기를자주산다', '2022/02/01', '2022/03/01' 
);
INSERT INTO 회원가입(회원ID,회원PW,삭제YN,기타사항,생성날짜,수정날짜) VALUES(
	'yee001', '001yee', 'N', '채소를자주산다', '2022/03/01', '2022/03/01'
);
INSERT INTO 회원가입(회원ID,회원PW,삭제YN,기타사항,생성날짜,수정날짜) VALUES(
	'hwang001', '001hwang', 'N', '상품권을자주산다', '2022/06/01' , to_date(SYSDATE)
);
INSERT INTO 회원가입(회원ID,회원PW,삭제YN,기타사항,생성날짜,수정날짜) VALUES(
	'kwang001', '001kwang', 'Y', '장난감을자주산다', '2022/08/01' ,'2022/09/01'
);
INSERT INTO 회원가입(회원ID,회원PW,삭제YN,기타사항,생성날짜,수정날짜) VALUES(
	'hyun001', '001hyun', 'Y', '햄을자주산다', '2022/09/01' ,'2022/10/01'
);
INSERT INTO 회원가입(회원ID,회원PW,삭제YN,기타사항,생성날짜,수정날짜) VALUES(
	'yang001', '001yang', 'N', '과자를자주산다', '2022/10/01' ,'2022/11/01'
);

----------------------------------------------------------2.회원로그 테이블 
--(회원ID는 최원가입.회원ID를 참조한다)
--INDEX_AUTO, 회원ID, 로그정보, 생성날짜, 수정날짜
CREATE TABLE 회원로그 (
IDEX_AUTO INT GENERATED ALWAYS AS IDENTITY,--AUTO숫자증가
회원ID 	  	VARCHAR(30) NOT NULL,
로그정보		VARCHAR2(300),	
생성날짜		DATE NOT NULL,
수정날짜		DATE NOT NULL,
FOREIGN KEY(회원ID) REFERENCES 회원가입(회원ID)
);
DROP TABLE 회원로그;
SELECT * FROM 회원로그;
--INSERT 
INSERT INTO 회원로그(회원ID,로그정보,생성날짜,수정날짜) VALUES(
	'kim001', '일년에한번방문합니다', to_date('2022/01/01', 'YYYY/MM/DD') , to_date('2022/01/01', 'YYYY/MM/DD') 
);
INSERT INTO 회원로그(회원ID,로그정보,생성날짜,수정날짜) VALUES(
	'bark001', '두달에한번방문합니다', '2022/02/01', '2022/03/01' 
);
INSERT INTO 회원로그(회원ID,로그정보,생성날짜,수정날짜) VALUES(
	'yee001', '한달에한번방문합니다', '2022/03/01', '2022/03/01'
);
INSERT INTO 회원로그(회원ID,로그정보,생성날짜,수정날짜) VALUES(
	'hwang001', '한달에세번방문합니다', '2022/06/01' , to_date(SYSDATE)
);
INSERT INTO 회원로그(회원ID,로그정보,생성날짜,수정날짜) VALUES(
	'kwang001', '한달에여섯번방문합니다', '2022/08/01' ,'2022/09/01'
);
INSERT INTO 회원로그(회원ID,로그정보,생성날짜,수정날짜) VALUES(
	'hyun001', '일주일에세번방문합니다', '2022/09/01' ,'2022/10/01'
);
INSERT INTO 회원로그(회원ID,로그정보,생성날짜,수정날짜) VALUES(
	'yang001', '매일매일방문합니다', '2022/10/01' ,'2022/11/01'
);

------------------------------------------------------------3.회원정보 테이블 
--(회원ID는 회원가입.회원ID를 참조한다)
--회원ID, 닉네임, 성별, 나이, EMAIL
CREATE TABLE 회원정보 (
IDEX_AUTO INT GENERATED ALWAYS AS IDENTITY,--AUTO숫자증가
회원ID 	  	VARCHAR(30) NOT NULL,
닉네임		VARCHAR(30),	
성별			CHAR(6),
나이 			INT,
이메일		VARCHAR(50),  
FOREIGN KEY(회원ID) REFERENCES 회원가입(회원ID)
);
DROP TABLE 회원정보;
SELECT * FROM 회원정보;
--INSERT 
INSERT INTO 회원정보(회원ID,닉네임,성별,나이,이메일) VALUES(
	'kim001', '김딸기', '남성' , '30', 'kim001@naver.com'
);
INSERT INTO 회원정보(회원ID,닉네임,성별,나이,이메일) VALUES(
	'bark001', '박고기', '남성' , '20', 'bark001@naver.com'
);
INSERT INTO 회원정보(회원ID,닉네임,성별,나이,이메일) VALUES(
	'yee001',  '이염소', '여성' , '40', 'yee001@naver.com'
);
INSERT INTO 회원정보(회원ID,닉네임,성별,나이,이메일) VALUES(
	'hwang001', '황부자', '여성' , '60', NULL
);
INSERT INTO 회원정보(회원ID,닉네임,성별,나이,이메일) VALUES(
	'kwang001',  '광토이', '여성' , '10', 'kwang001@naver.com'
);
INSERT INTO 회원정보(회원ID,닉네임,성별,나이,이메일) VALUES(
	'hyun001',  '현돼지', '남성' , '40', 'hyun001@naver.com'
);
INSERT INTO 회원정보(회원ID,닉네임,성별,나이,이메일) VALUES(
	'yang001',  '양스낵', '여성' , NULL , 'yang001@naver.com'
);

--------------------------------------------------------------------------------<회원가입문제> SELECT 해결
--문제1
--회원정보 테이블에서 삭제되지않는 최원들 중 나이가 30이상인 회원ID와 닉네임과 EMAIL 컬럼을 출력하시오
SELECT 회원정보.회원ID, 회원정보.닉네임, 회원정보.이메일
FROM 회원가입
INNER JOIN 회원정보 
ON 회원가입.회원ID = 회원정보.회원ID
WHERE 회원정보.나이>=30 AND 회원가입.삭제YN = 'N';

--문제2
--회원정보 테이블에서 생성날짜가 특정날짜(예.2022년 6월 30일) 이전인 회원중에 성별이 여자인 회원의 회원ID와 로그정보를 출력하시오
SELECT 회원ID, 로그정보 
FROM 회원로그
WHERE 회원ID IN
(
	SELECT 회원ID
	FROM 회원정보
	WHERE 생성날짜 <'2022-06-30' AND 성별 = '여성'
);

문제1번: 예제7-38 inner join 예제 참조 / 문제 2번: 부속질의문 예제7-42 참고

문제1번

문제2