How can I do an Optional JOIN using SQL

Question

I want to return all rows from biometrics, and grab extra data if the JOIN exists. However my current query only returns rows when the userid matches.

How may I fix this so it returns all biometric rows, and extra data from the JOIN if it exists?

SELECT b.id as id, g.date
FROM `biometrics` as b
INNER JOIN `users goals` as g ON biometricid = b.id and userid = $user->id

Answer 1

Use a left outer join:

SELECT b.id as id, g.date
FROM `biometrics` as b
LEFT OUTER JOIN `users goals` as g ON biometricid = b.id and userid = $user->id

Nice examples of different join types can be found here.