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