Join Expressions

Lecture 5 · a join takes two relations and returns another relation. It is a Cartesian product restricted to the tuples that match under some condition, and it also decides which attributes appear in the result. Joins are normally written as subquery expressions in the from clause.

The two sample relations

course

course_id	title	dept_name	credits
BIO-301	Genetics	Biology	4
CS-190	Game Design	Comp. Sci.	4
CS-315	Robotics	Comp. Sci.	3

prereq

course_id	prereq_id
BIO-301	BIO-101
CS-190	CS-101
CS-347	CS-101

Observe the mismatch — this is the whole point of the chapter.CS-315 has no prereq row, and CS-347 has no course row. An inner join loses both; an outer join keeps them and fills the missing side with NULL.

Inner join (theta join)

inner join

course inner join prereq
  on course.course_id = prereq.course_id

course_id	title	dept_name	credits	prereq_id	course_id
BIO-301	Genetics	Biology	4	BIO-101	BIO-301
CS-190	Game Design	Comp. Sci.	4	CS-101	CS-190

Inner join vs natural join — the exam’s favourite question.

inner join … on uses an explicit condition and keeps both copies of the common attribute (see the two course_id columns above).
natural join matches on all attributes with the same name automatically and keeps only one copy of each.
Natural join is dangerous when two tables accidentally share a column name (e.g. name) — it silently adds an unwanted equality.
on can express any predicate (<, >, ≠) — that is why it is called a theta join; natural join is only equality on same-named columns.

Outer joins — “avoid loss of information”

An outer join computes the inner join and then adds back the tuples of one relation that found no partner, padding the other side with null values.

Kind	Keeps	NULLs	SQL
Inner join	Only matching rows from both sides	never invents NULLs	`A inner join B on A.x = B.x`
Left outer join ⟕	All rows of the LEFT table + matches	right columns NULL when unmatched	`A natural left outer join B`
Right outer join ⟖	All rows of the RIGHT table + matches	left columns NULL when unmatched	`A natural right outer join B`
Full outer join ⟗	All rows of BOTH tables	either side may be NULL	`A natural full outer join B`

The Course / CourseFaculty example

Course

CourseID	CourseName
c100	Database
c101	Mechanics
c102	Electronics

CourseFaculty

CourseID	Faculty
c100	Syeed
c102	Razib
c104	Asif

Left outer join

left outer join

Course natural left outer join CourseFaculty

Course.CourseID	CourseName	CourseFaculty.CourseID	Faculty
c100	Database	c100	Syeed
c101	Mechanics	NULL	NULL
c102	Electronics	c102	Razib

Use case: “find all courses for which no faculty is assigned yet” — take the left outer join and keep the rows where Faculty is null.

Right outer join

right outer join

Course natural right outer join CourseFaculty

Course.CourseID	CourseName	CourseFaculty.CourseID	Faculty
c100	Database	c100	Syeed
c102	Electronics	c102	Razib
NULL	NULL	c104	Asif

Use case: “find all unregistered courses that already have a faculty allocated”.

Full outer join

full outer join

Course natural full outer join CourseFaculty

Course.CourseID	CourseName	CourseFaculty.CourseID	Faculty
c100	Database	c100	Syeed
c101	Mechanics	NULL	NULL
c102	Electronics	c102	Razib
NULL	NULL	c104	Asif

A full outer join answers both questions at once: unmatched courses and unmatched faculty allocations. MySQL has no FULL OUTER JOIN — emulate it with LEFT JOIN … UNION … RIGHT JOIN.

Exam checklist. (1) A join = Cartesian product + match condition. (2) Inner join drops unmatched rows on both sides. (3) Left/right/full outer joins keep the unmatched side and pad with NULL. (4) on keeps duplicate columns, natural merges them. (5) “Which rows are missing?” questions are always an outer join + IS NULL filter.