Обсуждение: Query optimization to select rows instead of too many or conditions
Hi, Suppose I want to select the rows from a table, say Emp, as below : Select * from Emp where (attr1 = val11 and attr2 = val12 and attr3 = val13) or (attr1 = val14and attr2 = val15 and attr3 = val16); Now suppose I got (x1, x2, x3) and (y1, y2, y3). Then I need to rewrite my query as : Select * from Emp where (attr1 = val11 and attr2 = val12 and attr3 = val13) or (attr1 = val14 and attr2 = val15 and attr3 = val16) or (attr1 = x1 and attr2 = x2 and attr3 = x3) or (attr1 = y1 and attr2 = y2 and attr3 = y3); So for each new set I need to add one more `or` conditions. It seems wired. Any better way to get it done ? -- ================ Regards, Arup Rakshit ================ Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible,you are, by definition, not smart enough to debug it. --Brian Kernighan
> On 21 Feb 2015, at 9:34, Arup Rakshit <aruprakshit@rocketmail.com> wrote: > > Select * from Emp > where (attr1 = val11 and attr2 = val12 and attr3 = val13) or (attr1 = val14and attr2 = val15 and attr3 = val16); > > Now suppose I got (x1, x2, x3) and (y1, y2, y3). Then I need to rewrite my query as : > > Select * from Emp > where (attr1 = val11 and attr2 = val12 and attr3 = val13) or (attr1 = val14 and attr2 = val15 and attr3 = val16) or > (attr1 = x1 and attr2 = x2 and attr3 = x3) or (attr1 = y1 and attr2 = y2 and attr3 = y3); > > So for each new set I need to add one more `or` conditions. It seems wired. > > Any better way to get it done ? If the number of attributes to compare is always the same, you can write: select * from Emp where (attr1, attr2, attr3) in ((val11, val12, val13), (val14, val15, val16), (x1, x2, x3), (y1, y2, y3)); Is that the sort of thing you're after? Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
On Saturday, February 21, 2015 12:42:03 PM Alban Hertroys wrote:
>
> > On 21 Feb 2015, at 9:34, Arup Rakshit <aruprakshit@rocketmail.com> wrote:
> >
> > Select * from Emp
> > where (attr1 = val11 and attr2 = val12 and attr3 = val13) or (attr1 = val14and attr2 = val15 and attr3 = val16);
> >
> > Now suppose I got (x1, x2, x3) and (y1, y2, y3). Then I need to rewrite my query as :
> >
> > Select * from Emp
> > where (attr1 = val11 and attr2 = val12 and attr3 = val13) or (attr1 = val14 and attr2 = val15 and attr3 = val16) or
> > (attr1 = x1 and attr2 = x2 and attr3 = x3) or (attr1 = y1 and attr2 = y2 and attr3 = y3);
> >
> > So for each new set I need to add one more `or` conditions. It seems wired.
> >
> > Any better way to get it done ?
>
> If the number of attributes to compare is always the same, you can write:
>
> select * from Emp where (attr1, attr2, attr3) in ((val11, val12, val13), (val14, val15, val16), (x1, x2, x3), (y1,
y2,y3));
>
> Is that the sort of thing you're after?
>
> Alban Hertroys
Thanks for the reply, Can the comparisons be done using Array ? I tried, but got some syntax error.
psql (9.2.7)
Type "help" for help.
app_development=# select * from pets where ARRAY[id, animals] IN '{{1,2,3},{4,5,6},{7,8,9}}';
ERROR: syntax error at or near "'{{1,2,3},{4,5,6},{7,8,9}}'"
LINE 1: select * from pets where ARRAY[id, animals] IN '{{1,2,3},{4,...
^
app_development=# select * from pets where ARRAY[id, animals] IN '{{6, 1}, {5, 1}}';
ERROR: syntax error at or near "'{{6, 1}, {5, 1}}'"
LINE 1: select * from pets where ARRAY[id, animals] IN '{{6, 1}, {5,...
^
app_development=#
--
================
Regards,
Arup Rakshit
================
Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as
possible,you are, by definition, not smart enough to debug it.
--Brian Kernighan
> On 21 Feb 2015, at 12:03, Arup Rakshit <aruprakshit@rocketmail.com> wrote:
>
> On Saturday, February 21, 2015 12:42:03 PM Alban Hertroys wrote:
>>
>>> On 21 Feb 2015, at 9:34, Arup Rakshit <aruprakshit@rocketmail.com> wrote:
>>>
>>> Select * from Emp
>>> where (attr1 = val11 and attr2 = val12 and attr3 = val13) or (attr1 = val14and attr2 = val15 and attr3 = val16);
>>>
>>> Now suppose I got (x1, x2, x3) and (y1, y2, y3). Then I need to rewrite my query as :
>>>
>>> Select * from Emp
>>> where (attr1 = val11 and attr2 = val12 and attr3 = val13) or (attr1 = val14 and attr2 = val15 and attr3 = val16) or
>>> (attr1 = x1 and attr2 = x2 and attr3 = x3) or (attr1 = y1 and attr2 = y2 and attr3 = y3);
>>>
>>> So for each new set I need to add one more `or` conditions. It seems wired.
>>>
>>> Any better way to get it done ?
>>
>> If the number of attributes to compare is always the same, you can write:
>>
>> select * from Emp where (attr1, attr2, attr3) in ((val11, val12, val13), (val14, val15, val16), (x1, x2, x3), (y1,
y2,y3));
>>
>> Is that the sort of thing you're after?
>>
>> Alban Hertroys
>
> Thanks for the reply, Can the comparisons be done using Array ? I tried, but got some syntax error.
Why would you want that?
> psql (9.2.7)
> Type "help" for help.
>
> app_development=# select * from pets where ARRAY[id, animals] IN '{{1,2,3},{4,5,6},{7,8,9}}';
> ERROR: syntax error at or near "'{{1,2,3},{4,5,6},{7,8,9}}'"
> LINE 1: select * from pets where ARRAY[id, animals] IN '{{1,2,3},{4,...
> ^
> app_development=# select * from pets where ARRAY[id, animals] IN '{{6, 1}, {5, 1}}';
> ERROR: syntax error at or near "'{{6, 1}, {5, 1}}'"
> LINE 1: select * from pets where ARRAY[id, animals] IN '{{6, 1}, {5,…
At the very least you will need to put braces around the set in IN.
But even then, you'll have to write the query such that ALL elements in your left-hand array are being matched to ALL
elementsin any of the right-hand arrays. Is that possible? Probably, but at this point I feel like I should remind you
ofwhat you put in your signature.
Something like this is closer to what you need:
select * from pets where ARRAY[id, animals] = ANY('{6, 1}', '{5, 1}');
I'm sure that won't match anything though, as the types are all wrong.
> Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as
possible,you are, by definition, not smart enough to debug it.
>
> --Brian Kernighan
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.