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