SQL Statement ignores where clause

Question

I have the following SQL statement that list all the columns of a table, and for some reason it ignores the last 2 lines of the where clause:

AND t.name ='table1' 
AND s.name = 'dba'

What am I doing wrong? This is the complete SQL code:

SELECT DB_Name() AS DatabaseName
,s.[name] AS SchemaName
,t.[name] AS TableName
,c.[name] AS ColumnName
,'[' + DB_Name() + ']' + '.[' + s.NAME + '].' + '[' + T.NAME + ']' AS FullQualifiedTableName
,d.[name] AS DataType
FROM sys.schemas s
INNER JOIN sys.tables t ON s.schema_id = t.schema_id
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN sys.types d ON c.user_type_id = d.user_type_id
WHERE d.NAME LIKE '%int%'
OR d.NAME LIKE '%float%'
OR d.NAME LIKE '%decimal%'
OR d.NAME LIKE '%numeric%'
OR d.NAME LIKE '%real%'
OR d.NAME LIKE '%money%'
AND is_identity = 0
AND t.name ='table1' 
AND s.name = 'dba'

you should read about logical operators precedence – Lamak Jun 27 '17 at 19:48 — Lamak, Jun 27 '17 at 19:48

score 4 · Accepted Answer · answered Jun 27 '17 at 19:49

4

put all of your d.Name statements in '()'

WHERE (d.NAME LIKE '%int%'
OR d.NAME LIKE '%float%'
OR d.NAME LIKE '%decimal%'
OR d.NAME LIKE '%numeric%'
OR d.NAME LIKE '%real%'
OR d.NAME LIKE '%money%') AND ...

answered Jun 27 '17 at 19:49

Matthew Wherry

343
2
11

score 2 · Answer 2 · answered Jun 27 '17 at 19:50

Reading this question out of context I cannot know for sure what result you want. I presume that you want all the ANDs to be true, and one of the ORs. In this case, you should modify your query like so:

WHERE (d.NAME LIKE '%int%'
OR d.NAME LIKE '%float%'
OR d.NAME LIKE '%decimal%'
OR d.NAME LIKE '%numeric%'
OR d.NAME LIKE '%real%'
OR d.NAME LIKE '%money%')
AND is_identity = 0
AND t.name ='table1' 
AND s.name = 'dba';

Notice the added parentheses. These specify that you want one of the clauses in the parentheses, and all the ones that are outside, to be true. This topic is called Logic Operator Precedence and has been discussed in depth in this thread. In short, it works in a similar way to the order of operation in math. You can either use parentheses and specify the order you want, or let SQL choose for you. You can read more about it here. Also, here is an article that deals specifically with your case (combination of ANDs and ORs.

However, I can't be sure if that is even what you want. SQL can't be sure either, which is why you get "incorrect" results.

score 1 · Answer 3 · answered Jun 27 '17 at 19:51

1

Use parenthesis to group your where clauses ("and" and "or"). I'm on my phone so I didn't try myself but I suspect a boolean operator precededence issue...

answered Jun 27 '17 at 19:51

shadowsheep

14,048
3
67
77

SQL Statement ignores where clause

3 Answers3