CODEWITHSUNDEEP
sqlwhere-clausecase-insensitive
SBT
SBT

Reputation: 5

What is more efficient SQL statement than the one shown

What problems does the statement shown here present? What could be changed that would allow a more efficient statement than this one? I was asked in an interview.

SELECT
    DATE, location, USER_ID
FROM
    customers
WHERE
    UPPER(name) = 'SATISH';

Upvotes: 0

Views: 62

Answers (2)

Turo
Turo

Reputation: 4924

Since you use a function on the column, an index on name would probably not been used. Many DB-System support case-insensitive indexes/search anyway, so the upper is not needed.

In other cases it could be useful to store the name in uppercase(maybe an extra column). Then the upper on the search-value is needed, too.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270463

This is your query:

SELECT DATE, location, USER_ID
FROM customers
WHERE UPPER(name) = 'SATISH';

Making this query more efficient is basically about using indexes.

First, many databases are by default case-insensitive. So this query could be equivalent to:

WHERE name = 'SATISH'

This version, however, can take advantage of an index on users(name). Even a case-insensitive collation will probably not use an index with the UPPER() function.

If your database is case-sensitive, three options come to mind:

  1. Store the name as upper case.
  2. Use a function-based index.
  3. Use a generated column instead of the base column

Not all databases support all three methods.

You can enforce the first with a check constraint:

check chk_user_name_uppercase (name = upper(name));

You can implement the second in some databases as:

create index idx_user_uname on user(upper(name));

You can implement the third as:

alter table user add name_upper as (upper(name));

create index idx_user_name_upper on user(name_upper);

Then the where clause would look like:

where name_upper = 'SATISH'

Upvotes: 2

Related Questions