What do SELECT 1 and SELECT COUNT(1) do in Oracle SQL?

Question

So I just had an interview today for a java developer position, and they asked me this question

Can you tell the difference for

SELECT 1 FROM table
SELECT count(1) FROM table
SELECT count(*) FROM table

I looked up on stack overflow but couldn't find a satisfactory answer. I have been a Java developer with knowledge on SQL for a couple years, but have never had to use unfamiliar statements like Select 1 or select count(1)

Also this is the entire question. i.e, these queries are not part of any bigger query.

Could you please help me get an answer for this?

Answer 1

SELECT 1 FROM table

Will return 1 for as many times as there are records in table.

SELECT count(1) FROM table

Will return the count of all records in table.

SELECT count(*) FROM table

Same as above.

* is used by Oracle as a hint (not technically a hint, but something similar) so it allows it to pick the best index to compute the count. That's the main difference with select count(1)... Analogically, this is the same idea behind WHERE EXISTS (SELECT 1 FROM ...) versus WHERE EXISTS(SELECT * FROM ...) This statement is no longer relevant to modern Oracle RDBMS engines.