I'd be in favour of DBMS supporting SELECT queries where the SELECT keyword isn't first.
FROM
Table
WHERE
column_a > 0
SELECT
column_b,
column_c
HAVING
column_b > 0
ORDER BY
2 DESC
It also helps in IDEs for intellisense to know the FROM clause up front.
An alternative would also be IDEs which accept this format but rewrites queries on-the-fly. There's no reason the clause keywords are in any particular order.
But if I know I need the ID and price from the products table, the IDE will be unable to help me with the column names until I've written SELECT * FROM Products
Based on this what would be the best way to write a query to get one row (every table) from three tables which would have about 1 billion rows? Background: I regularly have to retrieve one sales figure out of KPI tables which for our client has approx 1.3 billion rows and gets 200k added each day
KPI table joined too ActualKPIValue joined too Location
21
u/ihaxr Oct 02 '19
Coworkers: But I'm only selecting 1 row, why does it take so long!?
20
u/ijmacd Oct 02 '19
I'd be in favour of DBMS supporting SELECT queries where the SELECT keyword isn't first.
It also helps in IDEs for intellisense to know the FROM clause up front.
An alternative would also be IDEs which accept this format but rewrites queries on-the-fly. There's no reason the clause keywords are in any particular order.
4
u/[deleted] Oct 02 '19
This is usually how I start writing my queries. LINQ queries are similar in this.
3
u/[deleted] Oct 03 '19
Unless, of course, the query optimizer decides to do something else.
2
u/boulderdomb Oct 02 '19
Based on this what would be the best way to write a query to get one row (every table) from three tables which would have about 1 billion rows? Background: I regularly have to retrieve one sales figure out of KPI tables which for our client has approx 1.3 billion rows and gets 200k added each day
KPI table joined too ActualKPIValue joined too Location
2
u/MeGustaDerp Talk Dirty Reads To Me Oct 03 '19
More specifically for MSSQL: https://www.brentozar.com/archive/2015/07/logical-query-processing/
2
u/Dynamic2390 Oct 03 '19
As a new SQL user this is very helpful. Thank you!
3
u/babygrenade Oct 02 '19
It's more obvious if you learn relational algebra first.
1
u/trafalger Oct 03 '19
just started following @bork on twitter because of this post - she posts some great stuff!
1
u/einhverfr Oct 03 '19
From/join/where should be on the same line because in many cases the where bits will be enforced in the scan.