DB 조인 방법

[1] Join types  1. equi join  2. inner join  3. left outer join  4. right outer join  5. full outer join [2] Join condition (equi 조인 제외, equi 조인은 where 절로 조건 기제)  1. natural  2. on  3. using (A1, A2, ..., An)  (natural, using 은 조인기준 어트리뷰트가 하나만 보임.  on 은 조인기준 어트리뷰트가 각각 다 보임)

loan

loan_number	branch_name	amount
170	Downtown	3000
230	Redwood	4000
260	Perridge	1700

Borrower

customer_name	loan_number
Jones	170
Smith	230
Hayes	155

/////////////////////////////

// Equi Join

/////////////////////////////

select * from loan, borrower where loan.loan_number = borrower.loan_number;

loan_number	branch_name	amount	customer_name	loan_number
170	Downtown	3000	Jones	170
230	Redwood	4000	Smith	230

/////////////////////////////

// Inner join

/////////////////////////////

select * from loan inner join borrower on loan.loan_number = borrower.loan_number;

loan_number	branch_name	amount	customer_name	loan_number
170	Downtown	3000	Jones	170
230	Redwood	4000	Smith	230

select * from loan inner join borrower using (loan_number);

= select * from loan natural inner join borrower;

= select * from loan natural join borrower;

loan_number	branch_name	amount	customer_name
170	Downtown	3000	Jones
230	Redwood	4000	Smith

/////////////////////////////

// Left outer join

/////////////////////////////

select * from loan left outer join borrower on loan.loan_number = borrower.loan_number;

loan_number	branch_name	amount	customer_name	loan_number
170	Downtown	3000	Jones	170
230	Redwood	4000	Smith	230
260	Perridge	1700	-	-

select * from loan left outer join borrower using (loan_number);

= select * from loan natural left outer join borrower;

loan_number	branch_name	amount	customer_name
170	Downtown	3000	Jones
230	Redwood	4000	Smith
260	Perridge	1700	-

select * from borrower left outer join loan on loan.loan_number = borrower.loan_number;

customer_name	loan_number	loan_number	branch_name	amount
Jones	170	170	Downtown	3000
Smith	230	230	Redwood	4000
Hayes	155	-	-	-

select * from borrower left outer join loan using (loan_number);

= select * from borrower natural left outer join loan;

customer_name	loan_number	branch_name	amount
Jones	170	Downtown	3000
Smith	230	Redwood	4000
Hayes	155	-	-

/////////////////////////////

// Full outer join

/////////////////////////////

select * from loan full outer join borrower on loan.loan_number = borrower.loan_number;

loan_number	branch_name	amount	customer_name	loan_number
170	Downtown	3000	Jones	170
230	Redwood	4000	Smith	230
260	Perridge	1700	-	-
-	-	-	Hayes	155

select * from loan full outer join borrower using (loan_number);

= select * from loan natural full outer join borrower;

loan_number	branch_name	amount	customer_name
170	Downtown	3000	Jones
230	Redwood	4000	Smith
260	Perridge	1700	-
155	-	-	Hayes