我有两个表
Tbl_1
Mat_Id Mat_desc
1001 ABC
1002 CDS
MNK
ELP
2003 STX
和
Tbl_2
Mat_Id Mat_desc
1004 TSS
1002 CDS
MNK
2045 EXP
2003 STX
我想在两个条件下将Tbl_1和Tbl_2连接为左连接。如果Tbl_1中的Mat_Id
存在,则ONMat_Id
Else ONMat_desc
。
所以最终的Table应该看起来像
Mat_Id Mat_desc
1001 NULL <---Being joined on Mat_Id
1002 CDS <---Being joined on Mat_Id
MNK <---Being joined on Mat_desc
NULL <---Being joined on Mat_desc
2003 STX <---Being joined on Mat_Id
如果我尝试
SELECT a."Mat_Id",b."Mat_desc FROM Tbl_1 a
LEFT OUTER JOIN Tbl_2 b
ON a."Mat_Id" = b."Mat_Id"
AND a."Mat_desc" = b."Mat_desc"
那么它可能不工作。
有什么提示如何使这成为可能吗?
如果mat_id
是null
,任何=
条件都将返回false。在这两个条件之间需要使用or
逻辑运算符,而不是and
。为了更加安全,并确保您不会在具有mat_id
s的行上匹配mat_desc
s,您还应该显式地检查它:
SELECT a."Mat_Id",b."Mat_desc"
FROM Tbl_1 a
LEFT OUTER JOIN Tbl_2 b
ON a."Mat_Id" = b."Mat_Id" OR (a."Mat_Id" IS NULL AND a."Mat_desc" = b."Mat_desc")
-- Here -------------------^