Question Re: SQL Where clause greater/less than multiple numeric columns in Technology Q&A

SQL Where clause greater/less than multiple numeric columns

dhkom — Thu, 22 Apr 2021 11:56:12 GMT

I never thought I'd be asking an SQL question here. However today is the day!

Say I have a table with numeric columns A, B populated as follows. We can think of column A being a foreign key to a parent table, and column A and B together being the primary key of its child table:

I would like a SQL Select to return all records after, for example, (2, 1). I.e. (2, 2), (3, 1), and (3, 2).

Obviously the following won't work (it won't return (3, 1)):

select A, B from mytable when A > 2 and B > 1

I wish there were a way to write "A > 2 and B > 1" in a way that indicates B is "is a breakdown" of A, for lack of a better way to express it.

Of course I could create a derived column with the two numbers concatenated together, padded with enough zeros to accomodate maximum number size. Something like:

A  B  AandB
1  1  0101
1  2  0102
2  1  0201
2  2  0202
3  1  0301
3  2  0302

... Then I could write the SQL I want as:

select A, B from mytable when AandB > '0201'

However it would be wonderful if I could write a Where clause operating on the original numbers.

Maybe it would have been best to avoid multiple numeric columns making up a child table's key, although I'm not sure avoiding such would always eliminate the need for what I'm asking about.

This has been a tough one to Google search for solutions to. Thoughts and ideas are welcome!

Re: SQL Where clause greater/less than multiple numeric columns

johnsmirnios — Thu, 22 Apr 2021 12:27:11 GMT

That would be a tuple comparison. The following would work:

WHERE (A = 2 AND B > 1) OR A > 2

Re: SQL Where clause greater/less than multiple numeric columns

VolkerBarth — Thu, 22 Apr 2021 12:29:10 GMT

I'd guess the folowing should do:

select A, B from mytable where A = 2 and B > 1 or A > 2;

Re: SQL Where clause greater/less than multiple numeric columns

johnsmirnios — Thu, 22 Apr 2021 12:32:51 GMT

I'm a little fuzzy on arrays but the following might also work and is easily extended to more columns:

WHERE ARRAY( A, B ) > ARRAY( 2, 1 )

Re: SQL Where clause greater/less than multiple numeric columns

dhkom — Thu, 22 Apr 2021 12:38:41 GMT

Thanks, John. I probably should have figured that out too - sometimes we're just blocked. I've not used this ARRAY syntax and will definitely investigate.

Re: SQL Where clause greater/less than multiple numeric columns

dhkom — Thu, 22 Apr 2021 12:40:57 GMT

Thanks, Volker. I probably should have figured that out too - sometimes we're just blocked. Thinking more about this, multi-level numeric primary keys should probably be avoided in favor of surrogate keys. This may have eliminated my need here. However the database in question is well established as it is.

Re: SQL Where clause greater/less than multiple numeric columns

VolkerBarth — Thu, 22 Apr 2021 12:41:49 GMT

The somewhat shorter answer for those aware of the higher precedende of AND vs. OR...:)

Re: SQL Where clause greater/less than multiple numeric columns

johnsmirnios — Thu, 22 Apr 2021 12:57:49 GMT

It's fine to rely on precedence but I have had too many first hand experiences in multiple languages where the author clearly assumed the wrong precedence 🙂 There's no mistaking the order of operations when the parentheses are given.

My favourite one from C/C++ is

bool flag_is_set = flags&SOME_FLAG_MASK != 0;

It does not do what the author intended. It does the following:

bool flag_is_set = flags & (SOME_FLAG_MASK != 0);

which will always return zero if SOME_FLAG_MASK is zero or the low bit of flags otherwise.

I've also seen

if( a &&

b || c &&

d )

{ ... }

and they clearly meant (b || c)

Re: SQL Where clause greater/less than multiple numeric columns

VolkerBarth — Thu, 22 Apr 2021 15:03:49 GMT

Well, I do get the point. On the other hand, I certainly dislike it when each and every condition is put into parantheses, as some tools do... It just makes it harder to read, and usually there is common knowledge of precedence rules. (Bitwise operators are less common, for sure...)

Re: SQL Where clause greater/less than multiple numeric columns

André_Schild — Fri, 23 Apr 2021 05:10:47 GMT

If your know the number range of your columns, then perhaps just multiply the first one by for example 10000 and then filter n that one.

Something like:

select A, B from mytable where A*10000+B  > 20001

Of course only works if you don't run into a numeric overflow, and the array solution already mentioned is elegant. I'm just wondering how both solutions compare on the performance level for large tables

Re: SQL Where clause greater/less than multiple numeric columns

dhkom — Fri, 23 Apr 2021 12:27:06 GMT

Thanks. Great ideas from all!