Hacker News Reader

Comment by chaps | Hacker News Reader

Has 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?

View on HN

1 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.

In the sense that code is data: yes, it absolutely is data.

don'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.

No 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.

> No need to be a dismissive dickhole about it, friend.

name-calling. it's a weak, last resort move.

you don't get a bunch of 1s from that query. And, I think I know why you're copying and pasting queries all the time instead of writing them.

"you don't get a bunch of 1s from that query."

You've seriously missed the point if this is what you're still saying. Take a step back, breathe, and try to consider that you've missed something. Whether that's a point I've made, or a lack of perspective, I don't know. For example, we almost definitely work in different fields with different practices and reasons for doing things differently. And that's fine. But your continued dismissiveness isn't helpful. Like another post said, "select *" is helpful in data analytics work. If it's not helpful in your field, that's also fine. But for me and my colleagues, it is. And I promise you're wrong in your thinking of why I copy paste SQL. What a weird fucking conversation.

Also, calling someone obtuse is name calling, so pot kettle black and all that, ya obtuse weirdo.

Good luck, friend.

Yes, and all those constants would be treated the same, just like in many languages things are conventions - you don't need to use _ to name a variable that you throw away, but you do, because conventions.

There's so many people that want to complain about the conventions of SQL but praise the conventions of other languages and I think its often just "which did you see first?"

You're missing the point that I don't really care about how they're treated. I want to glance at it and know what it does. For me, the 1 gets in the way. Maybe it doesn't get in the way for you because you don't use it the same way as I do, as a practice.

> Has nothing to do with the language.

...but... this is literally how you write an EXISTS clause in SQL.

> you might as well make it 200 or 1648194

Yes, you can put anything there.

There's lots of funky inconsistent syntax in SQL because it evolved organically over literally the past half century. It has everything to do with the language.

  Yes, you can put anything there.

And yes, you missed the point.

Would it make you feel better about it to think of it as (or write it as) "select true" ? Because that's essentially what it's communicating, that there is something to return and that's what we're checking for.

Nope, for the same exact reason. Select * makes the most sense because * is in the context of my data, not something I'm working into it. Pretty sure it's the same speed.

Also mind you, I use a lot of CTEs, so this would look weird in that context -- hence why using row number sometimes makes more sense and achieves the same thing.

Filter out everything, Project only what you need, Transform it as lightly as possible.

In any context I understand a row number would never "make sense" if a constant of 1 would be the same output, it would be a lot more code that does... nothing?

Any code using select * just breaks in the future with any new columns being added, no thanks.

  Any code using select * just breaks in the future with any new columns being added, no thanks.

For you, maybe. In my workflows this is really a non-issue for me.

Maybe consider that we use SQL differently and your goals and challenges are different from mine.

(Edit: What's with the downvotes from people just disagreeing about preferences? So weird.)

I didn't downvote you, but consider this - I work with SQL a lot, like a lot a lot. Something that's your code today is probably my code tomorrow.

So when you say "my flow is X" and your flow is inimical to maintaining it and extending it, people might get a bit irritated at the last dev that did the exact same thing.

I've spent most of my career working with teams that mostly write SQL code, at multiple companies.

Except for very rare fringe cases, using "SELECT *" in production code is universally considered bad practice.

Select * is pretty standard for wide table queries like they are used in Data Analytics teams regularly. Because you really want ALL columns, even new ones without going back to fix all 300+ dashboards.

Yep. I think the others commenting here aren't using SQL for analytics and don't recognize the importance of select * in that context.

I haven't downvoted anyone, but have followed this argument with interest as an intermediate SQL user.

If I was to guess why someone would downvote you, it wouldn't be for disagreeing with you, but more because you've subtly shifted from quite a strong objective stance ("this is not readable") to a subjective one ("this is not how I prefer to write it"), without really conceding anyone else's points.

1 think my point makes more sense when you consider that I 1. Don't work with production code (more analysis, ad hoc code in an investigatory capacity) and 2. that when 1 mention someone is "new" what 1 mean is someone actively learning and not from a technical background. 1ME, folk like that have a difficult time with that floating 1. So while yes it's a standard that programmers are familiar with, it's not something that someone new will be very comfortable with. Lots of people 1 work with come from a pandas-only background.

Not really conceding because as far as 1 can see, everybody is coming from a position of familiarity.

That's totally fair. Perhaps the confusion could have been avoided by qualifying in your initial comment that you're referring to a specific situation i.e. not-too-technical analysts writing ad hoc code.

you're getting downvoted because it's not really a preference, it's pretty widely known to be bad practice and unhygienic in production queries. select * would get your pr rejected and chewed out by dba at every place i've ever worked at. so you kinda just look like you don't know what you're talking about.

In an exists query select * is harmless, select 1 and select * result in the same execution plan at least in MS SQL.

In a query than returns result rows it could break the query as soon as you add columns with names that already exist in other tables you joined in the query.

yes, it's harmless in this position but it provides no additional benefits to the select 1 idiom and is suggestive of poor query discipline. it's far easier to say just don't ever use select * in queries.

I would add to this a bit in that

1. Given that "select " is considered something to avoid except when necessary in edge cases

2. And "select 1" will accomplish the same goal

Anyone reading the "select " version of the code will have to stop and consider whether it is using "select " for a reason, because "select 1" would be the normal choice. Using "select " is assumed to be conveying some intent (that isn't there) _because_ it's not the expected way to do it.

I kind of see it like

    if (thisField == thatField) ...

    if ( (( true || false )) && ( 11 == 11 ) && thisField == thatField ) ...

Sure, they do the same thing... but you have to stop and look at the second one to make sure you're understanding what it does and if there's some reason its weird.

How would Select * break with a new column?

If you have a table with two columns and you do a 'select *', adding a column to the table can break code that is only expecting 2 columns.

Usually code refers to columns by name and additional columns are just ignored, the only case I know is when you add a column to a joined table and the column name already exists in the other table resulting in ambiguous column names.

In an exists clause the * is harmless

I am talking about 'select ' at the top level, they can be harmless in exists, sub-selects and CTE's. The number of columns sent to the client (the program) will change when you add a column to the database. If you don't remember to change all of the places in your code where you used a 'select ', you program is likely going to fail or have unexpected results.

Any situation where a new or elsewise unknown attribute breaks your code, binding is a big one.

* Say you are joining two tables and one now has a conflicting/duplicate name, surprise, you now have broken code, it literally will not execute with ambiguous duplicate references.

* By the same token, downstream views can break for the same/similar reason.

* In some engines views are not going to actually include your columns until they are "refreshed"(SQL Server) so that one day that's out of band of your deployment your views will suddenly change.

* Say you have a report with specific headers - tada, it's now got whatever people add to the table - and sourcing it can be a pain because its unclear where the source is in the query, requiring schema.

* Performance expectations can change if the data type is much larger, up to the point of actually breaking the client or consuming tens of billions of times more resources.

The relevant thing for exists is the set of the rows returned by the select being non/empty. The value in the columns is irrelevant. Using * creates an incorrect impression that the value is relevant and the relevance tied to the data.

Just wanted to write, why not select count(*) ? But I guess that's what you meant by row numbers.

Select 1 communicates that no columns need to be selected, so it forces inexperienced readers such as myself to understand why that is the case.

So imho, it carries more information than selecting some arbitrary columns, or counting the number of rows (for which I only care it's > 0)

Reply on news.ycombinator.com