CODEWITHSUNDEEP
sqlsql-server
user4764254
user4764254

Reputation:

How to select data from 2 tables and convert NULL to 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.

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

Upvotes: 2

Views: 59

Answers (3)

Brave Soul
Brave Soul

Reputation: 3620

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

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Use the ANSI standard function 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.

Upvotes: 3

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9063

You can use ISNULL in following:

ISNULL replaces NULL with the specified replacement value.

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

Upvotes: 0

Related Questions