# 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:
Notice that here we have two
id columns: one for the
users table and one for the
# Selecting only certain columns
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
# 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
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
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.
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!