Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I do an Optional JOIN using SQL

Tags:

sql

join

tsql

mysql

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
like image 261
ditto Avatar asked May 11 '15 03:05

ditto


People also ask

Does SQL have anti join?

Anti-join is used to make the queries run faster. It is a very powerful SQL construct Oracle offers for faster queries. Anti-join between two tables returns rows from the first table where no matches are found in the second table. It is opposite of a semi-join.

Which keyword is optional when doing natural join or join on?

ON. An INNER JOIN combines data from two tables where there is a match on the joining column(s) in both tables. Remember, the INNER keyword is optional.

Which type of join does not require each record?

An outer join does not require each record in the two joined tables to have a matching record. There are three types of outer joins. Left outer joins, right outer joins, and full outer joins.


1 Answers

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.

like image 187
Nic Avatar answered Oct 01 '22 03:10

Nic