9 ms·
Yeah, using `SELECT 1` language is a big nah from me. I want my queries to be readable by both new and experienced.
by chaps 3y ago
Yeah, using `SELECT 1` language is a big nah from me. I want my queries to be readable by both new and experienced.
- gigatexal 3y agoThis isn’t readable? It’s a common pattern.
- funnymony 3y agoIts a matter of your eyes being used to this. If for ten years you always indented the code this way Void F() { Foo(); Bar(); Baz();} Then following snippet will seem hard to parse mentally: Void F() { Foo(); Bar(); Baz(); } And vice versa
- wvenable 3y agoI assume you could just use `SELECT *` there and the result is the same.
- TurkTurkleton 3y agoIndeed, I would be surprised if there exists any RDBMS where the content of the select list in an EXISTS subquery matters. Postgres's SQL dialect even lets you use an empty select list (`... WHERE EXISTS (SELECT FROM related_table WHERE related_id = id_from_outer_query)`). In T-SQL, however, a non-empty select list is required, and in my experience, developers writing T-SQL tend to prefer a constant value like `1` or `NULL` over `*` -- I suspect there's some superstition there related to both the common wisdom that `SELECT *` queries are to be avoided (which is true -- you really should only ever select the columns of interest) and a lack of truly understanding that EXISTS only reads enough of the table to ascertain whether rows exist that satisfy the subquery's predicate, and returns a boolean value, not a result set.
- gpm 3y agoAs a non-sql-expert (I've used an exists query approximately once in my life) I prefer `select 1` over `select column_name` or `select *` because it doesn't look like it might be meaningful. Postgres's dialect seems like it made the right choice here.
- jrumbut 3y agoSelecting NULL to signify something exists breaks my brain a little bit. I really prefer 1 stylistically, even if there is no technical difference.
- chaps 3y agoYeah. 1 breaks my brain for the same reason probably.
- mjan22640 3y agoTurn the brain 90° from columns to rows and it will be fine.
- justsomehnguy 3y agoIt works fine for me.. when I'm thinking in sqlese. The bigger travesty here is using SELECT for the tasks which are not selecting (returning data) anything.
- marcosdumay 3y agoBoth Oracle and SQL Server have the advice to use a constant value on their documentation. Postgres used to advise the use of `*`, but looks like they improved their optimizer so it doesn't matter anymore. It's not superstition. It's people that know deeply how a complex system works picking the option with the best set of side-effects.
- nice-try-fletch 3y agoYou could. But `SELECT 1` is a very common pattern in SQL Server, intended exactly to declare intent to the reader that "this subquery doesn't return any real data" - in the event the `EXISTS` that surrounds it isn't enough of a clue. You could use `SELECT NULL` or `SELECT 0` or `SELECT *` or `SELECT 12/0` and the behavior would be identical, but most of those would likely raise even more questions to an inexperienced reader than `SELECT 1`.
- slotrans 3y agowhere exists (select 1...) is completely standard and normal. We should write our code to be readable, but it is reasonable to expect that the reader knows the language.
- chaps 3y agoHas nothing to do with the language. 1 (in its isolated presentation) exists outside of my set of data and that's not something that gives me comfortable feelings. It forces me to think (less think more good) why some random value needs to float around my data and maybe needs to be appended or summed or whatever.. because reasons. As far as I'm concerned you might as well make it 200 or 1648194. And why would those ever be needed in any context around my data?
- naikrovek 3y ago1 isn't data, here. it's just a flag to return that row. I am sure you know this, so I'm wondering why you consider it data which is outside of your data set. it isn't data when used like this.
- chaps 3y agoIn the sense that code is data: yes, it absolutely is data.
- naikrovek 3y agodon't be obtuse. it isn't in the dataset and it isn't returned in the results. > code is data, so it's data dude, don't. this is the weakest and most grasping argument I think I've ever heard.
- chaps 3y agoNo need to be a dismissive dickhole about it, friend. Maybe I'm not wording it right then? Like I said in other posts, I copy paste SQL statements all the time. If I were to copy/paste that statement then all I'd get is a bunch of ones and that's useless to me. The SQL itself is data to me in the same way that when I view a lazy list comprehension, I view it (and SQL) as something that's just waiting to be run. Maybe not now, and maybe not in the current SQL, but there's a non-zero chance that I'll copy/paste it. So in that context, a bunch of ones is useless to me and IS data because the ones are literally the output of the SQL statement. Better to generalize my code writing process in a way so that I can copy/paste a "select *" or "select rownum" because those are more useful down the line. Really think we all just code differently.
- mvdtnz 3y agoStrange hill to die on - the SELECT 1 query is not particularly hard to understand, and it's demonstrably better.
- chaps 3y agoEh, 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 agoHelp a fellow out here. What’s the purpose?
- noisem4ker 3y agoWith 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 agoI'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 agoSELECT 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.
- nsenifty 3y agoYeah just change it to `SELECT OrderID`.
- chaps 3y agoYep, agreed.
- thaumasiotes 3y agoI don't get it. "SELECT 1" is significantly more informative than "SELECT red_herring". It's a self-documenting and automatically enforced comment telling you that the information requested by the SELECT statement is not used by the query.
- chaps 3y agoI copy paste SQL all the time, especially with CTE after CTE all the time. The row number is a simple thing to copy paste that fills multiple functions with zero modification.
- thaumasiotes 3y agoAnd that's different from 1 in what way other than being more confusing? Note that in the case you're advocating for, the row number is called "OrderId", which you might have trouble with if you insist on copying a query from somewhere else and using it without modification. Wouldn't you prefer "1"?
- chaps 3y agoIt's helpful within CTEs as a unique identifier. This whole conversation makes me wonder if not many people minmax this sort of meta programming thing.
- paulddraper 3y agoYou can write `SELECT NULL` or `SELECT *` if you prefer. PostgreSQL allows a zero-column `SELECT`.
- croes 3y agoThen write Select *
- inopinatus 3y agoIt’s literally the documented idiom for producing singular output from amplified rows at https://www.postgresql.org/docs/current/functions-subquery.html#FUNCTIONS-SUBQUERY-EXISTS https://www.postgresql.org/docs/current/functions-subquery.h....
- Sesse__ 3y agoIf you don't like EXISTS, the other standard way to get a semijoin is using IN with a subquery. SELECT * FROM a WHERE x IN (SELECT y FROM b);
- meinaccount00 3y agoI've been working with Spark lately and just beeing able to write "semi join" (or "anti join") is quite nice and readable
- Sesse__ 3y agoExplicit semi- and antijoin operators would be a nice addition to the language, but do note that they present some additional challenges for the optimizer. In particular, when the join condition becomes explicit, it can take on more forms than before, which can limit the reorderings allowed or allow new ones. E.g. you now need to deal with this structure: (a SEMIJOIN b ON a.x=b.y) JOIN c ON b.z=c.z This was an impossible structure before, since IN and EXISTS both hide b's columns (all semi- and antijoins effectively come last), and your optimizer will now need to know whether e.g. this associative rewrite is allowed or not: a SEMIJOIN (b JOIN c ON b.z=c.z) ON a.x=b.y Also, you'll need to deal with LATERAL semijoins, which you didn't before… None of this is impossible, but there's more to it than just a small syntactic change.
- magicalhippo 3y agoJust to be sure to check the resulting plans. The DB we use usually has sub-optimal plans when using IN compared to EXISTS, often quite drastically so.
- Sesse__ 3y agoYou always need to check the resulting plans if you care about execution performance, that's just how life is. I'm offhand a bit surprised IN does worse than EXISTS; I can understand NOT IN being slow, because it has very surprising NULL handling that is hard to optimize for.