CODEWITHSUNDEEP
sqlsql-serverwhere-clause
Erik
Erik

Reputation: 61

SQL Server: Where & Like Statements

I'm fairly rusty with my SQL game. I have some code (see below) that checks on a database instance (instance001, instance002, etc), then looks for when the LAST_READ and LAST_WRITE fields are null, along with when it was last reset. Nothing too over the top.

The issue I'm having is how to execute this across multiple instances. You'll see I commented out a section. If I add in the appropriate OR statement (or inst_name like 'instance002'), it isn't getting the results I was seeking.

Example: instance001 yields 1 records. instance002 yields 60. If I use where inst_name like 'instance001' or inst_name like 'instance002' I get 210 records. How would I manipulate the SQL to provide 61? 

declare @timeString nvarchar(50) = CONVERT(varchar(24), GETDATE(), 120)
select DB_NAME, INST_NAME, MIN([LAST_SRVR_RST]) AS MIN_SRVR_RST, LAST_READ, LAST_WRITE, LOG_DATE=@timeString
from CMS_DB_LAST_READ_WRITE     -- Targeted DB with data.
where inst_name                 -- Targeted instance(s)
    like 'instance001'
    /*
    like 'instance002'
    like 'instance003'  
    like 'instance004'
    */
    and LAST_READ is null       -- Both read and write must be null
    and LAST_WRITE is null      -- to show no activity.
    --and [LAST_SRVR_RST] = MIN([LAST_SRVR_RST])
group by DB_NAME, INST_NAME, LAST_SRVR_RST, LAST_READ, LAST_WRITE

Upvotes: 3

Views: 77

Answers (1)

Siyual
Siyual

Reputation: 16917

AND takes precedence over OR, you need to group the two conditions using parenthesis:

...
WHERE (inst_name = 'instance001' OR inst_name = 'instance002')
AND   LAST_READ  IS NULL 
AND   LAST_WRITE IS NULL
GROUP BY ...

Note: I changed the usage from LIKE to =, as you are looking for an exact match, and it doesn't make much sense to use LIKE in that situation.

The reason for the grouping is due to the precedence order mentioned above. This order is:

Level   Operators
1       ~ (Bitwise NOT)
2       * (Multiply), / (Division), % (Modulo)
3       + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND), ^ (Bitwise Exclusive OR), | (Bitwise OR)
4       =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
5       NOT
6       AND
7       ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
8       = (Assignment)

What this means is, all of your AND statements will be evaluated first. After they have all been evaluated, then your OR statement is evaluated.

This makes your query look more like this to the compiler:

WHERE   
(
        inst_name = 'instance001'
AND     LAST_READ  IS NULL
AND     LAST_WRITE IS NULL
)
OR      inst_name = 'instance002'

Which is why you were getting 210 results, as opposed to the expected 61, as it was pulling in all records in instance002, disregarding your other WHERE filters.

Rewriting the query grouping the two conditions as mentioned above would look like this to the compiler instead:

WHERE   
(
        LAST_READ  IS NULL
AND     LAST_WRITE IS NULL
)
AND      
(       
        inst_name = 'instance002'
OR      inst_name = 'instance001'
)

Which should be the logical conditions you were expecting.

Another alternative way you can write this query without the AND/OR grouping altogether is by using an IN clause:

...
WHERE inst_name IN ('instance001', 'instance002')
AND   LAST_READ IS NULL
AND   LAST_WRITE IS NULL
GROUP BY ...

Upvotes: 5

Related Questions