Now, we can start querying our tables. Make sure you have already logged into your database before following along with this demo. See the Database Accounts guide for details.
It would be nice if we could see the offices associated with each phone number. A simple way to combine tables is:
SELECT * FROM offices, phones;
However, this is not what we want. It performs something similar to a cross product. We could filter out the undesired rows follows:
SELECT * FROM offices, phones
WHERE offices.id = phones.office_id;
In the statement above, we use the WHERE constraint to only show those rows where the id columns match. We can even filter by multiple criteria:
SELECT * FROM offices, phones
WHERE offices.id = phones.office_id
AND description != 'Office';
However, this is not the best way forward if we want to combine data across multiple tables. Instead, we should use a JOIN operation to explicitly state the relationship between the tables.
Rather than creating a bunch of rows and then filtering out invalid rows, we can use an INNER JOIN operation instead on the office_id and id columns. The statement and the expected output are:
SELECT * FROM offices
INNER JOIN phones
ON offices.id = phones.office_id;
The order of tables or columns for the ON clause does not change the rows, but swapping the order of tables does change the order of the columns:
SELECT * FROM phones
INNER JOIN offices
ON phones.office_id = offices.id;