SQL question

t-sql code

po114477 2018-10-08 12:48:30 ‧ 1392 瀏覽

what's the difference of this two code?
I got all NULL on the First code
And the Second one is OK

USE DataEntry_Test
SELECT TOP 100 A.Ref_Category,A.Ref_att01,
C.ATV_Attribute_Value_Description AS ATV01
FROM dbo.reference AS A,dbo.Category_Attribute AS B,
dbo.Attribute_Type_Value AS C
WHERE B.ref_cat_att1=C.ATV_Attribute_Type
AND A.Ref_att01=C.ATV_Attribute_Value
AND A.Ref_Category= B.Ref_Category
AND A.Ref_Category= C.ATV_Category

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

純真的人

iT邦大師 1 級 ‧ 2018-10-08 13:16:37

最佳解答

因為第一個SQL資料，是 A表 * B表 * C表
所以會有Null資料交集不到~

SELECT TOP 100 A.Ref_Category
,A.Ref_att01
,(
	SELECT C.ATV_Attribute_Value_Description
	WHERE B.ref_cat_att1 = C.ATV_Attribute_Type
	AND A.Ref_att01 = C.ATV_Attribute_Value
	AND A.Ref_Category = B.Ref_Category
	AND A.Ref_Category = C.ATV_Category
) AS ATV01
FROM dbo.reference AS A
,dbo.Category_Attribute AS B
,dbo.Attribute_Type_Value AS C

第二個SQL資料，是 A表 * B表 * C表並指定交集資料~
所以一定沒有Null問題~

SELECT TOP 100 A.Ref_Category
,A.Ref_att01,
C.ATV_Attribute_Value_Description AS ATV01
FROM dbo.reference AS A
,dbo.Category_Attribute AS B
,dbo.Attribute_Type_Value AS C
WHERE B.ref_cat_att1 = C.ATV_Attribute_Type
AND A.Ref_att01  =C.ATV_Attribute_Value
AND A.Ref_Category = B.Ref_Category
AND A.Ref_Category = C.ATV_Category

重點：有無 Where 的篩選

回應 3
分享
檢舉

po114477 iT邦新手 5 級 ‧ 2018-10-08 14:09:25 檢舉

Thank you
and I still need to create a column like this,
How can I fix it? without NULL

USE DataEntry_Test
SELECT TOP 100 A.Ref_Category,A.Ref_att01,
(SELECT C.ATV_Attribute_Value_Description
WHERE B.ref_cat_att1=C.ATV_Attribute_Type
AND A.Ref_att01=C.ATV_Attribute_Value
AND A.Ref_Category= B.Ref_Category
AND A.Ref_Category= C.ATV_Category)AS ATV01,
(SELECT C.ATV_Attribute_Value_Description
WHERE B.ref_cat_att2=C.ATV_Attribute_Type
AND A.Ref_att02=C.ATV_Attribute_Value
AND A.Ref_Category= B.Ref_Category
AND A.Ref_Category= C.ATV_Category)AS ATV02
FROM dbo.reference AS A,dbo.Category_Attribute AS B,dbo.Attribute_Type_Value AS C

純真的人 iT邦大師 1 級 ‧ 2018-10-08 14:46:36 檢舉

因為你只有用From
還要加上Where
否則一定會有null

例如以下篩選

FROM dbo.reference AS A
,dbo.Category_Attribute AS B
,dbo.Attribute_Type_Value AS C
WHERE .......

另外會有null
是因為那些欄位的值會變成null

WHERE B.ref_cat_att2 = C.ATV_Attribute_Type
AND A.Ref_att02 = C.ATV_Attribute_Value
AND A.Ref_Category = B.Ref_Category
AND A.Ref_Category = C.ATV_Category

--在某些交集的資料，會變成以下這樣~
WHERE B.ref_cat_att2 = null
AND A.Ref_att02 = null
AND A.Ref_Category = null
AND A.Ref_Category = null

連帶查詢的資料也會null