faster way to do this select query

Question

Say Employee has three columns FirstName, LastName and ID.

The query should first search for a name in firstname, only if its not found search for last name.

so select *from Employee where FirstName = 'test%' or lastname='test'%'. wont' work.

The query below will work.

select FirstName, LastName, ID
from EMPLOYEE
WHERE 
    LastName = 'Test%'
    AND
    (COUNT(SELECT FirstName, LastName, ID FROM EMPLOYEE WHERE FirstName = 'Test%')=0)
    OR
    SELECT FirstName, LastName, ID FROM EMPLOYEE WHERE FirstName = 'Test%'

I need to map this back to NHibernate, is there a faster efficient way of doing this instead of making two database calls?

Answer 1

Select * 
From Employee 
Where FirstName Like @Text + '%'

Union

Select * 
From Employee 
Where LastName Like @Text + '%';

Answer 2

FROM Employee
WHERE (FirstName LIKE 'Test%' AND LastName NOT LIKE 'Test%')
OR (LastName LIKE 'Test%' AND firstName NOT LIKE 'Test%')

Granted you don't care what order they come back in. If the records must come back with the records that the first name matches, followed by the names where the last name match, then this won't work.

Answer 3

Give this one a try:

SELECT FirstName, LastName, ID FROM EMPLOYEE WHERE FirstName = 'Test%'
OR (FirstName <> 'Test%' AND LastName = 'Test%')

Answer 4

"Premature optimization is the root of all evil".

Why would you care of how the search is done as the optimizer sees it, instead of declaring what you want?
It all boils down to a boolean truth table: when FirstName matches, you want the record (whatever is in LastName, match or nomatch) and if FirstName does not match, you want the record when LastName matches:

F match,   L match   => yes
F match,   L nomatch => yes
F nomatch, L match   => yes
F nomatch, L nomatch => no

That is exactly the OR condition: (FirstName matching) OR (LastName matching); the only discarded records are when both FirstName and LastName do not match.

The boolean optimization will ensure that the 2nd condition is not even evaluated when the 1st one is true.

So your query is:

SELECT FirstName, LastName, ID
FROM Employee
WHERE (FirstName LIKE 'Test%')
   OR (LastName LIKE 'Test%')

UPDATE: I may have misunderstood the goal if it is indeed not to return any LastName match if records were found with only the FirstName...
Anyway, the stance on premature optimization is still valid...
You need somehow a 2 pass query as you cannot tell if LastName is to be considered until you're sure you don't have any match on FirstName. But with proper indexes and statistics, the optimizer will make it very efficient.

The query:

SELECT FirstName, LastName, ID
FROM   Employee 
WHERE  (FirstName LIKE 'Test%')
    OR (LastName LIKE 'Test%'
        AND (SELECT Count(ID)
             FROM   Employee
             WHERE  FirstName LIKE 'Test%') = 0)

is only marginally more expensive than the previous.

Answer 5

I don't think any of these queries answers the question. My understanding is that a search on 'John%' should return employees with last name John, Johnson, etc. ONLY if there were no employees with first name John, Johnny, etc. All the queries show will return both John Adams and Lyndon Johnson if the table contains both, but only John Adams should appear, because last names should be matched ONLY if there are no first names that matched.

Here's a proposal using SQL Server syntax. It should be possible to write this in other dialects of SQL:

select top (1) with ties
  FirstName, LastName, ID
from (
  select
    0 as SearchLastNames,
    FirstName, LastName, ID
  from EMPLOYEE
  where FirstName like 'Test%'
  union all
  select
    1 as SearchLastNames,
    FirstName, LastName, ID
  from EMPLOYEE
  where LastName like 'Test%'
) as T
order by SearchLastNames;

If there are any matching first names, the smallest SearchLastNames value will be 0, and the TOP (1) with ties .. order by SearchLastNames will return information only for the first name matches (where SearchLastNames is 0).

If there are no matching first names, the only SearchLastNames value will be 1. In that case, TOP will return information for all last name matches (where SearchLastNames is 1), if there are any.

A more clumsy, but more portable solution is this:

  select
    FirstName, LastName, ID
  from EMPLOYEE
  where FirstName like 'Test%'
  union all
  select
    FirstName, LastName, ID
  from EMPLOYEE
  where LastName like 'Test%'
  and not exists (
    select
    FirstName, LastName, ID
    from EMPLOYEE
    where FirstName like 'Test%'
  );