How to replace NULL with a value in SELECT statement?

We have this table orders

id	order_id
1	TK12
2	TK19
3	TP99
4	AD100
5	TU65
6	MA33
7	PP98
8	DE28
9	TH54
10	VD66
11	GT74
12	PO1901
14
15
16
17

Now select all order_id from the table and if the value is NULL, return the ‘N/A’ value instead. There are several ways to do that:

1. Use CASE:

SELECT
	CASE WHEN order_id IS NULL THEN
		'N/A'
	ELSE
		order_id
	END AS id
FROM
	orders;

2. Use COALESCE

SELECT
	COALESCE(order_id, 'N/A') AS id
FROM
	orders;

The results will be:

id
MA33
PP98
DE28
TH54
VD66
GT74
PO1901
N/A
N/A
N/A
N/A

3. Bonus

In MySQL, you can also use IFNULL:

SELECT
	IFNULL(order_id, 'N/A') AS id
FROM
	orders;

In Oracle, you can use NVL:

SELECT
	NVL(order_id, 'N/A')
FROM
	orders;

For SQL Server, ISNULL is the equivalent:

SELECT
	ISNULL(order_id, 'N/A') AS id
FROM
	orders;

---

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS