10

Say I have a query like this:

SELECT * FROM my_table WHERE name = "john doe" AND phone = "8183321234" AND email = "johndoe@yahoo.com" AND address = "330 some lane";

But say I only need 3 out of the 4 to match, I know I can write a very long query with several ORs but I was wondering if there was a feature for this?

Thanks.

JD Isaacks
  • 56,088
  • 93
  • 276
  • 422

5 Answers5

19
SELECT
  * 
FROM 
  my_table 
WHERE 
  CASE WHEN name = "john doe"           THEN 1 ELSE 0 END +
  CASE WHEN phone = "8183321234"        THEN 1 ELSE 0 END +
  CASE WHEN email = "johndoe@yahoo.com" THEN 1 ELSE 0 END +
  CASE WHEN address = "330 some lane"   THEN 1 ELSE 0 END
  >= 3;

Side note: this will very likely not be using indexes efficiently. On the other hand, there will very likely be no indexes on these kinds of columns anyway.

Tomalak
  • 332,285
  • 67
  • 532
  • 628
  • 1
    I think it will suffice to just sum the comparison expressions as they themselse always return 0 or 1: `name="john doe" + phone="12345" + email="john.doe@example.com" + address="123 some lane" >= 3`. – Gumbo Apr 17 '09 at 16:18
  • Yes, that does it on MySQL, I guess. Nevertheless I like explicitness better, and a comparison operation does not necessarily return a numerical value on other systems. – Tomalak Apr 17 '09 at 16:30
  • Hm... I wonder why this keeps catching down-votes. Is it wrong or is this just opinion voting? – Tomalak Apr 17 '09 at 16:48
  • Well, yeah. You could argue that it is cleaner and shorter. I would hardly call my solution "overcomplex" though. It's correct, not performing any worse *and* it would run on almost any other SQL environment there is. Frankly, I don't see a good reason to down-vote, but there you go. – Tomalak Apr 17 '09 at 17:06
  • Well, I'd say it is at the very least flirting with over complexity, especially when there's a much shorter way of doing it. That being said, I've removed my downvote, because I guess you could also make the argument chaos' solution is not very clear to someone that doesn't understand how the comparisons get evaluated into 1s. :) – Paolo Bergantino Apr 17 '09 at 17:20
  • @Paolo Bergantino: "especially when there's a much shorter way of doing it" is a very Perlesque view on the world. ;-) I am used to XSLT and ColdFusion, so verbosity does not drive me away. I'm rather verbose than implicit any time, even in environments where I don't have to. – Tomalak Apr 17 '09 at 17:30
  • 1
    Yes, I suppose I should have said 'holy thundering sesquipedalian verbosity, Batman'. If someone doesn't understand numeric evaluation of boolean expressions, though, they should be able to infer it pretty quickly from the existence of my example. :) – chaos Apr 17 '09 at 18:08
  • 1
    @Tomalak: "sesquipedialian" = "1,5 feet long", from Horace's "Ars Poetica" – Quassnoi Apr 17 '09 at 20:36
  • @Quassnoi: My Google Translate already told me. :) I just like the word a lot, never saw it before. That's one detail I love about the English language - it never stops to surprise and amuse me. – Tomalak Apr 17 '09 at 21:03
  • That's more the Latin language. :) – chaos Apr 17 '09 at 22:02
  • @chaos: I am quite aware of that. ;) Nevertheless it's the English language the word is used in. – Tomalak Apr 17 '09 at 22:50
  • @chaos how did that word end up in your vocabulary? – JD Isaacks Apr 20 '09 at 20:02
  • 1
    A friend of mine used it as an element of his merciless mocking of certain turns of phrase I would use. I love it, of course. It's like I always say: never use a big word where a diminutive lexeme will suffice. – chaos Apr 20 '09 at 20:42
15

Holy overcomplexity, Batman.

