From: | tv(at)fuzzy(dot)cz |
---|---|
To: | "salah jubeh" <s_jubeh(at)yahoo(dot)com> |
Cc: | "pgsql" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: What is the difference between these queries |
Date: | 2011-04-12 10:29:27 |
Message-ID: | 261d92079468d276552b4df3f01d5153.squirrel@sq.gransy.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Lonni J Friedman | 2011-04-12 10:40:15 | UPDATE failed with 'ERROR: index row requires 8968 bytes, maximum size is 8191' |
Previous Message | Jeremy Palmer | 2011-04-12 10:11:54 | Re: Out of memory |