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.

Improve this question
3
  • 1
    What's the purpose of this 'without using OR' constraint? Commented Mar 21, 2019 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). Commented Mar 21, 2019 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. Commented Mar 21, 2019 at 13:07

2 Answers 2

Reset to default
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.

Improve this answer
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). Commented Mar 21, 2019 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”... Commented Mar 21, 2019 at 13:08
  • Thanks! So what would be the most efficient way to print the rows that have the same value in multiple columns? Commented Mar 21, 2019 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.)

Improve this answer

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.