CODEWITHSUNDEEP
sqlwhere-clause
Spencer Barnes
Spencer Barnes

Reputation: 2877

Will this 'Where' condition speed up SQL query

I am going to be making a complex (for me) SQL query that involves finding the totals of both invoiced value and goods received value both linked to a purchase order line (so that's 3 joined tables, and possibly more) along with various date filters and so on.
In many cases, PO lines will reach a state where I know I won't ever have to worry about them again. I could therefore add a logic field to my PO line table to show this, tick the relevant lines as I go along, and add a where condition in the SQL to make it ignore them.

What I want to know is, will that Where condition be executed before or after the Select? Because if it's doing all the calcs and then just filtering the output, I don't want it as it's just more time/processing. If however the Where clause filters the input (ie before it does any calcs) then it could be a very significant time-saver.

I know you could just say 'try it and see' but whether or not I add that logic field has implications for other processes and reports, so I want to get it planned without spending too much time building a test.

So, the TL;DR: Is Where executed before Select in SQL? Or to put it another way, does Where filter the input or the output of a query?

Hope that makes sense, I'm still a bit of a beginner here!

Upvotes: 1

Views: 47

Answers (1)

marc_s
marc_s

Reputation: 754220

Check out Order of execution which shows that the WHERE clause is evaluated in step 2, while SELECT is step 5.

So the WHERE filters (and thus reduces) the number of rows that will be processed by the SELECT.

I'm not entirely sure if this applies to all regular SQL-based database engines - the site doesn't seem to limit itself to a single RDBMS - so there's a chance this will be handled the same by any relational database system

Upvotes: 2

Related Questions