where clause with composite primary key-CodePudding

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.

CodePudding user response：

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

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

CodePudding user response：

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

CodePudding user response：

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.