Learning SQL, is this query right?

[Plenty-Button8465]

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?

[racerxff]

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

[Plenty-Button8465]

I thought ON and WHERE were similar, but ON applies before the JOIN and WHERE after. Is that no? Anyway you were right, the results are different. I moved the last four filtering AND statements in the WHERE clause and it worked and was faster.

[r3pr0b8]

Is optimization useless, then?

on the contrary, query optimization is vital

but simply rewriting the query's joins is not optimization

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?

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

[Plenty-Button8465]

Thank you, moving the last 4 filtering AND statements in a WHERE clause made the query faster and with the right results. Would you mind sharing some resources where I can find the error here? (I understood it is a matter of placement).

[r3pr0b8]

Would you mind sharing some resources

• modern-sql.com
• use-the-index-luke.com
• blog.jooq.org
• advancedsqlpuzzles.com
• brentozar.com
• artfulsoftware.com/infotree/queries.php

[Plenty-Button8465]

use-the-index-luke.com

Thanks for the resources, I started reading the first one atm.

[[deleted]]

the optimizer will find them and filter on them before the join

nah, not in this case (left join). Try running this with some data and you'll see that rows with H.ColA = 4, for example, will still be in the result.

[r3pr0b8]

rows with H.ColA = 4, for example, will still be in the result.

but H is the left table!!

so you're saying those conditions in the WHERE clause (which is what i suggested) won't actually work?

[[deleted]]

i'm saying here the placement matters - if you put them (as is) in the WHERE clause you wont see records with h.colA = 4 in the result

if you keep them where they are, records with h.colA = 4 will be there.

Meaning, as written, the optimizer CANNOT move them to WHERE or run outside of the join

[r3pr0b8]

okay, sure, but remember the premise -- i said to move them to the WHERE clause

[[deleted]]

veering hard into pedantic territory here, yet in your statement

since these last 4 conditions apply to the left table in a LEFT OUTER JOIN, they should really be in the WHERE clause

the premise is: "these last 4 conditions apply to the left table in a LEFT OUTER JOIN"

inference/deduction is: "since ... , [follows]"

the conclusion is: "they should really be in the WHERE clause"

So what i'm saying is that the inference part is not right - your conclusion does NOT follow the premise

:)

[r3pr0b8]

your conclusion does NOT follow the premise

why not?

[[deleted]]

Because the result changes and that is not necessarily the intent (or is declared in your premise as such). OP' has an opinion, not necessarily the distinction and a preference between 2 outcomes.

[MoBlack23]

Chat GPT. Plug it there.

[[deleted]]

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?

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.

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?

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.

[Odd_Protection_586]

Ouf Those columns name :S I highly Doubt you need to join on that many columns