What does this asterisk(*) mean?

select 
    employee_id, 
    salary * (employee_id % 2) * (name not like "M%") as bonus 
from Employees order by employee_id;

CodePudding user response：

It's multiplication.

when data is

employee_id salary name
          1      2   MM
          2      3   AM
          3      4   SA

result will be

employee_id  bonus
          1      0 = (2 * 1 * 0)
          2      0 = (3 * 0 * 1)
          3      4 = (4 * 1 * 1)

here, (name not like "M%") is boolean. mysql-boolean

CodePudding user response：

The asterix is actually the simplest part of the expression. It is the multiplication operator.

• salary * (employee_id % 2) * (name not like "M%") multiplies the three numbers.
• (employee_id % 2) is the modulo (remainder) of employee_id divided by 2. This means it is 0 for even IDs and 1 for odd IDs.
• (name not like "M%") is a boolean expression (and the quotes should better be single quotes for standard-compliancy). We want a number here in order to apply our multiplication. MySQL converts booleans according to the rule TRUE = 1, FALSE = 0.

The result of the whole expression is hence:

• zero for all rows with an even ID and for all rows with name like 'M%'
• the original salary for all other rows

A more readable way to write the query would hence be:

select 
  employee_id,
  case when employee_id % 2 = 0 or name like 'M%' then 0 else salary end as bonus
from employees
order by employee_id;