How do I convert this to use JOINS?

Question

How do I convert this to use JOINS?

SELECT j.job_id, j.name AS job_name, a.name AS advertiser_name, j.time_added, j.active, j.moderated
FROM jobs j, advertisers a
WHERE a.advertiser_id = j.advertiser_id

Answer 1

You could write that with a join like:

SELECT  *
FROM    jobs j
JOIN    advertisers a 
ON      a.advertiser_id = j.advertiser_id

Answer 2

SELECT j.job_id, j.name AS job_name, a.name AS advertiser_name, j.time_added, j.active, j.moderated from
jobs j join advertisers a
on a.advertiser_id = j.advertiser_id

Answer 3

To elaborate on JOINS here are a couple examples:

Your example is an INNER JOIN- however, you used "implicit join notation" here is the explicit way of notating this:

SELECT j.job_id, j.name AS job_name, a.name AS advertiser_name, 
j.time_added, j.active, j.moderated
FROM jobs j
    INNER JOIN advertisers a
    ON j.advertiser_id = a.advertiser_id

Depending on your desired outcome you may elect to use an OUTER JOIN instead. The difference from an INNER JOIN being that the INNER JOIN will only find records that have a matching record in the other table. Conversely, using an OUTER JOIN you can specificy if you want records from your jobs table or your advertisers table to appear if they do not have a corresponding record in the other table.

In this example, the query will find all records in the jobs table regardless of a match in the advertisers table:

SELECT j.job_id, j.name AS job_name, a.name AS advertiser_name, 
j.time_added, j.active, j.moderated
FROM jobs j
    LEFT OUTER JOIN advertisers a
    ON j.advertiser_id = a.advertiser_id

This query, however, will find all records in the advertisers table regardless of a match in the jobs table:

SELECT j.job_id, j.name AS job_name, a.name AS advertiser_name, 
j.time_added, j.active, j.moderated
FROM jobs j
    RIGHT OUTER JOIN advertisers a
    ON j.advertiser_id = a.advertiser_id

The LEFT or RIGHT corresponds to the which side of the ' = ' the table is on.

A FULL OUTER JOIN will return all records from each table regardless of matches:

SELECT j.job_id, j.name AS job_name, a.name AS advertiser_name, 
j.time_added, j.active, j.moderated
FROM jobs j
    FULL OUTER JOIN advertisers a
    ON j.advertiser_id = a.advertiser_id