SQL is a versatile language–there are many ways to get the job done.
One way to compare information from multiple tables is the JOIN command, but there are sometimes alternatives.
Let’s say I’ve got two tables, office and person:
Let’s say I need to know which people have printers. I could do a JOIN ON office_room = office_num. On the other hand, if you just select FROM multiple tables at the same time, you can filter with WHERE.
SELECT name, has_printer FROM office, person WHERE office_room=office_num;
It turns out that SELECT FROM tbl1,tbl2 is actually the same as a CROSS JOIN, also known as the cartesian product of two tables. This joins every row of the first table with every row of the second table. Use WHERE to filter out only the correct rows you want.
Now let’s say I want to know the number of chairs left in each office. One way would be to JOIN, something like:
num_chairs – count(name)
ON office_room = office_num
GROUP BY office_room;
But another option is a correlated subquery, where the subquery refers to a column in the outer query, and doesn’t use JOIN at all:
WHERE office_room = office.office_num)
Correlated subqueries definitely have a performance hit, since each row is evaluated separately, but they can be quite handy.
Note: These recommendations aren’t best practices, but handy syntax to be aware of and are sometimes convenient.