Here's one. You have a custom Visualforce Page which poses the question:

Please enter a First Name, Last Name, and optionally an email address and we'll look for duplicates.

You could have 2 "static" queries

If (email != null)
   SELECT - FROM Contact WHERE Firstname LIKE :fname AND Lastname LIKE :lname AND Email LIKE :email
Else
  SELECT - FROM Contact WHERE Firstname LIKE :fname AND Lastname LIKE :lname  //no email search

Not too hard to maintain, in fact I'd probably write it that way, but you could use dynamic SOQL

basequery = 'SELECT - FROM Contact WHERE Firstname LIKE ' + firstname + ' AND Lastname LIKE ' + lname;
if(email != null) basequery += ' AND Email LIKE ' + email;

Of course you still have an if, but the core of the query is only in 1 spot. Let to maintain.

But here's where it can be very cool. What if you wanted to search BOTH Contacts AND Leads for duplicates?

Now you'd need 4 static queries...but you can have only 1 Dynamic Query

So you write a method...

List<SObject> findDuplicates(object, fname,lname,email)
{
basequery = 'SELECT - FROM ' + object + ' WHERE Firstname LIKE ' + fname+ ' AND Lastname LIKE ' + lname;
if(email != null) basequery += ' AND Email LIKE ' + email;
return Database.query(basequery);
}

And now you can just call the method

findDuplicates('Contact',fname,lname,email);
findDuplicates('Lead',fname,lname,email);

There are many other examples, but it allows you to write some generic queries that might work across any objects or maybe they work where the user is able to select which fields they want to filer on - instead of just email it could be a pick list of fields and the query can be 

basequery = 'SELECT - FROM ' + object + ' WHERE Firstname LIKE ' + fname+ ' AND Lastname LIKE ' + lname;
if(input != null) basequery += ' AND ' + Picklist_Value_Field_Name + ' LIKE ' + input;


Anyway, very powerful for making code generic and reusable across objects and across orgs where you might not know what custom fields exist.

Hope that helps some :)