If you use it in the same way you use trailing commas. Fair. But the site says to make it easier to add dynamic conditions. Which is a terrible idea in maybe not all but many SQL engines.
Lets say its 2001 and you are writing some hot e-commerce stuff in plain php. You want to filter data depending on multiple fields in the submitted form. If some field is there, you add one more "AND" clause to the "WHERE", like this: if (isset($_POST['product'])) { $query .= "AND product = " . $_POST['product']; }. So in order not to check every time if the added clause is the first one you start with "WHERE 1=1 ", as "WHERE AND ..." would not work.
I get how this isn't good. But how else would you handle multi-field filtering, keep all the ANDs and use (product_id = $1 OR $1 IS NULL) so the unset filters are no-op? That's ok as long as the query planner is smart enough.
In this case. A query that you build by adding different strings. 1=1 is for adding AND statements to the WHERE clause dynamically. In your code. I never seen it used for anything else. Adhoc is just the practice of running raw SQL queries.
So you end up with things like this.
"SELECT * FROM Music WHERE 1=1" +
"AND category='rock'"
The risk is now that you by mistake allow for SQL-injections but also every genre will generate a different query plan. Depending on what SQL engine you use this may hurt performance.
And one would think that this is a thing of the past. But it is not.
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.
I'll add this as a caveat. I'm an analyst so my SQL isn't really exposed to anyone other than myself and so I wasn't aware of this, thanks for flagging.
A random person claims adding 1=1 is a security risk and you are going to add it as caveat without verifying if the claim is true nor knowing why? That's how misinformation spreads around.
OP doesn't know what they are talking about because adding 1=1 is not a security risk. 1=1 is related to sql injections where a malicious attacker injects 'OR 1=1' into the end of the where clause to disable the where clause completely. OP probably saw '1=1' and threw that into the comment.
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 spike CPU and mem the same way as on-prem.
The reason I pointed out this specific issue is just that I thought it was the worsed of many poor tips. ChatGPT can give better tips.