CODEWITHSUNDEEP
sqlsql-server
Kajal_T
Kajal_T

Reputation: 73

Are these two SQL Server queries the same?

I have written two queries for fetching column values that either do not start with vowels or do not end with vowels.

Query 1: 

SELECT DISTINCT CITY
FROM STATION
WHERE city NOT LIKE '[aeiou]%'
   OR city NOT LIKE '%[aeiou]'

Query 2:

SELECT DISTINCT CITY
FROM STATION
WHERE city NOT LIKE '[aeiou]%[aeiou]'

According to me, they should be same query as they pass test cases easily and filter same result. Are these two different queries in any scenario?

Upvotes: 2

Views: 140

Answers (4)

davidhigh
davidhigh

Reputation: 15488

No, they are not the same. It can be seen by application of de Morgan's rule:

!a or !b = !(a and b),

where the events a and b are given by

a = like '[aeiou]%'
b = like '%[aeiou]'.

Moreover, a and b corresponds to the event like '[aeiou]%[aeiou]', which is basically a set intersection of the two sets containing all events a and b.

EDIT: The previous sentence is wrong, and it produces wrong results exactly for one-element strings. In order to find the set a and b, consider

a = { x | first element in {a,e,i,o,u} }
b = { x | last  element in {a,e,i,o,u} }

Therefore,

a AND b = { x | first AND last element in {a,e,i,o,u} }

Now, in contrast, like '[aeiou]%[aeiou]' is the subset of a AND b that has another optional character between the first and the last element.

Sorry for the confusion.

Upvotes: 2

Abhijeet
Abhijeet

Reputation: 23

No. They are not same. Here is sample input: xyz, axyz, xyza, axyza, a.

1st query will return: xyz, axyz, xyza. In this 'AND' should be used to get the desired output. Query below will return xyz only.

SELECT DISTINCT CITY
FROM STATION
WHERE city NOT LIKE '[aeiou]%'
   AND city NOT LIKE '%[aeiou]'

2nd query will return: xyz, axyz, xyza, a. This is because the condition 

NOT LIKE '[aeiou]%[aeiou]'

means that it should not start AND end with a vowel. Hence axyza is not selected whereas a, axyz and xyza are selected, though they shouldn't have been.

Upvotes: 1

Stephan Lechner
Stephan Lechner

Reputation: 35154

No, they behave differently. I expected some differences with cities like "a", because a string with length 1 will not match pattern [aeiou]%[aeiou], but may match both [aeiou]% and %[aeiou]. Just tried it out, and actually there are differences then. One can still discuss which of both queries interpret 'a' correctly, but at least both queries behave differently:

SELECT *
  FROM (VALUES ('aasfa')
             , ('basda')
             , ('asdfb')
             , ('a')
       ) t1 (c)
       where c  not like '[aeiou]%[aeiou]';

Result:

c
-----
basda
asdfb
a

Query:

SELECT *
  FROM (VALUES ('aasfa')
             , ('basda')
             , ('asdfb')
             , ('a')
       ) t1 (c)
       where c    not  like '[aeiou]%'
or c not  like '%[aeiou]'

Result:

c
-----
basda
asdfb

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Both these queries fetch cities that have a non-vowel at the beginning or end of their name. They should return the same result set. For instance, "Miami" matches both queries.

However, they are not the same. One will execute the underlying like operator twice, and the other once. In this case, the performance difference is likely to be not noticeable, but it could make a difference under other circumstances.

If you want cities that do not have vowels at the beginning and end of their name, then:

WHERE city not  like '[aeiou]%' AND city not  like '%[aeiou]'

or:

WHERE city LIKE '[^aeiou]%[^aeiou]'

"Miami" does not match these conditions, but "Boston" does.

Upvotes: 1

Related Questions