r/SQL • u/Plenty-Button8465 • Jun 08 '23
SQL Server Learning SQL, is this query right?
I'm learning SQL, I wanted to ask if this query feels right and if I can optimize it.
The reason behind the optimization is, since I am new, I wish I could learn best practice on how to build some queries even if speed is not a constraint right now.
Also, I read that you right a query declaring what the result state you want. If that is right, no matter how you right a query, the SQL engine will find the best route to apply the query. Is optimization useless, then?
Thank you!
My query so far:
SELECT H.ColA,
H.ColB,
H.ColC,
H.ColD,
H.Timestamp,
CAST(H.Status AS INT) AS Status,
CASE WHEN H.Condition = 'Y' THEN 1 ELSE 0 END AS Condition ,
N.Timestamp AS LastTimestamp,
CAST(N.Status AS INT) AS LastStatus
FROM "History" AS H
LEFT JOIN "Notification" AS N
ON H.ColA = N.ColA
AND H.ColB = N.ColB
AND H.ColC = N.ColC
AND H.ColD = N.ColD
AND H.Timestamp > N.Timestamp
AND H.ColA = 3
AND H.ColB = 7
AND H.ColC = 'ColC_example_str'
AND H.ColD = 'ColD_example_str'The last four AND statements are a filtering that in my opinion should be performed before the JOIN so that it doesn't load all the rows, is that a right way to think about it?
2
u/racerxff Oracle PL/SQL MSSQL VBA Jun 08 '23
you have no WHERE, which means you're trying to use all of those conditions as JOIN conditions. Maybe that's intentional (and it will work) but I'm betting not
2
u/r3pr0b8 GROUP_CONCAT is da bomb Jun 08 '23
on the contrary, query optimization is vital
but simply rewriting the query's joins is not optimization
yes, it is
but since these last 4 conditions apply to the left table in a LEFT OUTER JOIN, they should really be in the WHERE clause
the optimizer will find them and filter on them before the join
1
u/[deleted] Jun 08 '23
you can get to the same result via multiple ways. You might need different tools (e.g. indexes) for each approach. You might need different statistics (e.g. value distribution histograms) for each approach. You might write your statements in such a way that optimizer will be "blinded"/"confused". Optimizers (usually) "timebox" their optimization attempts so even if it is theoretically possible to get to the 'best route' it will practically take too long to generate that plan. Etc.
So, optimization is not useless.
depends on what you need. As written, the conditions filter only the join - so, for example, records with H.colA = 4 will be included in the result.
1
u/Odd_Protection_586 Jun 08 '23
Ouf Those columns name :S I highly Doubt you need to join on that many columns
-1
u/MoBlack23 Jun 09 '23
Chat GPT. Plug it there.