t...@fuzzy.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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
