SQL-内部联接 [英] SQL - Inner join

问题描述

我有一个查询，如下所示:

I have an query as following:

SELECT t1.onderzoeksNR, t1.antwoorden as b1_m1_v1_items, t1.commentaar as b1_m1_v1_toe, t2.antwoorden as b1_m1_v2_items, t2.commentaar as b1_m1_v2_toe
FROM antwoorden t1
INNER JOIN antwoorden t2
ON t1.onderzoeksNR = t2.onderzoeksNR
WHERE t1.behandeltraject = 1 AND t1.meting = 1 AND t1.vragenlijst = 1
AND t2.behandeltraject = 1 AND t2.meting = 1 AND t2.vragenlijst = 2

这将输出以下内容:

这正是我想要的.

但是，问题是这样的:

如果我添加另一个内部联接

If I add another inner join

inner join antwoorden t3 
on t1.onderzoeksNR = t3.onderzoeksNR

但是像这样:

t3.behandeltraject = 1 AND t3.meting = 2 AND t3.vragenlijst = 1

查询将返回0条记录.

很显然，这是因为antwoorden中没有没有行包含所要求的属性.

Obviously, this is because there are no rows in antwoorden containing the asked properties.

(t3.behandeltraject = 1 AND t3.meting = 2 AND t3.vragenlijst = 1)

我的问题:

1. 我如何仍可以检索开始时的内容，

1. How can I still retrieve what I had in the beginning,

然后向其添加内部联接antwoorden t3，但是具有空值(如果未找到带有t3.behandeltraject = 1 AND t3.meting = 2 AND t3.vragenlijst = 1的行)?

And then add the inner join antwoorden t3 to it, but then with empty values (if no rows with t3.behandeltraject = 1 AND t3.meting = 2 AND t3.vragenlijst = 1 are found)?

为了说明我的意思:

而不是返回0条记录.

任何帮助将不胜感激

推荐答案

您需要LEFT JOIN，并且需要将一些WHERE条件移至JOIN条件中

You need a LEFT JOIN and you need to move some WHERE criteria into the JOIN criteria:

SELECT t1.onderzoeksNR, t1.antwoorden as b1_m1_v1_items, t1.commentaar as b1_m1_v1_toe, t2.antwoorden as b1_m1_v2_items, t2.commentaar as b1_m1_v2_toe
FROM antwoorden t1
INNER JOIN antwoorden t2
ON t1.onderzoeksNR = t2.onderzoeksNR
LEFT JOIN antwoorden t3 ON t1.onderzoeksNR = t3.onderzoeksNR
     AND t3.behandeltraject = 1 AND t3.meting = 2 AND t3.vragenlijst = 1
WHERE t1.behandeltraject = 1 AND t1.meting = 1 AND t1.vragenlijst = 1
AND t2.behandeltraject = 1 AND t2.meting = 1 AND t2.vragenlijst = 2

LEFT JOIN将允许您返回前两个表中的记录，无论它们是否是JOIN到t3，但是如果您使用WHERE条件在t3.meting = 2等位置过滤记录，您将删除结果.将WHERE条件移到JOIN中将返回与初始查询相同的记录，因为您添加的t3中的每一列都将返回NULL，因为没有一个满足条件.

LEFT JOIN will allow for records in your first two tables to return regardless of whether they JOIN to t3, but if you use WHERE criteria to filter records where t3.meting = 2, etc. you'll remove results. Moving that WHERE criteria into the JOIN will return the same records as your initial query, just NULL for every column from t3 you add in, since none meet the criteria.

这篇关于SQL-内部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！