如何从两个表中选择数据并将NULL转换为0?

I have 2 tables, Table1 (Id, Name, Email) and Table2 (Id, ItemName, Price, IsShipping). I need to join them on Id columns, but IsShipping column has NULL values, I need to change NULL to 0.

我有两个表，表1 (Id, Name, Email)和表2 (Id, ItemName, Price, IsShipping)。我需要在Id列上连接它们，但是IsShipping列有NULL值，我需要将NULL更改为0。

For now I have:

现在我有:

SELECT Table1.Id, Table1.Name, Table1.Email, Table2.ItemName, Table2.Price, Table2.IsShipping 
FROM Table1
JOIN Table2
ON Table1.Id = Table2.Id
--here I need to make something like:
WHERE IsShipping IS NULL = 0 -- or etc

3 个解决方案

#1

You can use ISNULL in following:

您可以使用ISNULL如下:

ISNULL replaces NULL with the specified replacement value.

ISNULL用指定的替换值替换NULL。

SELECT t1.Id, t1.Name, t1.Email, 
       t2.ItemName, t2.Price, 
       ISNULL(t2.IsShipping, 0)  -- Here you pass ISNULL
FROM Table1 t1
JOIN Table2 t2
ON t1.Id = t2.Id

#2

Use the ANSI standard function coalesce():

使用ANSI标准函数coalesce():

SELECT t1.Id, t1.Name, t1.Email, t2.ItemName, t2.Price,
       coalesce(t2.IsShipping , 0) as IsShipping
FROM Table1 t1 JOIN
     Table2 t2
     ON t1.Id = t2.Id;

I also added table aliases. They make the query easier to write and to read.

我还添加了表别名。它们使查询更易于编写和读取。

#3

you can use coalesce() and Both ISNULL and COALESCE are ANSI standard

您可以使用coalesce()， ISNULL和coalesce都是ANSI标准

SELECT Table1.Id, Table1.Name, Table1.Email, Table2.ItemName, Table2.Price, 
coalesce(Table2.IsShipping,0) as IsShipping
FROM Table1
JOIN Table2
ON Table1.Id = Table2.Id

#1