SELECT first_name || ' ' || middle_name || ' ' || last_name AS full_name FROM employees;

SELECT CONCAT(first_name, ' ', COALESCE(middle_name, ''), ' ', last_name) AS full_name FROM employees;

NULL concatenation returns NULL for entire result. If middle_name is NULL, full_name becomes NULL. Use COALESCE to replace NULL with empty string, or use CONCAT() which is NULL-safe in MySQL.

Never use || with multiple columns. Always COALESCE each nullable field

Critical

Returns NULL for entire full_name if ANY part is NULL

SELECT * FROM customers WHERE UPPER(email) = 'JOHN@EXAMPLE.COM';

CREATE INDEX idx_email_normalized ON customers(email_normalized); SELECT * FROM customers WHERE email_normalized = 'john@example.com';

UPPER() function on every row prevents index use. Database must compute UPPER(email) for ALL rows before filtering - full table scan. Solution: Normalize at insert time with computed column or store normalized version.

Never wrap indexed columns in functions in WHERE. Normalize once at insert

Critical

Full table scan (all 10M rows checked), 45 seconds execution time

SELECT * FROM products WHERE name LIKE '%laptop%';

SELECT * FROM products WHERE name LIKE 'laptop%'; -- OR use full-text search

Leading % prevents index usage - database cannot seek to prefix, must scan all rows. LIKE 'laptop%' CAN use index (prefix search). If you MUST search middle patterns, use full-text search.

LIKE 'prefix%' = OK (index-friendly). LIKE '%middle%' = Bad (full scan)

Critical

Index on name exists but CANNOT be used (leading % is problem)