SQL Antipatterns: Avoiding the Pitfalls of Database Programming/

...

/

Synopsis: Fear of the Unknown

Synopsis: Fear of the Unknown

Let’s get introduced to the antipattern that arises because of considering the NULL value as an ordinary value.

We'll cover the following...

In our example bugs database, the Accounts table has columns first_name and last_name. We can use an expression to format the user’s full name as a single column using the string concatenation operator:

Press + to interact
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM Accounts;

Let’s suppose that your boss asks you to modify the database to add the user’s middle initial to the table (perhaps two users have the same first and last name, and the middle initial is an excellent way to avoid confusion). This seems to be a pretty simple alteration and you manually add the middle initials for a few users.

Press + to interact
ALTER TABLE Accounts ADD COLUMN middle_initial CHAR(2);
UPDATE Accounts SET middle_initial = 'J.' WHERE account_id = 123;
UPDATE Accounts SET middle_initial = 'C.' WHERE account_id = 321;
SELECT CONCAT(first_name, ' ', middle_initial,' ', last_name) AS full_name
  FROM Accounts;

Suddenly, the application ceases to show any names. Then, on a second look, you notice that this ...