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.

regards, tom lane