Change NULL value to another value in SQL server

Question

I have 3 tables. ID and Name is the primary key for the first table.

First Table

ID | Name | Date
----------------
1  | AA   | 11/02
2  | BB   | 04/10

ID, Name and Option are the primary key for second table:

Second Table

ID | Name | Option  | SeqNo
---------------------------
3  |  DD  |   LOVE  | 1
4  |  EE  |  SINGLE | 1

Option is the primary key for the third table:

Third Table

Option | Status
---------------
LOVE   |  Y
MARRIED|  Y
SINGLE |  N

After I join these tables, I will get like this.

ID | Name | Option | SeqNo | Status
------------------------------------
1  | AA   | NULL   | NULL  | NULL
2  | BB   | NULL   | NULL  | NULL
3  | CC   | LOVE   |   1   |  Y
4  | DD   | SINGLE |   1   |  N

My question is, how to change the NULL value to a value contain in another table?

As an example, The Option column must be filled in with the value inside the third table. I'm using SQL Server 2005

Please show the syntax you are using to perform the JOIN that generates the results — connectedsoftware
– connectedsoftware, Commented Oct 19, 2016 at 7:46
SELECT t1.*, t2.Option, t2.SeqNo, t3.Status FROM Table1 AS t1 LEFT JOIN Table2 AS t2 ON t2.ID = t1.ID AND t2.Name = t1.Name LEFT JOIN Table3 AS t3 ON t3.Option = t2.Option </pre> — Syafah
– Syafah, Commented Oct 19, 2016 at 8:50

Yaron Idan · Accepted Answer · 2016-10-19 07:46:50Z

2

This link describes how to replace null values in different sql engines, including sql server - http://www.sqlines.com/oracle/functions/nvl Basically, the syntax you are looking for is -

ISNULL(SeqNo, 'N/A')

answered Oct 19, 2016 at 7:46

Yaron Idan

6,7857 gold badges47 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Syafah Over a year ago

If I want to replace NULL value with multiple value, do I need to duplicate the syntax? @Yaron

Yaron Idan Over a year ago

This is not your original question, and also not a very clear requirement. First - if this answer your question, please accept the answer.

Collectives™ on Stack Overflow

Change NULL value to another value in SQL server

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related