Re: What is the difference between these queries

> tv(at)fuzzy(dot)cz writes:
>>> Query1
>>> -- the first select return 10 rows
>>> SELECT a, b
>>> FROM table1 LEFT JOIN table2 on (table1_id = tabl2_id)
>>> Where table1_id NOT IN (SELECT DISTINCT table1_id FROM table3)
>>> EXCEPT
>>> -- this select return 5 rows
>>> SELECT a, b
>>> FROM table1 LEFT JOIN table2 on (table1_id = tabl2_id)
>>> Where table1_id NOT IN (SELECT DISTINCT table1_id FROM table3)
>>> and b ~* 'pattern'
>>> -- the result is 5 rows
>>>
>>> Query2
>>> --this select return 3 rows
>>> SELECT a, b
>>> FROM table1 LEFT JOIN table2 on (table1_id = tabl2_id)
>>> Where table1_id NOT IN (SELECT DISTINCT table1_id FROM table3)
>>> and b !~* 'pattern'
>>>
>>> Why query1 and query2 return different set. note that query two return
>>> a
>>> subset
>>> of query1
>
>> Those queries obviously are not equivalent - the regular expression is
>> applied to different parts of the query.
>
> Not sure I buy that ... personally I was wondering whether there were
> some null values of b.

Seems you're right - I somehow misread/misunderstood those queries. The
NULL value in 'b' seems like the most probable cause (even the fact that
query2 returns subset of query1 corresponds to this).

regards
Tomas