Weird issue with the order of clauses seeming to matter It's not an issue for me I was more just looking for an answer as to WHY it does this.

I have 2 queries both the same with the exception that one has a where clause in a different place (but no so that it should affect the results).

apologies I deal with sensitive information so these images don't really do this justice

Query 1:

Query 1 Results:



Query 2:


Query 2 Results:



I guess you will just have to take my word that both queries are exactly the same (aside form the IN statement moving to the top, underneath the date query, in the first query its at the bottom) but there is no subqueries or anything so I don't understand why both queries don't yield the same results lol

EDIT : Full queries with changed names;



While Re-writing the full query I noticed a hidden OR, god this is embarrassing, the last line is;

AND NOT myTable2.field LIKE 'Other Thing' OR 'Some OtherThing'


so When I added the line to the bottom it obviously became part of the OR LOL!
OK, just to follow up my correct (yay!) comment answer - in my experience almost everything along the lines of "weird issue" involving data selection, involves a rogue, unparenthesised "OR" somewhere in the where clause. It's always the first place I'd look.

Typically you write some code along the lines of:

WHERE a
AND b OR c
AND d


and then when you look at the code your mind kind of fills in the parenthesis where you think it ought to be. Whereas order or precedence rules actually perform it thus:

WHERE (a AND b) OR (c AND d)


A good trick to use when coding, which I tend to do automatically now, is as soon as you use an "OR" anywhere in your code, then add some parenthesis to make it totally clear.

Even if your where clause is simply:

WHERE a OR b


Then explicitly change it to

WHERE ( a OR b )


since, you're future-proofing against someone adding to the code later and accidentally writing something like:

WHERE a OR b
AND c