What is the difference between using multiple methods and unions?

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 should I use a connection? And what will it look like?

Answer 1

Compounds are synthetic sugar that is easier to read.

Your request will look like this:

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

With more than two tables, joins help make the query more readable by keeping the conditions associated with the table in one place.

Answer 2

The keyword joinis a new way to join tables.

When I learned SQL, it does not exist yet, so the join was done as you show in your question.

, , :

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

: left outer join, right outer join full join, .

Answer 3

The 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