帮助构建图片库的数据库

Question

i have two tables,

a) albums

b)pictures

i am using the following query to select the albums.id, albums.cover_picture_id, pictures.url where albums.cover_picture_id = pictures.id, i want to perform the following

select all the values from albums, but it should also fetch the pictures.url with reference to albums.cover_picture_id

for the purpose i am using the following SELECT STATEMENT

SELECT 
albums.id,
albums.cover_picture_id,
pictures.url
FROM albums 
JOIN pictures 
ON 
(
albums.cover_picture_id = pictures.id
)

it works fine but i have a problem here, the table entity albums.cover_picture_id may contain the value 0 which does not exist in pictures.id so it will not fetch the value if it finds 0, i would want to fetch all the albums from the albums table even if it finds 0. how do i add the clause in the select statement.

thank you

Answer 1

use LEFT JOIN like this:

SELECT 
albums.id,
albums.cover_picture_id,
pictures.url
FROM albums 
LEFT JOIN pictures 
ON 
(
albums.cover_picture_id = pictures.id
)

Excerpt from Wikipedia:

The result of a left outer join (or simply left join) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result—but with NULL in each column from B. This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate). If the right table returns one row and the left table returns more than one matching row for it, the values in the right table will be repeated for each distinct row on the left table.