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