CODEWITHSUNDEEP
sqljoin
Thomaschaaf
Thomaschaaf

Reputation: 18194

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

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?

Upvotes: 4

Views: 2679

Answers (3)

Guffa
Guffa

Reputation: 700630

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.

Upvotes: 2

Joe Albahari
Joe Albahari

Reputation: 30954

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

Upvotes: 0

Andomar
Andomar

Reputation: 238216

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.

Upvotes: 4

Related Questions