Search Postgresql Archives

Re: What is the difference between these queries

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



>
> 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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux