Thread: What is the difference between these queries

>
> 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. To get equal results you should
move it to the first SELECT (in the former query):

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'
EXCEPT
SELECT a, b
FROM table1 LEFT JOIN table2 on (table1_id = tabl2_id)
Where table1_id NOT IN (SELECT DISTINCT table1_id FROM table3)

or to the subselect

SELECT a, b
FROM table1 LEFT JOIN table2 on (table1_id = tabl2_id)
Where table1_id NOT IN (SELECT DISTINCT table1_id FROM table3 WHERE b !~*
'pattern')

Not sure which of those solutions is the right one (depends on what the
query is supposed to do0.

Tomas

tv@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

> tv@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.

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