Display and join table using column with null values

2
votes

I have into col1 value as below:

enter image description here

If I'm doing select:

select col1 from table1

How to instead of NULL display X?

It's possible then to join other table using values from col1? (A, B and X)

like: table1.col1 = table2.col1 ??

3
sqlsql-serversql-server-2008joinnull
select COALESCE(col1,'X') AS col1 from table1 - Lukasz Szozda
Also IsNull(Col1, 'RepalceValue') dang enter key COALESCE allows for multiple replace values such as COALESCE(Col1, Col2, 'ANother') checks if each is null down the chain - SCFi
This kinda feels a lot like a homework question... - Zane

3 Answers

1
votes

Try,

Select x.Col1 
FROM Table1 x INNER JOIN Table2 y
ON (x.Col1 = y.Col1 or x.Col1 is NULL and y.Col1 is NULL)
0
votes

Use ISNULL(col1,'X'), this will return the value of col1 if it is not null , else it will return the default value 'X'

select ISNULL(col1,'X') from table1
0
votes
SELECT x.Col1 
FROM table1 x 
INNER JOIN table2 y on ISNULL(x.Col1, 'X') = ISNULL(y.Col1, 'X')