-1

I'm looking to optimise an SQL query with two IN conditions in the WHERE clause and preserve the original behavior. For example:

New query:

select * from empl
where age in (50,65) and location in ('Panama','Nigeria');

Original query:

Select * from empl
where (age = 50 and location = 'Panama') or (age = 65 and location  = 'Nigeria');

Problem: The original query returns 2 rows, but the new query clubs all the ages and location in the IN condition and returns multiple rows.

How to optimize this properly?

Sundar
  • 86
  • 9
  • Do you mean age exactly 50 or 65, or do you mean age **between** 50 and 65? Do you need to select on gender at all, given that almost everyone will identify as one of those two options. What about people who don't? – Tangentially Perpendicular Apr 02 '23 at 03:05
  • The age is exact, updated gender with a different where condition, kindly check – Sundar Apr 02 '23 at 03:11
  • If the query can be updated to replace gender with some arbitrary places around the world then the whole premise makes no sense. The age selection is bizarre. There's something you're not telling us. Voting to close as needing details or clarity. – Tangentially Perpendicular Apr 02 '23 at 03:52
  • 2
    What database are you using? You tagged a bunch. – Shawn Apr 02 '23 at 05:31
  • 1
    I removed the conflicting DBMS tags. Please add only one tag for the database product you are really using. –  Apr 02 '23 at 07:17

1 Answers1

2

To test equality for age and location in lockstep like in your original query, use ROW values in Postgres. Like:

SELECT * FROM empl
WHERE (age, location) IN ((50,'Panama'), (65,'Nigeria'));

See:

Aside: age is typically bad design and should be replaced with something like birthday.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228