CS/IS/Computer-Related FAQ

Topic: SQL

Question
What are joins? What is the difference between all of the different types of joins?

Answer
A join is the combination of columsn across multiple tables. This differs from a union, in that unions are combinations of rows.

The join syntax looks something like:
SELECT * FROM tbl1 [LEFT | RIGHT | INNER | OUTER] JOIN tbl2 ON tbl1.id=tbl2.id;

The syntax of course can have slight variations with additional or alternative keywords. The line above will append the columns of tbl2 onto tbl1, aligning records where the id field of tbl1 is equal to the id field of tbl2.

An inner join will return all those records in tbl1 that have a match in tbl2.
A left join will return all records in tbl1 regardless of it having a match in tbl2. Any missing fields will be filled with NULL.
A right join will return all records in tbl2 regardless of it having a match in tbl1. It is important to note that a left join can be made into a right join by swapping the order of the tables.
An outer join will return the cross product of the tables, meaning all possible combinations from tbl1 and tbl2.

This concludes today’s tip. Thank you for joining me. A har har.