optional search parameters in sql query and rows with null values

Question

Ok here is my problem :

Before i start the description, let me to tell you that I have googled up a lot and I am posting this question for a good optimal solution :)

i am building a rest service on WCF to get userProfiles... the user can filter userProfiles by giving something like userProfiles?location=London

now i have the following method

GetUserProfiles(string firstname, string lastname, string age, string location)

the sql query string i built is: select firstname, lastname, .... from profiles where (firstName like '%{firstname}%') AND (lastName like '%{lastName}%') ....and so on with all variables being replaced by string formatter.

Problem with this is that it filters any row having firstname, lastname, age or location having a null value....

doing something like (firstName like '%{firstName}%' OR firstName IS NULL) would be tedious and the statement would become unmaintanable! (in this example there are only 4 arguments, but in my actual method there are 10)

What would be the best solution for this?....How is this situation usually handled?

Database used : MySql

Answer 1

It's a fundamental property of NULL that when you compare it to anything—including NULL—it returns false, which is why what you're doing doesn't work.

So the first question to answer is: why do you want a row with a NULL lastname be returned when they enter a lastname of "smith"? Possibly you mean that either the firstname or the lastname needs to match to be returned, in which case you're not running the correct query. The most naive solution is:

SELECT firstname, lastname, ....
FROM profiles
WHERE IFNULL(firstName LIKE'%{firstname}%'
OR lastName LIKE '%{lastName}%'

Now this will work for several hundreds and possibly several thousand rows but won't scale beyond that for several reasons:

1. ORs are typically woeful in terms of performance. Avoid them where possible. If you look at database applications written by experienced programmers you probably won't find a single OR condition (except from those who proselytize the virtues of OR on the back of having once written a guestbook application that has 3 users and 2 hits a month);
2. Doing a LIKE with a % at the front will mean no indexes will be sued.

There are several solutions to (2). Probably the easiest in MySQL is to use full text searching on MyISAM tables. It won't find matches like "Johannes" if you type in "han" but it is generally sufficient.

Often you deal with OR conditions by using either UNION or (preferably) UNION ALL on multiple queries. For example:

SELECT firstname, lastname, ....
FROM profiles
WHERE firstName LIKE'%{firstname}%'
AND lastName LIKE '%{lastName}%'
UNION ALL
SELECT firstname, lastname, ....
FROM profiles
WHERE firstName LIKE'%{firstname}%'
AND lastname IS NULL
UNION ALL
SELECT firstname, lastname, ....
FROM profiles
WHERE firstName IS NULL
AND lastName LIKE '%{lastName}%'
UNION ALL
SELECT firstname, lastname, ....
FROM profiles
WHERE firstName IS NULL
AND lastName IS NULL

Looks big and ugly but UNION ALL scales extremely well (ignoring the % at the start of the LIKE criteria). It's basically concatenating (in this case) four queries. A UNION will do an implicit DISTINCT on the result rows but we know there'll be no overlap here because we're varying checking for NULL.

Another possibility is not to treat NULL as something you want to search for. This is a far better and more intuitive solution (imho). If someone types in a lastname of "Smith" do you really want rows with a NULL lastname showing up?

Or do you want them to show up because you might've got a match on the first name? If so, you want a slightly different query. For example:

SELECT firstname, lastname, ....
FROM profiles
WHERE id IN (
  SELECT id 
  FROM profiles
  WHERE firstName LIKE'%{firstname}%'
  UNION ALL
  SELECT id 
  FROM profiles
  WHERE lastName LIKE '%{lastName}%'
)

Answer 2

You can use COALESCE(value,...)

coalesce(firstName, '') like '%{firstname}%'