This is, hands down, the best internet resource I’ve come across for learning more advanced SQL elements. I am learning so much.
For example, you have an index on last_name
, and you run this:
SELECT first_name, last_name, phone_number
FROM employees
WHERE UPPER(last_name) = UPPER('winand')
You’d think that the where clause would utilize the index, but it doesn’t.
Although there is an index on
LAST_NAME
, it is unusable—because the search is not onLAST_NAME
but onUPPER(LAST_NAME)
. From the database’s perspective, that’s something entirely different.
But!! You can create a computed field that contains that function, if you need it:
ALTER TABLE employees
ADD COLUMN last_name_up VARCHAR(255) AS (UPPER(last_name));
CREATE INDEX emp_up_name ON employees (last_name_up);