How to bulk SELECT rows with multiple pairs in WHERE clause

mysql, mysql-5.5, sql

Solution

If you're after elegant SQL, you could use row constructors:

SELECT * FROM email_phone_notes WHERE (email, phone) IN (
  ('foo@bar.com'  , '555-1212'),
  ('test@test.com', '888-1212')
  -- etc.
);

However, that's not at all index-friendly and would not be recommended on a table of any significant size. Instead, you could materialise a table with your desired pairs and join that with your table:

SELECT * FROM email_phone_notes NATURAL JOIN (
  SELECT 'foo@bar.com' AS email, '555-1212' AS phone
UNION ALL
  SELECT 'test@test.com', '888-1212'
-- etc.
) t;

Or else pre-populate a (temporary) table:

CREATE TEMPORARY TABLE foo (PRIMARY KEY (email, phone)) Engine=MEMORY
  SELECT email, phone FROM email_phone_notes WHERE FALSE
;

INSERT INTO foo
  (email, phone)
VALUES
  ('foo@bar.com'  , '555-1212'),
  ('test@test.com', '888-1212')
  -- etc.
;

SELECT * FROM email_phone_notes NATURAL JOIN foo;

Problem

Let's say I have a table, `email_phone_notes` that looks like this: ``` +-----------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+--------------+------+-----+---------+-------+ | email | varchar | NO | PRI | NULL | | | phone | varchar | NO | PRI | NULL | | | notes | text | NO | | 0 | | +-----------------------+--------------+------+-----+---------+-------+ ``` So, each email/phone combination is unique, but you could have several email addresses with different phone numbers and vice versa. This is a little contrived but it mirrors my scenario. I'd like to do a query like this: ``` SELECT * FROM email_phone_notes  WHERE email = 'foo@bar.com' AND phone = '555-1212'; ``` But, I'd like to do multiple pairs at once so I don't have to make several `SELECT` queries. It's also important to keep the pairs together because I don't want to return an errant phone/email combination that wasn't requested. I could do something like this, but for the possibility of several hundred values the query will be really long. ``` SELECT * FROM email_phone_notes WHERE ( (email='foo@bar.com' && phone='555-1212') || (email='test@test.com' && phone='888-1212') || ... ``` Is there a more elegant solution, or should I stick with this?

Original source