161–168 of 168 posts
Asking as someone who only occasionally contributed (or tried to) with a repository.
Examples: https://github.com/ben-n93/SQL-tips-and-tricks/pulls?q=is%3A...
Earlier quoted context omitted.
Duration of working with SQL doesn't matter. The better SQL programmers don't do it specifically, and have experience in real languages that they bring over to database queries.
Not sure I get this. But I think it does matter since you understand why people do it to begin with. I worked on two enterprise solutions over the last couple of years that have this exact problem. That people are using WHERE 1=1 and then add random "AND something=something" that completely trashes the performance of the db. Also, it does not matter as much on-prem. But in cloud envs it does. Since you can't really s…
Earlier quoted context omitted.
I think that it means the reason for doing where 1 = 1 is sometimes to allow for easy insertion of dynamic queries which can be a security and performance issue. The actual usage of where 1 = 1 doesn't cause the security or performance issue.
Which is exactly what the site says. To insert dynamic conditions. I know that you can use 1=1 for the same reasons as trailing commas. But kinda obvious that this is not the case here.
If I'm inspecting a dataset I use WHERE 1=1 so I can add and remove conditions more easily.
I realise the confusion is in my wording of dynamic - I might amend the README.md to clarify. Thanks for the feedback!
Earlier quoted context omitted.
Presumably you are thinking about queries in code that add WHERE clauses dynamically that aren't escaped correctly- which doesn't have to be the case. 1 = 1 is at least handy for simply joining a variadic amount of other clauses with ' AND ' rather than counting if there's any to add at all.
Yes. "Use a dummy value in the WHERE clause so you can dynamically add and remove conditions with ease:" I don't know how to read this in another way.
Earlier quoted context omitted.
Yeah, unfortunately you're right that they are real conventions. Quite common too. I also _understand_ why they exist. It's simple: It makes code marginally easier to write. But writing confusing, unintuitive and honestly plain ugly code. Just so you can save a second after clicking run and the compiler tells you the mistake is a bad reason.
A lot of "readability" depends on what you're used to and what you expect. I don't think these conventions are inherently "ugly" or "confusing", but they are different to what I've been doing for a long time, and thus unexpected, and thus "ugly". But that's extremely subjective. I've done plenty of SQL, and I've regularly run in to the "fuck about with fucking trailing commas until it's valid syntax"-problem. It's a…
Earlier quoted context omitted.
Analysts usually query data warehouses, which are columnar, so * is a query/warehouse killer. Everybody should just select the columns they need.
This is another area where I wish SQL was more composable. I’d love to be able to specify a bunch of columns using a single reference or a function, without having to resort to dynamic sql. Exclude and rename are a start, but not enough.
Earlier quoted context omitted.
Not sure I get this. But I think it does matter since you understand why people do it to begin with. I worked on two enterprise solutions over the last couple of years that have this exact problem. That people are using WHERE 1=1 and then add random "AND something=something" that completely trashes the performance of the db. Also, it does not matter as much on-prem. But in cloud envs it does. Since you can't really s…
If the query planner can't optimize out "IF TRUE" I don't know what to say. Is there something deeper happening or is this just gross incompetence?
It is kinda funny that op backpedal on this cause to me the whole site is amateurish. I just pointed out what I thought was the worst part. It is likely that it is generated by AI. Either way the post is terrible.
Earlier quoted context omitted.
If the query planner can't optimize out "IF TRUE" I don't know what to say. Is there something deeper happening or is this just gross incompetence?
Jesus. The problem is not WHERE 1=1. It is WHY people do it. People do it because they then in Python, JS or whatever can easily add conditions. Like QUERY + "AND x='blabla'". There is the problem. Every time you create a unique SQL statement the query will need a new query plan in most SQL engines. Some will cache parts of the query. And you could use parameters along with this paradigm but if you are this bad at SQ…
I certainly didn't use AI, all these tips/tricks are from work experience and reading Snowflake documentation and the like, but I guess I can't convince you either way. Regardless I appreciate the feedback!