Chapter 10: How to Join Tables in SQL
Chapter 10: How to Join Tables in SQL κ΄λ ¨
Joins are one of the most important features that SQL offers. Joins allow us to make use of the relationships we have set up between our tables. In short, joins allow us to query multiple tables at the same time.
INNER JOIN
The simplest and most common type of join in SQL is the INNER JOIN. By default, a JOIN command is an INNER JOIN.
An INNER JOIN returns all of the records in table_a that have matching records in table_b, as demonstrated by the following Venn diagram.
The ON clause
In order to perform a join, we need to tell the database which fields should be "matched up". The ON clause is used to specify these columns to join.
SELECT *
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;
The query above returns all the fields from both tables. The INNER keyword doesn't have anything to do with the number of columns returned - it only affects the number of rows returned.
Namespacing on Tables
When working with multiple tables, you can specify which table a field exists on using a .. For example:
table_name.column_name
SELECT students.name, classes.name
FROM students
INNER JOIN classes on classes.class_id = students.class_id;
The above query returns the name field from the students table and the name field from the classes table.
LEFT JOIN
A LEFT JOIN will return every record from table_a regardless of whether or not any of those records have a match in table_b. A left join will also return any matching records from table_b.
Here is a Venn diagram to help visualize the effect of a LEFT JOIN.
A small trick you can do to make writing the SQL query easier is define an alias for each table. Here's an example:
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.id;
Notice the simple alias declarations e and d for employees and departments respectively.
Some developers do this to make their queries less verbose. That said, I personally hate it because single-letter variables are harder to understand the meaning of.
RIGHT JOIN
A RIGHT JOIN is, as you may expect, the opposite of a LEFT JOIN. It returns all records from table_b regardless of matches, and all matching records between the two tables.
SQLite Restriction
SQLite does not support right joins, but many dialects of SQL do. If you think about it, a RIGHT JOIN is just a LEFT JOIN with the order of the tables switched, so it's not a big deal that SQLite doesn't support the syntax.
FULL JOIN
A FULL JOIN combines the result set of the LEFT JOIN and RIGHT JOIN commands. It returns all records from both from table_a and table_b regardless of whether or not they have matches.
SQLite
Like RIGHT JOINs, SQLite doesn't support FULL JOINs but they are still important to know.