SQL Tips and Tricks • Hacker News Board

Re: SQL Tips and Tricks

#121

Post by TheCycoONE » Thu, Sep 26, 2024, 12:32 AM UTC

AtNightWeCode wrote: post #101

A common mistake I see is that people think foreign keys will automatically create indexes. Missing indexes is a general problem in SQL. Missing indexes on columns that are in foreign keys are even worse.

In some RDBMS a foreign key will automatically create an index: https://dev.mysql.com/doc/refman/8.4/en/create-table-foreign...

I think this falls under the read the documentation fully point.

Edit: It occurs to me you likely meant on the column itself rather than on the referenced column. I don't have an example that does that.

Re: SQL Tips and Tricks

#122

Post by beart » Thu, Sep 26, 2024, 12:57 AM UTC

magicalhippo wrote: post #63

code_biologist wrote: post #60

Earlier quoted context omitted.

Instead of joining tables and using distinct or similar to filter rows, consiser using subquery "columns", ie in SELECT list. What does this mean? Running SELECT column1, ( SELECT column2, column3, ... FROM table_b WHERE table_a.id = table_b.a_id ) FROM table_a Results in "subquery must return only one column" as I expected. You mean returning the multiple columns as a record / composite type? Keep in mind GROUP BY c…

Sorry, was on mobile so hadn't patience to type examples. SELECT column1, ( SELECT column2 FROM table_b WHERE table_a.id = table_b.a_id ) as b_column2, ( SELECT column3 FROM table_b WHERE table_a.id = table_b.a_id ) as b_column3 FROM table_a It might look like a lot more work, but in my experience it's usually a lot faster. YMMV but check it.

Would a cross apply accomplish the same result without the risk of multiple rows?

Cross apply (select top 1 ... ) x

Re: SQL Tips and Tricks

#123

Post by beart » Thu, Sep 26, 2024, 1:01 AM UTC

elchief wrote: post #22

use sqlfluff linter and do what it says

Agreed! It may not be perfect, but it's better than arguing. And the maintainers are very responsive.

Re: SQL Tips and Tricks

#124

Post by otteromkram » Thu, Sep 26, 2024, 1:13 AM UTC

Ugh. Don't put opening braces on new lines.

As for formatting, indent the first field, too

  SELECT
     employee_id
  ,  employee_name
  ,  job
  ,  salary
  FROM employees
  ;

Re: SQL Tips and Tricks

#125

Post by otteromkram » Thu, Sep 26, 2024, 1:17 AM UTC

silveraxe93 wrote: post #40

regexman1 wrote: post #33

Earlier quoted context omitted.

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…

I love leading commas and am leading the charge for change.

I apologize in advance and hope you are able to come to grips with how easy it is to read and understand at some point in the near future.

Re: SQL Tips and Tricks

#126

Post by otteromkram » Thu, Sep 26, 2024, 1:19 AM UTC

willvarfar wrote: post #15

the_gorilla wrote: post #6

Leading comma is nice in SELECT statements because you can comment toggle individual lines. Indenting your code to make it more readable is basically what anyone with room temperature IQ does automatically. A lot of these other tips look like they're designed to deal with SQL design flaws, like how handling nulls isn't well defined in the spec so it's up to each implementation to do whatever it wants.

A lot of databases support trailing commas in select clauses. Which is just as well. I want to scratch my eyes out every time I see someone formatting with comma starting the lines. It's the kind of foolish consistency that is a big part of performative engineering.

I host to help in the continued scratching of your eyes out in the future.

Leading commas are king. Simple as.

Re: SQL Tips and Tricks

#127

Post by otteromkram » Thu, Sep 26, 2024, 1:24 AM UTC

shmerl wrote: post #117

I don't get the point of the dummy value. How does it help doing anything? I can add conditions with ease without it.

Because any subsequent clauses usually start with AND, so if you're just checking data or validating it outside of production, it makes sense since you can comment to see lines you aren't checking out.

It also depends on how you write SQL, but not by much.

I wouldn't put a dummy variable into a finalized query.

Re: SQL Tips and Tricks

#128

Post by layoric » Thu, Sep 26, 2024, 1:40 AM UTC

magicalhippo wrote: post #11

I'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…

Agreed. Learn how to use EXPLAIN and interpret using whatever tools you prefer. Also monitor your queries. Something I did in a previous start up was install PgHero, and man did that tool help me optimise and prioritise performance.

Re: SQL Tips and Tricks

#129

Post by jason-phillips » Thu, Sep 26, 2024, 1:46 AM UTC

Also, window functions.

Re: SQL Tips and Tricks

#130

Post by sbuttgereit » Thu, Sep 26, 2024, 1:51 AM UTC

rawgabbit wrote: post #112

My tips for working with complex Stored Procedures. 1. At the beginning of the proc, immediately copy any permanent tables into temporary tables and specify/limit/filter only for the rows you need. 2. In the middle of the proc, manipulate the temporary tables as needed. 3. At the end of the proc, update the permanent tables enclosed within a transaction. Immediately rollback transaction/exit the proc, if an error is…

And remember that these rules may be completely valid in one vendor's database, but another's may have very different priorities/characteristics/trade offs.

Also, the version of the database can matter, too.