6

Some RDBMS's seem to allow a query without a FROM clause, eg:

postgres=# SELECT 'foo' bar;
┌─────┐
│ bar │
├─────┤
│ foo │
└─────┘

But for those that allow it, why would SELECT count(*) not return 0?

postgres=# SELECT count(*);
┌───────┐
│ count │
├───────┤
│     1 │
└───────┘
7

Speaking for PostgreSQL, I found this question to be somewhat interesting.

Omitted FROM Clauses

This isn't at all permitted in the SQL spec. PostgreSQL calls this feature an "Omitted FROM clause", and says this about it

PostgreSQL allows one to omit the FROM clause. [...] Some other SQL databases cannot do this except by introducing a dummy one-row table from which to do the SELECT. Note that if a FROM clause is not specified, the query cannot reference any database tables.

Here is what isn't explicit. PostgreSQL is simply introducing an implicit one-row dummy table for you. The example of the syntax is SELECT 2+2 so you can see it's only really made for trivial cases.

# SELECT count(*);
 count 
-------
     1

This makes sense so long as you remember that it's operating on an implicit dummy table.

CREATE TABLE dummy_table ();
INSERT INTO dummy_table DEFAULT VALUES;

SELECT count(*) FROM dummy_table;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.