-1

For example I have a composite primary key on columns id, age in the following table:

id age name
1 23 John
2 24 Alex
3 22 Rob
4 20 Ella

can I somehow query the table using composite primary key in where clause, somehow like that:

SELECT * FROM table
WHERE primary_key < (3, 22)

with result like that:

id age name
1 23 John
2 24 Alex

I cannot query it like where id < 4 and age < 22 because it would compare every column separately.

Oleksandr Baranov
  • 528
  • 2
  • 6
  • 23
  • 1
    `where (id, age) < (3, 22)` ? –  Apr 08 '22 at 14:38
  • I removed the conflicting DBMS tags. Please add only one tag for the database product you are really using. –  Apr 08 '22 at 14:38
  • You can put several clauses in an parenthesis to check both of them together - and return true or false depending on what both of them say. – Stoff Apr 08 '22 at 14:39
  • 1
    How does an (id, age) comparison make any sense? Show us some sample table data and the expected result - all as formatted text (not images). [mcve] – jarlh Apr 08 '22 at 14:40
  • @a_horse_with_no_name thanks! that's what I need! could you please make a separate answer so that I could accept it? – Oleksandr Baranov Apr 08 '22 at 14:49

3 Answers3

2

You can compare tuples with other operators than just = or IN

So

where (id, age)  < (3, 22)

is valid SQL.

But I have to admit that I find that condition highly confusing given the column names chosen

0

Out of curiosity, is this what you're looking for? It checks both ID and AGE together in this case. If ID < 3 is true AND Age > 22 is true on the same row. (Had to turn the '>' on age, otherwise you will not get the result that you're showing in the example given since it was looking for and ID that is under 3, and an Age that was under 22. There are no matches there, so to get your example working, I changed it to look for an ID under 3, and an Age over 22 instead.

Fiddle: https://www.db-fiddle.com/f/iamNwXwdWhUJGvNnTC5dXG/0

CREATE TABLE test (
  id INT,
  age int,
  name varchar(50)
);
INSERT INTO test (id, age, name) 
VALUES 
(1, 23, 'John'),
(2, 24, 'Alex'),
(3, 22, 'Rob'),
(4, 20, 'Ella');

SELECT * FROM test
WHERE ((id < 3) and (age > 22))

Result:

id age name
1 23 John
2 24 Alex
Stoff
  • 517
  • 6
  • 22
0

Some SQL engines may support that type of comparison, but it's not supported in "standard SQL"

It seems like you want:

SELECT * FROM table
WHERE (id < 3) or (id = 3 and age < 22)

which most SQL engines should handle just fine with a compound key, so long as you have a constraint on the first key that reduces the number of comparisons on the second key.

D Stanley
  • 149,601
  • 11
  • 178
  • 240