0

I have created this table:

CREATE TABLE IF NOT EXISTS `names` (
  `ID` int(6) unsigned NOT NULL,
  `C1` varchar(200),
  `C2` varchar(200),
  `C3` varchar(200),
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `names` (`ID`, `C1`, `C2`, `C3`) VALUES
  ('1', 'Richard', 'Ken', 'Dan'),
  ('2', NULL,'Richard', 'Helen'),
  ('3', 'Ken', NULL, 'Maria');

I can select rows that contain "Ken" with an OR:

SELECT *
FROM `names`
WHERE C1 = 'Ken' or C2 = 'Ken' or C3 = 'Ken'

But, I need a better method to select the rows which have “Ken” in one of the columns C1, C2, or C3, which is without using OR.

3
  • 1
    What's the purpose of this 'without using OR' constraint?
    – danblack
    Mar 21 '19 at 0:33
  • If there is no indices by separate fields (as shown in your CREATE TABLE) the first Aaron Bertrand♦'s solution seems to be optimal. Iа you create separate indices by each field to search in, the solution with 3 separate queries (each by one field only) combined by UNION may be faster (dependent by the count of records in the table and the count of records in output).
    – Akina
    Mar 21 '19 at 4:47
  • @Akina A union can end up being far more costly, especially if the output really needs to be all columns and there are a lot of rows. Mar 21 '19 at 13:07
1

You can do this without the OR keyword, in a couple of ways:

WHERE 'Ken' IN (C1,C2,C3);

But that will get rewritten to use the same plan.

Or worse:

WHERE CONCAT(C1,C2,C3) LIKE '%Ken%';

Maybe you could explain why you don't want OR for better answers.

3
  • 1
    Using CONCAT may give errorneous result - the substring which is searched may occur in the result of concatenation virtually (the starting part is taken from the end of one field value and the slack is taken from the beginning of another field value). To fix this you can add non-text separators which is absent in substring you search for, for example, CONCAT(C1,CHAR(0),C2,CHAR(0),C3).
    – Akina
    Mar 21 '19 at 4:43
  • @Akina yes, of course. Not a final solution and certainly not my first choice. Just spitballing ways to get around the odd requirement of “no OR”... Mar 21 '19 at 13:08
  • Thanks! So what would be the most efficient way to print the rows that have the same value in multiple columns? Mar 21 '19 at 15:44
0

Your question is an example of why one should not spread an "array" across columns.

Instead, have another table with (ID, name) where ID can be joined to your main table. The main table no longer has c1,c2,c3. Then, with an INDEX(name, ID) on the new table, the lookup is easier and much faster.

(PS: WHERE 'Ken' IN (C1,C2,C3) will not be efficient since it must look at every row.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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