A modern programming language that makes developers happier.

kotlinlang

Anyone have thoughts on writing a query in SQLDelight where my goal is to see if the table is empty?

Currently I have this
```sizeOfTable:
SELECT COUNT(*);```
but open to other ideas if anyone knows better. :sweat_smile:

you could also do `== 0` to get a boolean

Cool.

tableIsEmpty:
SELECT _COUNT_(*) == 0;

it is. thanks

ooh. wonder if

```tableIsEmpty:
SELECT COUNT(*) FROM myDbTable LIMIT 1 == 0;```
would work...

don't think that's valid SQL, but even if it is that is effectively `LIMIT false`

`COUNT(*)` is _always_ optimized to O(1) so there's nothing to improve upon here except possibly returning the boolean directly rather than doing it in code.

Maybe 20 years ago you needed to do `COUNT(column_in_index)`, but not anymore.

Well apparently my table is empty (according to android studio db inspector), but

SELECT _COUNT_(*) == 0

returns false. I wonder if I need to add the table name to the select statement.

yeah. just updated

tableCount:
SELECT _COUNT_(*) FROM myDbTable

and then just checking if it == 0 later. and that works as expected :shrug:

Yeah sorry I kind of assumed you had a `FROM` in there

I'm surprised `SELECT COUNT(*) == 0` even works

```sqlite&gt; SELECT COUNT(*);
1

sqlite&gt; EXPLAIN QUERY PLAN SELECT COUNT(*);
QUERY PLAN
`--SCAN CONSTANT ROW

sqlite&gt; EXPLAIN SELECT COUNT(*);
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     1     0                    0   Start at 1
1     Null           0     1     1                    0   r[1..1]=NULL
2     AggStep        0     0     1     count(0)       0   accum=r[1] step(r[0])
3     AggFinal       1     0     0     count(0)       0   accum=r[1] N=0
4     Copy           1     2     0                    0   r[2]=r[1]
5     ResultRow      2     1     0                    0   output=r[2]
6     Halt           0     0     0                    0```
weird

If you do `SELECT *;` it yells. `SELECT COUNT(0);` is 1, but not sure why `SELECT COUNT(*);` works...

`SELECT COUNT(*)` of any table (regardless of number of columns) is fine but `SELECT *` of a zero-column table isn't, I guess?

not that sqlite supports zero-column tables though…

I assume you’re in SQLite. MySQL Innodb is surprisingly slow at count queries and must do a full table scan!
<https://www.percona.com/blog/count-vs-countcol-in-mysql/|https://www.percona.com/blog/count-vs-countcol-in-mysql/>