在SQL查询中获得NULL。

I have three tables:

我有三个表:

Lesson
教训
Subject
主题
Question
问题

My query:

我的查询:

SELECT    
    LE.LessonId, LE.LessonName,
    SUB.SubjectName,
    QU.QuestionId, QU.QuestionName
FROM 
    Lesson LE
INNER JOIN 
    Subject SUB ON SUB.LessonId = LE.LessonId
INNER JOIN 
    Question QU ON QU.LessonId = LE.LessonId
WHERE 
    LE.LessonPageId = 1552
    AND SUB.IsActive = 1
    AND QU.IsActive = 1

I am getting the null result because of "Question" table is NULL.

因为“问题”表是空的，所以我得到了空结果。

But I want the result in spite of the "Question" table is NULL.

但我想要的结果是，尽管“问题”表是空的。

Thanks in advance.

提前谢谢。

1 个解决方案

#1

You are not getting a "NULL" result. You are getting an empty result set. NULL is a value within a single column.

你不会得到一个“空”结果。您将得到一个空的结果集。NULL是一个列中的值。

If this is the case, then you want left join. This would look like:

如果是这种情况，那么需要左连接。这样子:

SELECT LE.LessonId, LE.LessonName, SUB.SubjectName,
       QU.QuestionId, QU.QuestionName
FROM Lesson LE LEFT JOIN
     Subject SUB
     ON SUB.LessonId = LE.LessonId AND SUB.IsActive = 1 LEFT JOIN
     Question QU
     ON QU.LessonId = LE.LessonId AND QU.IsActive = 1
WHERE LE.LessonPageId = 1552;

Some comments.

一些评论。

When you use LEFT JOIN you pretty much should be using it for all joins (this is not true all the time, but it is good guidance). A LEFT JOIN keeps all rows in the first table, even when the ON conditions would filter the results.

当您使用左连接时，您应该将它用于所有的连接(这不是始终正确的，但它是很好的指导)。左连接保留第一个表中的所有行，即使ON条件会过滤结果。

Also note that two of the conditions in the WHERE clause were moved to ON clauses. This is needed -- otherwise the WHERE would filter out non-matching rows.

还要注意WHERE子句中的两个条件被移动到ON子句中。这是必需的——否则WHERE将过滤不匹配的行。

#1