JOIN: searching in multiple tables

Now that we've learned about relational data, we will learn how to ask our RDBMS for data from multiple tables at once.

This is how we could get the account number and name of our users:

SELECT * FROM users JOIN accounts ON users.id = accounts.holder_id;

The important part of this query is:

users JOIN accounts ON users.id = accounts.holder_id;

This tells the database to join together the users and the accounts tables, matching each row by making sure that the id column of each user row matches the holder_id column of each account row.

We then select all columns (SELECT *) of the resulting joined table:

id	first_name	surname	id	holder_id	number
1	John	Smith	3	1	9586859
2	Rolf	Smith	4	2	1234567
3	Anne	Pun	1	3	8375841
4	Robert	Baratheon	2	4	1343545

Notice that here we have two id columns: one for the users table and one for the accounts table.

Selecting only certain columns

Instead of SELECT *, we can tell the database which columns we would like to get back:

SELECT users.*, accounts.number
FROM users
JOIN accounts
ON users.id = accounts.holder_id;

This query asks for all columns of the users table, and only the number column of the accounts table.

Joining in reverse

The order of the JOIN sometimes matters, and we will learn more about when that is the case. But often, it does not. This is just the same:

SELECT users.*, accounts.number
FROM accounts
JOIN users
ON users.id = accounts.holder_id;

And so is this (reversing the ON clause):

SELECT users.*, accounts.number
FROM accounts
JOIN users
ON accounts.holder_id = users.id;

Joining on non-related columns

You don't have to use a primary key and foreign key pair on the ON clause.

What I mean by this is that if the accounts.holder_id was not a foreign key, the query would still work. However, it would be slower.

TIP

Whenever you are going to access data in a way that suggest relationship, such as with a JOIN, try to use primary and foreign keys. That will speed up queries substantially!