當連接列中存在空單元格時，如何使用撰寫SQL連接？

架構：

CREATE TABLE PRODUCT
(
    PRODUCTID INT, 
    PRODUCTNAME VARCHAR(100), 
    PRODUCTUSER VARCHAR(100)
);

CREATE TABLE USER
(
    USERID INT, 
    USERNAME VARCHAR(100),
    USEREMAIL VARCHAR(100)
);

INSERT INTO PRODUCT(PRODUCTID,PRODUCTNAME,PRODUCTUSER) 
VALUES (1, 'Product1', 'Chen'), (2, 'Product2', 'Bob'), 
       (3, 'Product3', ''), (4, 'Product4', '');

INSERT INTO USER (USERID, USERNAME, USEREMAIL) 
VALUES (1, 'Chen', '[email protected]'),
       (2, 'Bob', '[email protected]'),
       (3, 'Paul', '[email protected]'),
       (4, '', ''), (5, '', '');

產品表：

ProductID  ProductName  ProductUser
------------------------------------
1          Product1     Chen
2          Product2     Bob
3          Product3      
4          Product4 

用戶表：

UserID  UserName  UserEmail
--------------------------------
1       Chen      chen@email.com
2       Bob       bob@email.com
3       Paul      paul@email.com
4    
5

我想加入 Product 和 User 表以獲取所有產品名稱和用戶名（如果可用）作為輸出。挑戰在于公共欄位 Product.ProductUser 和 User.UserName 都包含空值，如上例所示。我知道這不是一個好的架構設計，但我無法更改架構，因為它超出了我的控制。

預期輸出：

PROCUTNAME   USERNAME
----------------------
Product1     Chen
Product2     Bob
Product3
Product4

示例查詢 1：

SELECT PRODUCTNAME, USERNAME
FROM PRODUCT P
JOIN USER U
ON P.PRODUCTUSER=U.USERNAME

上面的查詢是一個內部連接，因此由于連接為空而回傳 Product3 和 Product4 的重復項。添加 WHERE 子句 ProductUser<>'' 會導致排除 Product3 和 Product4。

通常，如果 ProductUser 是 NULL 而不是空，那么我知道 LEFT JOIN 將是解決方案。我認為我理解在 LEFT JOIN 的 ON 子句和 WHERE 子句中使用標準之間的區別。

So trying a left join with criteria in the ON clause:

SELECT PRODUCTNAME, USERNAME
FROM PRODUCT P
LEFT JOIN USER U
ON P.PRODUCTUSER=U.USERNAME AND P.PRODUCTUSER<>''

So the above query works by 1st doing an inner join based on the given criteria in ON clause, and then pulls in all other rows from the product table and puts a null for username. (or in other words it first lists all products, and then joins on those records that satisfy the ON criteria.)

This gives me output as expected. But I am not sure whether my approach is correct so trying another approach:

SELECT PRODUCTNAME, USERNAME
FROM PRODUCT P
LEFT JOIN (SELECT * FROM USER WHERE USERNAME<>'') U
ON P.PRODUCTUSER=U.USERNAME

This also works.

Is the left join with criteria in the ON clause a correct way to approach the problem?

uj5u.com熱心網友回復：

您可以通過分組洗掉重復項嗎？

declare @prod TABLE (PRODUCTID INT, PRODUCTNAME VARCHAR(100), PRODUCTUSER VARCHAR(100));

declare @user table (USERID INT, USERNAME VARCHAR(100),USEREMAIL VARCHAR(100));

INSERT INTO @prod(PRODUCTID,PRODUCTNAME,PRODUCTUSER) values (1,'Product1','Chen'),
(2,'Product2','Bob'),(3,'Product3',''),(4,'Product4','');

INSERT INTO @user(USERID,USERNAME,USEREMAIL) VALUES (1,'Chen','[email protected]'),
(2,'Bob','[email protected]'),(3,'Paul','[email protected]'),
(4,'',''),(5,'','');


select * from @prod
select * from @user

select 
    p.ProductId,
    p.ProductName,
    u.USERNAME
FROM @prod p
    left join @user u on p.PRODUCTUSER = u.USERNAME
group by
    p.PRODUCTID,
    p.PRODUCTNAME,
    u.USERNAME

uj5u.com熱心網友回復：

select
    main.PRODUCTNAME
    , case
        when sub.USERNAME is null
        then ''
        else sub.USERNAME
    end USERNAME

from #PRODUCT main
    left join
    #USER sub
    on main.PRODUCTUSER = sub.USERNAME
        and sub.USERNAME like '%[A-Za-z]%'

標籤：sql sql-server tsql

上一篇：狀態更改的兩個日期之間的間隔

下一篇：SQL-GROUPBY同一列的3個值