I remember doing the "WHERE 1=1" trick in my last job and it causing a... let's say "unproductive", discussion in the pull-request.
[deleted]
SQL Tips and Tricks
31–40 of 168 posts
Re: SQL Tips and Tricks
#32Regarding "Comment your code!": At least for MSSQL, it’s often recommended not to use -- for comments but instead /**/, because many features like the query store save queries without line breaks, so if you get the query from there, you need to manually fix everything instead of simply using your IDEs formatter.
I didn't realise that, great to know. Thanks!
Re: SQL Tips and Tricks
#33The "readability" section has 3 examples. The first 2 are literally sacrificing readability so it's easier to write, and the last has an unreadable abomination that indenting is really not doing much.
That's a totally valid point haha.
Re: SQL Tips and Tricks
#34I'll add some of mine: Learn your DB server. Check the query plans often. You might get surprised. Tweak and recheck. Usually EXISTS is faster than IN. Beware that NOT EXISTS behaves differently than EXCEPT in regards to NULL values. Instead of joining tables and using distinct or similar to filter rows, consiser using subquery "columns", ie in SELECT list. This can be much faster even if you're pulling 10+ values fr…
> Learn your DB server. Check the query plans often. You might get surprised. Tweak and recheck.
Oftentimes the well-designed queries behave unexpectedly, because the column statistics are not updated or when the data is fragmented for big tables (e.g. random PK insertion).
Re: SQL Tips and Tricks
#35The "readability" section has 3 examples. The first 2 are literally sacrificing readability so it's easier to write, and the last has an unreadable abomination that indenting is really not doing much.
Alternatively, write a mess of SQL like a three year old child that just discovered MSPaint then push the "beautifier" button and knock off for an early lunch.
Re: SQL Tips and Tricks
#36Regarding "Comment your code!": At least for MSSQL, it’s often recommended not to use -- for comments but instead /**/, because many features like the query store save queries without line breaks, so if you get the query from there, you need to manually fix everything instead of simply using your IDEs formatter.
That sounds like a bug in the query store
Re: SQL Tips and Tricks
#37The "readability" section has 3 examples. The first 2 are literally sacrificing readability so it's easier to write, and the last has an unreadable abomination that indenting is really not doing much.
I'm not the biggest fan of how the first two conventions look, but they are real conventions used by real SQL people. And I can understand why they exist. I've seen them enough to not be bothered by them any more.
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.
Re: SQL Tips and Tricks
#38Never use WHERE 1=1. It is both a security risk and a performance risk to run dynamic ad-hoc queries.
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.
Re: SQL Tips and Tricks
#39Never use WHERE 1=1. It is both a security risk and a performance risk to run dynamic ad-hoc queries.
Can you elaborate on security issues here?
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.
Re: SQL Tips and Tricks
#40The "readability" section has 3 examples. The first 2 are literally sacrificing readability so it's easier to write, and the last has an unreadable abomination that indenting is really not doing much.
That's a totally valid point haha.
I'll try to give some constructive criticism instead of a drive by pot shot. I'm sorry, it's just that the leading commas make my eyes bleed and I really hope the industry moves away from it.
On point 3: What I do is use CTEs to create intermediate columns (with good names) and then a final one creating the final column. It's way more readable.
```sql
with intermediate as (
select
DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) > 7 as days_7_difference,
DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) >= 29 as days_29_difference,
LAG(overnight_fta_share, 1) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity) as overnight_fta_share_1_lag,
LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity)as overnight_fta_share_2_lag
select
iff(days_7_difference, overnight_fta_share_1_lag, null) as C7_fta_share,
iff(days_29_difference, overnight_fta_share_2_lag, null) as C28_fta_share