Question 21416 in Mysql Question posted in
The official MySQL documentation can be found here.

What does this asterisk(*) mean in an sql query – Mysql

What does this asterisk(*) mean?

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

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search