I need to repeatedly retrieve a group of records from a data warehouse fact table based on multiple where conditions.


WHERE (user_d=1 OR user_id=2) AND (branch_id=1 OR branch_id=2)


WHERE (user_id=1 AND branch_id=1) OR (branch_id=2 AND branch_id=2)

I was thinking of doing something like this:

... WHERE CONCAT(user_id,branch_id) IN ('11','22 )

and then creating a new column, turning the concat into a natural key which will be created on insert:

WHERE new_key IN ('11','22')

This will allow:

  • SELECT queries to avoid JOINS. I have input of user_id and branch_id, and can generate that in the application.

  • I can put a unique constraint on the key field, and then can use an UPSERT.

Is generating a natural key like this strictly for easier lookups bad practice?


I want to use an IN clause because I will have a lot of combined values to lookup.

I won't have IDs to build the new_key or look it up, it will be values.

I think I answered my own question. It's bad practice because it's redundant data.

The CONCAT will be a better choice off a JOIN. To optimize, instead of storing values in the database, it's better to add a key-value store in front like redis.

1 Answer 1


Just use composite keys. If the table's natural key is (user_id, branch_id), use that.

    user_id ....,
    branch_id ...,
    PRIMARY KEY(user_id, branch_id);

In joins you can do it like you showed:

FROM sometable s
INNER JOIN whatever w ON (s.user_id = w.user_id AND s.branch_id = w.branch_id)

or pairwise:

FROM sometable s
INNER JOIN whatever w ON (s.user_id, s.branch_id) = (w.user_id = w.branch_id)

For use with IN, use lists of anonymous records:

FROM whatever w
WHERE (w.user_id, w.branch_id) IN ( ('a','1'), ('b', '2'), ... )

or if you have a lot of values, where this starts getting inefficient, join on a temp table. Big IN lists are bad whether they're singular values or composite.

  • Thanks. I want to use an IN clause because I have a lot of keys to look up. Also, I want to have the keys made of values, not IDs. I should have specified.
    – stampede76
    Aug 19, 2016 at 2:44
  • Value, ID, whatever. same principle. Don't concatenate like that, use composite keys. Aug 19, 2016 at 6:05
  • Edited, hope that helps. Really, use composite keys. Aug 19, 2016 at 6:12
  • IN ( ('a','1'), ('b', '2'), ... ) that really helps, thank you. I used a JOIN, and have fields from multiple tables in the WHERE clause.
    – stampede76
    Aug 19, 2016 at 12:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.