3 ms·

Strange hill to die on - the SELECT 1 query is not particularly hard to understand, and it's demonstrably better.

• chaps 3y ago
  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.
• wodenokoto 3y ago
  Help a fellow out here. What’s the purpose?
  • noisem4ker 3y ago
    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.
    • pcthrowaway 3y ago
      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
    • wruza 3y ago
      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.
      • magicalhippo 3y ago
        > 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.
    • nice-try-fletch 3y ago
      _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.
  • Quekid5 3y ago
    It's kind of a workaround for the fact that SQL doesn't do 0-tuples, aka. "unit".