What is PEMDAS and why is it so important to know about it?

When I first started working with SQL, I thought math operations would work just like they did on a calculator. But one day, I ran a query that completely threw me off. I was expecting one result, but MySQL have me something totally different. That’s when I realized that I had ignored PEMDAS.

Table of contents

1. PEMDAS in SQL (Yes, It’s a Thing!)
2. The Moment It Clicked for Me
3. Tricky SQL Differences to Watch Out For
4. Advice for Anyone Learning This

PEMDAS in SQL (Yes, It’s a Thing!)

If you’ve ever taken a maths course, you might remember PEMDAS, but as English is not my first language, I’ve never heard of PEMDAS as a word before. I knew the concept, of course, but not the abbreviation:

• Parentheses first
• Exponents next
• Multiplication and Division (left to right)
• Addition and Subtraction (left to right)

SQL follows the same logic, meaning MySQL, PostgreSQL, SQL Server, and other databases all process arithmetic expressions in a predictable order. But there’s a catch: databases might handle some operations slightly differently!

The Moment It Clicked for Me

I remember writing a simple SQL query like this:

SELECT 10 + 5 * 2;

I expected 30 (because, in my head, 10 + 5 happened first). But MySQL gave me 20. Why? Because multiplication comes before addition.

Here’s the right way to force addition first:

SELECT (10 + 5) * 2; 
Output: 30

That’s when I realized: If you’re not sure, always use parentheses!

Tricky SQL Differences to Watch Out For

Not all databases handle things exactly the same way:

• Exponents (^) work differently
  • MySQL and SQL Server use POWER(base, exponent).
  • PostgreSQL actually lets you use ^ (e.g., SELECT 2^3; → 8).
• Bitwise operations (&, |, ^) can have different precedence
  • In some databases, these can affect the order of calculations in ways you might not expect.
• String concatenation (||) might not behave like math
  • In PostgreSQL, SELECT 'Hello' || 'World'; gives "HelloWorld", not a math error!

Advice for Anyone Learning PEMDAS

If you’re just starting out with SQL, here’s what I wish someone had told me:

1. Always use parentheses if you’re unsure about the order of operations. It makes your queries more readable and avoids surprises.
2. Test small expressions in a query editor before using them in a big query.
3. Look up the specific rules for your SQL database, small differences can trip you up.
4. Remember that SQL is not a calculator! Sometimes functions like POWER() behave differently than you’d expect.

Once I started keeping these in mind, my queries became much easier to debug. Hopefully, this helps you avoid the same confusion I had! Happy querying! 😊