tv@xxxxxxxx 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