SELECT * 
FROM my_table 
WHERE (
    (name = "john doe") +
    (phone = "8183321234") +
    (email = "johndoe@yahoo.com") +
    (address = "330 some lane")
) >= 3;
chaos
  • 122,029
  • 33
  • 303
  • 309
  • +1 for the Batman comment. I'm not sure if that works, but it does, the +1 is well deserved anyways, as that is very neat. :) – Paolo Bergantino Apr 17 '09 at 16:16
  • Thank you kindly. And of course it works; I never post SQL without testing it if I can help it. – chaos Apr 17 '09 at 16:26
  • I am still newb to SQL but I don't understand how that query does what it does. – JD Isaacks Apr 17 '09 at 17:00
  • Does (name = "john doe") return an int instead of a Boolean? – JD Isaacks Apr 17 '09 at 17:01
  • 1
    MySQL has no concept of 'booleans' as distinct from ints. Even most language that do have such a concept allow evaluation of a boolean in numeric context, yielding 0 or 1 integer. – chaos Apr 17 '09 at 17:05
  • 1
    Thanks, I knew you could use 1 or 0 as a Boolean but I didn't know you could use a Boolean as a 1 or a 0 (If that makes sense). – JD Isaacks Apr 20 '09 at 19:57
  • So I noticed, if any of the values are NULL then it turns them all NULL. For example the first 3 matched, you would have 3 but if the last was NULL, it would return 3+NULL=NULL and you would not get anything returned when 3 indeed did match. Is there a way around this? – JD Isaacks Dec 15 '10 at 15:40
  • Wrap all the fields in `IFNULL`, e.g. `IFNULL(name, 0)`. – chaos Dec 15 '10 at 18:21
7

Same thing using indexes:

SELECT  *
FROM    (
        SELECT  id
        FROM    (
                SELECT  id
                FROM    mytable _name
                WHERE   name = 'john doe'
                UNION ALL
                SELECT  id
                FROM    mytable _name
                WHERE   phone = '8183321234'
                UNION ALL
                SELECT  id
                FROM    mytable _name
                WHERE   email = "johndoe@yahoo.com"
                UNION ALL
                SELECT  id
                FROM    mytable _name
                WHERE   address = '330 some lane'
                ) q
        GROUP BY 
                id
        HAVING
                COUNT(*) >= 3
        ) di, mytable t
WHERE   t.id = di.id

See the entry in my blog for performance details.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
1

I like the IF construct:

SELECT * FROM my_table
WHERE
(    IF(name    = 'john doe', 1, 0) +
     IF(phone   = '8183311234', 1, 0) +
     IF(email   = 'johndoe@yahoo.com', 1, 0) +
     IF(address = '330 some lane', 1, 0)
) >= 3
moo
  • 7,619
  • 9
  • 42
  • 40
0

Modifying Tomalak's query slightly so that it will use indexes if they are present. Although unless there is an index on each field, a full table scan will happen anyway.

SELECT
*, 
(
    IF(name="john doe", 1, 0) +
    IF(phone = "8183321234", 1, 0) +
    IF(email = "johndoe@yahoo.com", 1, 0) +
    IF(address = "330 some lane", 1, 0) 
) as matchCount
FROM my_table 
WHERE 
    name = "john doe" OR 
    phone = "8183321234" OR 
    email = "johndoe@yahoo.com" OR 
    address = "330 some lane"
HAVING matchCount >= 3
Paolo Bergantino
  • 480,997
  • 81
  • 517
  • 436
Brent Baisley
  • 962
  • 1
  • 6
  • 4
  • You need to indent code samples 4 spaces to make them display nicely. I made this change for you, hope you don't mind. :) – Paolo Bergantino Apr 17 '09 at 16:48
  • Mysql index merge performs worse than UNION ALL. See here: http://explainextended.com/2009/03/07/selecting-friends/ – Quassnoi Apr 17 '09 at 16:54
  • @Paolo how do you indent when you are typing code samples on here? – JD Isaacks Apr 17 '09 at 16:57
  • 1
    @John Isaacks: select a piece of code and press a 0100101 button above the edit field. The selected code will be indented with 4 spaces. – Quassnoi Apr 17 '09 at 17:02
  • 1
    @John Isaacks: For copy/paste code samples, it helps to replace all tabs with spaces in a text editor beforehand, since tabs and spaces combined tend to blow the layout. – Tomalak Apr 17 '09 at 17:10