A 38-year-old's MYSQL primer 02
Alright, first real step. Drew the sword, found the radish field — now let's pull some radishes.
0. These little slip-ups can happen.
If there's one thing that frustrates me as a non-major learning dev as a latecomer, it's that no one ever tells you the supposedly 'obvious' stuff. Before each post I'll jot down whatever I remember of those gotchas.
1) In a table, the vertical dimension is called a column and the horizontal one is called a record (row).
1. CASE
The CASE clause combines operators and functions like WHEN, ELSE, and END to return values in specific shapes.
1) Let me print the example table first.
select * from sample37;
2) Looking at the table, there are NULL values ( 'NULL' is not a data value but a marker for an unknown value). NULL values can't be added, subtracted, multiplied, or divided. That's why, when needed, you must be able to convert NULL into '0' or 'some desired value.' Let's practice this kind of transformation via CASE.
(1) First let's try it this way. To walk through the code: the content inside ( parentheses ) is the CASE clause. The 'a' after the ( parentheses ) is the column's name — called an alias.
select
( case when a is null
then 0 else a
end
) a
from sample37;
+ α ) If no alias is given, the code above produces this result.
+ α ) Of course, the code above can also be written on a single line as below. That's just my personal habit from using Oracle. Personally I alternate between the two styles for readability.
select (case when a is null then 0 else a end) a from sample37; 
(2) Looking at the examples above, with just one column it's hard to compare. Let's add a column this time.
select a,
( case when a is null
then 0 else a
end
) "a(null=0)"
from sample37;
+ α ) If you look closely, you'll notice a difference. It's the "a(null=0)" part. That's the alias part — in the earlier example 'a' didn't have " " around it. For plain strings you can use aliases without " ", but because a(null=0) contains operator syntax, we wrap it in " " to force it to be a string. You can also use single quotes instead: 'a(null=0)' .
2. COALESCE
In the earlier example I used case when a is null then 0 else a to convert NULL. In hindsight, reading it out loud might go something like this:
-> For the case ( CASE ), when ( WHEN ) the selected column ( a ) is NULL ( NULL ), then instead ( THEN ) put 0 ( 0 ), otherwise ( ELSE ) put the value of the selected column ( a ).
However, there's a function that does the same long-winded CASE query very succinctly: COALESCE.
Instead of writing a CASE, just put COALESCE(selected_column, replacement_value) after the SELECT:
select
a,
coalesce(a,0)
from sample37;
3. Using CASE in practice
So when do we use CASE? Let's practice with a scenario.
Let's imagine a situation. On Korean resident registration, men are 1 and women are 2. But for people who don't know this, it's easier to see 'man/woman' than 1/2. According to the book, converting specific data into a textual form like this is called 'decoding,' and turning it into a number is called 'encoding.'
There are two ways to decode. Either you use CASE with individual searched conditions, or you have CASE compare a pre-specified expression.
1) Searched CASE
select a AS '코드',
case
when a=1 then '남자'
when a=2 then '여자'
else '미지정'
end as '성별'
from sample37;
2) Simple CASE
select a AS '코드',
case a
when 1 then '남자'
when 2 then '여자'
else '미지정'
end as '성별'
from sample37;
+ α ) Bonus: CASE can also be used in WHERE and ORDER BY clauses, not just SELECT.
3) And in a WHEN clause you can also transform NULL.
select a AS '코드',
case
when a=1 then '남자'
when a=2 then '여자'
when a is null then '데이터 없음 '
else '미지정'
end as '성별'
from sample37;
+ α ) Also, Oracle has a dedicated NVL function for transforming NULL — but let's try to stick with standard SQL here.
4. DEFAULT
When you inspect a column layout with DESC (as we did in primer 01), you'll often see a Default column. That shows the value assigned when a row is inserted without an explicit value — NULL if not set. As mentioned above, NULL values often make calculations awkward. Sure, every time NULL shows up you can transform it via CASE or COALESCE as in the earlier examples, but if you use DEFAULT you can avoid getting NULL in the first place and store a pre-specified value (like 0).
1) Let me check the table's column layout with DESC.
desc sample411;
+ α ) Bonus: how do you set the DEFAULT value? You can set it when creating the table. You can also modify or add it via the ALTER command. As for the how... I searched and this site isn't bad.
2) First let me add a row with explicit values for all columns.
insert into sample411(no, d) values(1,1);
select * from sample411;
3) Now let me set a value explicitly for one column, and use the DEFAULT function for the others.
insert into sample411(no, d) values(2,default);select * from sample411;
And that wraps up the second practice session too~ the end ;D
