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
