Eh, anytime I need to read SQL that includes that I have to sit down and think about it. It's not glance-and-understand and you have to maintain that state in your head. Not the sort of SQL I want to rely on as a practice.

Mind you, most of what I write are ad hoc SQL queries where I might write 100-200 queries in a single day. So that sort of thing really adds up. And it's in a strange place where performance isn't dire, since I can work on other stuff while queries chug.

Help a fellow out here. What’s the purpose?

With most databases (PostgreSQL is an exception), SELECT must select something, even if you only care about the record existing in general, and you're not interested in reading anything from it. SELECT 1 means "select the first column", whatever it is. Specifying a column number also works with ORDER BY and other clauses. Of course any natural number works, as long as the record has at least that many fields.

SELECT 1 means "select the first column", whatever it is

No, it selects a literal integer 1 in all SQL engines.

You can, and do, select any expressions, not only column names.

  CategoryId,
  CategoryId + 1,
  1,
  sqrt(CategoryId * ProductId),

Column names alone are expressions too.

SQL queries return not columns from table, but a result of relational operations with new columns specified by expressions. What a downvoted user itt cannot accept is that 1 is as meaningful as * or <colname> in general and is an idiom for “columns don’t matter here, only [the number of] rows”. It’s like [[1]].length vs [[id, …]].length. Saying “it’s not my data” is misunderstanding what that data is.

> You can, and do, select any expressions, not only column name

I find this very useful when I need to insert some fixed data alongside some data from the database. I'll then use

    insert into TableX(Key, TotalWeight, Name, Category) 
    select Key, sum(Weight), 'foo', 42 from TableY where Key = 1234 group by Key

or something like that. Usually the source of the fixed data is in a spreadsheet, so I just use Excel to generate the SQL statements.

_SELECT 1 means "select the first column"_

In SQL Server at least, no, it literally means select the integer 1. In the ORDER BY clause, it does mean to order by ordinal position, but that's not a great thing to glorify, since ordinal position is not necessarily stable. I think other dialects like MySQL might allow GROUP BY 1, but that's not a great thing to glorify either.

I'm pretty sure you still have to select something in postgres... or are you saying there's a different operator to do the same thing?

> SELECT 1 means "select the first column"

interesting.. in postgres this actually gives you a single-column value of 1, in an unnamed column... I haven't used other DBMSes much but expected similar behaviour

It's kind of a workaround for the fact that SQL doesn't do 0-tuples, aka. "unit".