눈가락★

https://leetcode.com/problems/find-customer-referee/

MySQL uses three-valued logic "TRUE", "FALSE" and "UNKNOWN".

Anything compared to NULL evaluates to the third value: UNKNOWN.

]That’s why MySQL provides the IS NULL and IS NOT NULL operators to specifically check for NULL.

select name
from Customer
where referee_id is null or referee_id != 2

https://leetcode.com/problems/employees-with-missing-information/

MySQL 은 full outer join 기능을 제공하지 않는다.

따라서 left join 과 right join 을 각각 진행한 뒤, union 으로 합치는 방법으로 full outer join 을 구현한다.

MySQL 에서 NULL은 특별한 값이기 때문에, 일반적인 비교 연산자로 비교할 수 없다.

무조건 is NULL 혹은 is not NULL 을 사용하여 비교해야 한다.

select distinct(employee_id)
from (
    select e.employee_id, e.name, s.salary
    from Employees e left join Salaries s 
    on e.employee_id = s.employee_id
    union all
    select s.employee_id, e.name, s.salary
    from Employees e right join Salaries s 
    on e.employee_id = s.employee_id
) a
where name is null or salary is null 
order by employee_id

< English (feedback by ChatGPT) >

Since MySQL natively doesn't support full outer join operation, we perform left join and right join each and union both of them to implement full outer join.

(Since MySQL does not natively support the FULL OUTER JOIN operation, we implement it by performing both a LEFT JOIN and a RIGHT JOIN, and then combining the results using UNION.)

Null is a special value in MySQL, so it's impossible to compare using comparison operators.

(In MySQL, NULL is treated as a special value, so it cannot be compared using standard comparison operators.)

We must use 'is Null' or 'is not Null' to compare.

(Instead, we must use IS NULL or IS NOT NULL for comparisons.)

https://leetcode.com/problems/reformat-department-table

행을 열로 바꾸는 pivot 을 사용한다.

MySQL 은 pivot 기능을 제공하지 않으므로, case when 문을 사용한다.

id 를 기준으로 생긴 그룹에서 month 가 특정 문자열이라는 조건에 맞는 revenue 를 추출한다.

조건에 맞는 revenue 는 하나 이상일 가능성이 있으므로, MAX를 사용하여 하나의 값만 추출하도록 한다.

(대개 MAX 혹은 SUM 이 사용된다)

SELECT id,
MAX(case when month = "Jan" then revenue else null end) as 'Jan_Revenue',
MAX(case when month = "Feb" then revenue else null end) as 'Feb_Revenue',
MAX(case when month = "Mar" then revenue else null end) as 'Mar_Revenue',
MAX(case when month = "Apr" then revenue else null end) as 'Apr_Revenue',
MAX(case when month = "May" then revenue else null end) as 'May_Revenue',
MAX(case when month = "Jun" then revenue else null end) as 'Jun_Revenue',
MAX(case when month = "Jul" then revenue else null end) as 'Jul_Revenue',
MAX(case when month = "Aug" then revenue else null end) as 'Aug_Revenue',
MAX(case when month = "Sep" then revenue else null end) as 'Sep_Revenue',
MAX(case when month = "Oct" then revenue else null end) as 'Oct_Revenue',
MAX(case when month = "Nov" then revenue else null end) as 'Nov_Revenue',
MAX(case when month = "Dec" then revenue else null end) as 'Dec_Revenue'
FROM Department
GROUP BY id;

< English (feedback by ChatGPT) >

We can use a pivot clause which transforms rows to columns.

(We can implement a pivot operation to transform rows into columns.)

MySQL doesn't support a pivot clause, so we use 'case when' statement.

(Since MySQL doesn't natively support pivot clauses, we use a CASE WHEN statement instead.)

we extract revenue under the condition that month is equals to a certain string from a group by id.

(We extract revenue values where the month column matches a specific string, grouped by id.)

Revenue under the condition could be more than 1, use MAX clause to get only one value.

(Because there may be more than one revenue entry that meets the condition, we use the MAX function to ensure only one value is returned.)

usually MAX and SUM are used for this

(In most cases, either MAX or SUM is used.)

https://leetcode.com/problems/swap-salary

Salary Table 의 sex column 내용을 조건에 따라 업데이트한다.

case 문을 set 에서도 사용 가능하다는 것을 알 수 있다.

UPDATE salary 
SET sex = (
    CASE
        WHEN sex = 'f' THEN 'm'
        WHEN sex = 'm' THEN 'f'
    END
)

< English (feedback by ChatGPT) >

Update sex column in salary table according to a condition.

(We update the sex column in the Salary table based on certain conditions.)

We can see case statement is available in set statement.

(This shows that a CASE statement can also be used within a SET clause.)