使用INNER JOIN时，意外的数据库输出 [英] Unexpected database output when using INNER JOIN

问题描述

我有以下SQL查询

SELECT 
       r.BEZEICHNUNG AS BEZEICHNUNG, r.ID AS ID,
       ra.BEZEICHNUNG AS raumBEZEICHNUNG, ra.ID AS raumID
FROM 
       RAUM r
       INNER JOIN RAZUORDNUNG rz ON rz.RAUM_ID = r.ID
       INNER JOIN RAUMATTRIBUTE ra ON rz.RAUMATTRIBUTE_ID = ra.ID 

WHERE
       RAUMKLASSE_ID = ISNULL(@Raumklasse_ID, RAUMKLASSE_ID) 
AND    STADT_ID = ISNULL(@Stadt_ID, STADT_ID) 
AND    GEBAEUDE_ID = ISNULL(@Gebaeude_ID, GEBAEUDE_ID) 
AND    REGION_ID = ISNULL(@Region_ID, REGION_ID)
AND    RAUMATTRIBUTE_ID = ISNULL(@Raumattribute_ID, RAUMATTRIBUTE_ID)     

不过，我觉得这事是不对的。

But I think that something is wrong with that.

例如：

如果我把三在浏览器中的 RAUMKLASSE_ID 文本框并调用我的方法返回只有一个房间。但也有6个房间的ID。奇怪的是，如果我删除了两个 INNER JOIN 和我的 SELECT ，这样第二行：

If I put three in the RAUMKLASSE_ID textfield in the browser and invoke my method it returns only one room. But there are six rooms with that ID. The strange thing is, that if I remove the two INNER JOIN and the second line of my SELECT, like this:

SELECT 
       r.BEZEICHNUNG AS BEZEICHNUNG, r.ID AS ID
FROM 
       RAUM r
WHERE
       RAUMKLASSE_ID = ISNULL(@Raumklasse_ID, RAUMKLASSE_ID) 
AND    STADT_ID = ISNULL(@Stadt_ID, STADT_ID) 
AND    GEBAEUDE_ID = ISNULL(@Gebaeude_ID, GEBAEUDE_ID) 
AND    REGION_ID = ISNULL(@Region_ID, REGION_ID)
AND    RAUMATTRIBUTE_ID = ISNULL(@Raumattribute_ID, RAUMATTRIBUTE_ID)

它返回的六间房，这是正确的。我不知道这个问题是我的查询内容。也许有人可以帮我吗？

it is returning the six rooms, which is correct. I don't know what the problem is with my query. Maybe someone can help me with that?

在此先感谢

推荐答案

这是预期的行为，因为：

This is the expected behaviour, since:

FROM RAUM r
INNER JOIN RAZUORDNUNG rz ON rz.RAUM_ID = r.ID
INNER JOIN RAUMATTRIBUTE ra ON rz.RAUMATTRIBUTE_ID = ra.ID  

会得到你只有在表中发现的客房 RAUM ， RAZUORDNUNG 和 RAUMATTRIBUTE 表中，除去这些 INNER JOIN 旨意让你所有这一切满足您的条件 RAUM 表中的房间，检查这些页面了解更多详细的加入取值：

Will get you only the rooms that are found in the tables RAUM, RAZUORDNUNG and RAUMATTRIBUTE tables, removing these INNER JOINs will get you all the rooms from the RAUM table that satisfy your condition, check these pages for more details about JOINs:

• 的为加入取值。


• 维基百科条目约加入取值

• A visual explanation for JOINs.
• Wikipedia article about JOINs

这篇关于使用INNER JOIN时，意外的数据库输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！