What's the difference between just using multiple froms and joins?

Question

Say I have this query:

SELECT bugs.id, bug_color.name FROM bugs, bug_color
    WHERE bugs.id = 1 AND bugs.id = bug_color.id

Why would I use a join? And what would it look like?

Answer 1

Joins are synticatic sugar, easier to read.

Your query would look like this with a join:

SELECT bugs.id, bug_color.name 
FROM bugs
INNER JOIN bug_color ON bugs.id = bug_color.id
WHERE bugs.id = 1

With more then two tables, joins help make a query more readable, by keeping conditions related to a table in one place.

Answer 2

Join syntax allows for outer joins, so you can go:

SELECT bugs.id, bug_color.name 
FROM bugs, bug_color 
LEFT OUTER JOIN bug_color ON bugs.id = bug_color.id
WHERE bugs.id = 1

Answer 3

The join keyword is the new way of joining tables.

When I learned SQL it did not yet exist, so joining was done the way that you show in your question.

Nowadays we have things like joins and aliases to make the queries more readable:

select
    b.id, c.name
from
    bugs as b
inner join
    bug_color as c on c.id = b.id
where
    b.id = 1

Also there are other variations of joins, as left outer join, right outer join and full join, that is harder to accomplish with the old syntax.