Joined Relations n Join operations take two relations
- Slides: 7
Joined Relations n Join operations take two relations and return as a result another relation. n These additional operations are typically used as subquery expressions in the from clause n Join condition – defines which tuples in the two relations match, and what attributes are present in the result of the join. n Join type – defines how tuples in each relation that do not match any tuple in the other relation (based on the join condition) are treated. Database System Concepts - 5 th Edition 4. 1 ©Silberschatz, Korth and Sudarshan
Joined Relations – Datasets for Examples n Relation loan n Relation borrower n Note: borrower information missing for L-260 and loan information missing for L-155 Database System Concepts - 5 th Edition 4. 2 ©Silberschatz, Korth and Sudarshan
Joined Relations – inner/outer join n loan inner join borrower on loan_number = borrower. loan_number n loan left outer join borrower on loan_number = borrower. loan_number Database System Concepts - 5 th Edition 4. 3 ©Silberschatz, Korth and Sudarshan
Joined Relations – natural join n loan natural inner join borrower n loan natural right outer join borrower Database System Concepts - 5 th Edition 4. 4 ©Silberschatz, Korth and Sudarshan
Natural Join n Relations r, s: A B C D B D E 1 2 4 1 2 a a b 1 3 1 2 3 a a a b b r r s s A B C D E 1 1 2 a a b
Natural Join (Cont’d) n 연산 규칙: l 동일한 이름의 컬럼 간에 equi-join l 그런 컬럼 쌍이 여럿이면 equi-join 조건들을 AND로 연결 l 결과 테이블에는 동일한 이름의 컬럼은 하나만 output n 앞 쪽의 예: select distinct r. A, r. B, r. C, r. D, s. E from r, s where r. B = s. B and r. D = s. D 또는 select distinct r. A, s. B, r. C, s. D, s. E from r, s where r. B = s. B and r. D = s. D Copyright CAU DBLAB
Joined Relations – natural join with using clause n loan full outer join borrower using (loan_number) n Find all customers who have either an account or a loan (but not both) at the bank. select customer_name from (depositor natural full outer join borrower ) where account_number is null or loan_number is null Database System Concepts - 5 th Edition 4. 7 ©Silberschatz, Korth and Sudarshan
