sqlite3 select from empty table = no select?

I usq sqlite3 (on Android) and try to return a default value, if a select has to handle NU开发者_如何学PythonLL values.

Normally this works:

SELECT ifnull(NULL, 12);
12

With a variable: SELECT ifnull(var, 21) from (select NULL as var); 21

But the moment I use a table and this table is empty, then I don't get anything:

SELECT ifnull(_id, 1) FROM empty_table;
<nothing here>

Even returning a simple number is not possible:

SELECT 123;
123
SELECT 234 FROM empty_table;
<nothing here>

Why does that happen? How can I still get a value out of it?

---

It's a bit different.

When using SELECT with rows, ifnull() is evaluated once per row, so you need at least one row.

You could probably try something:

SELECT IFNULL((SELECT _id FROM empty_table LIMIT 1), 1)

---

select 1 from the_table

SQL returns always one row (at most) for each input row. If the table is empty, you will not get a single row. If the table has ten thousand rows, you will get ten thousand time the number 1 (even though your program probably only select the first one, it is somewhat inefficient).

SELECT ifnull(_id, 1) from the_table

That is also rather unspecified behaviour: Without any ORDER BY, you will just get a random _id.

select 1

In the case when there is no table (not all databases support this), it is like selecting from a table with just a single row (and no columns).

How can I still get a value out of it?

How about

select ifnull(max(_id), 1) from the_table

That will always return exactly one row, even when the table is empty, and you know which _id you are getting.

---

You can't. A column from a table containing no rows is not equivalent to a null value

---

ifnull(_id, 1) checks whether the value of _id is null or not in each row of the query results.

If the table is empty, there are no rows to check, so the ifnull never gets tested.