Arithmetic operations done in SQL Server and MySQL may not give the same result. Integer divisions always result in an integer in SQL Server whereas in MySQL, it results to a decimal. Let’s see this with examples.
SQL Server
Run the following code
SELECT 5/2
The result is 2 and not 2.5 This is because both 5 and 2 are Integers and the end result is also converted to the data type of integer. So the actual value 2.5 becomes 2 when implicitly converted to an integer datatype.
SELECT 1/0
This results to the error Divide by zero error because any number divided by zero is infinity.
SELECT 'a'/10
The above throws an error "Conversion failed when converting the varchar value 'a' to data type int."
Now let’s observe arithmetic operations in MySQL, given the same set of data.
MySQL
Run the following code
SELECT 5/2
The result is 2.5 Although both 5 and 2 are of integer datatypes, MySQL results to decimal datatype during the division
SELECT 1/0
MySQL returns NULL for the above select statement. It won't give Divide by Zero error
SELECT 'a'/10
The above returns 0 and not an error.
Hope these tips were useful and you should keep them in mind when doing arithmetic calculations in MySQL
No comments:
Post a Comment