如何使用SQL进行可选的JOIN [英] How can I do an Optional JOIN using SQL

问题描述

我想从biometrics返回所有行，并在存在JOIN的情况下获取更多数据.但是，我当前的查询仅在用户ID匹配时才返回行.

我该如何解决这个问题，以便它返回所有生物识别行，如果存在，还会返回来自JOIN的额外数据?

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

解决方案

使用左外部联接:

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

可以在此处找到不错的示例. >

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

解决方案

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.

这篇关于如何使用SQL进行可选的JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！