Arithmetic Operations - SQL Server Vs MySQL

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

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan