Joins

Join

• a join operation takes two relations and returns the result of their merging
• a join operation is a cartesian product that is followed by the application of some condition
• the result is frequently used in the FROM clause

Natural Join

• the natural join returns tuples which have the same values for all common attributes, and retains a single copy of each common column
• the natural join is the simplest way to normalise

Example

table customer

customer_ID	customer_name
100	Hello
200	World

table seller

seller_ID	customer_ID	seller_name
500	100	Panda
560	200	Cat

SELECT * 
FROM customer NATURAL JOIN seller

returns

customer_ID	seller_ID	customer_name	seller_name
100	500	Hello	Panda
200	560	World	Cat

Outer Join

• outer join is a type of join that involves zero information loss
• all rows are preserved between relations, adding null values if needed
• we can use left outer join, right outer join, or full outer join

Inner Join

• inner join is the default type of join, unless mentioned otherwise
• inner join preserves consistency (no nulls present), but may cause loss of data

Example

customer_ID	customer_name
100	Hello
200	World
300	Is
400	A
500	Square

seller_ID	seller_name	customer_ID
150	Green	100
250	Is	200
350	My	600
450	Pepper	800

seller INNER JOIN customer ON seller.customer_ID = customer.customer_ID

seller_ID	seller_name	customer_ID	customer_name
150	Green	100	Hello
250	Is	200	World

seller LEFT JOIN customer ON seller.customer_ID = customer.customer_ID

(left join is shorthand for left outer join)

seller_ID	seller_name	customer_ID	customer_name
150	Green	100	Hello
250	Is	200	World
350	My	null	null
450	Pepper	null	null

seller FULL OUTER JOIN customer ON seller.customer_ID = customer.customer_ID

seller_ID	seller_name	customer_ID	customer_name
150	Green	100	Hello
250	Is	200	World
350	My	null	null
450	Pepper	null	null
null	null	300	Is
null	null	400	A
null	null	500	Square