MySQL Query Evaluation and Operator Precedence: A Comprehensive Cheat Sheet

SQL Query Evaluation Order

MySQL evaluates SQL queries based on the logical processing order of the different clauses:

1. FROM: Identify the data source, including JOIN operations.
2. WHERE: Filter rows based on the conditions.
3. GROUP BY: Group rows sharing a property for aggregate calculations.
4. HAVING: Filter groups based on aggregate conditions.
5. SELECT: Select and calculate output columns.
6. DISTINCT: Remove duplicate rows from the results.
7. ORDER BY: Sort the results.
8. LIMIT: Limit the number of rows returned.

Operator Precedence and Associativity

Operators in MySQL have precedence (priority) and associativity (the direction in which an expression is evaluated). Understanding this helps in avoiding unexpected results in queries.

Precedence List (from highest to lowest)

1. BINARY, COLLATE: Force binary comparison and collation.
2. !, -, ~: Unary negation, negative, and bitwise NOT.
3. ^: Bitwise XOR.
4. *, /, DIV, %, MOD: Multiplication, division, integer division, and modulus.
5. +, -: Addition and subtraction.
6. <<, >>: Bitwise left and right shifts.
7. &: Bitwise AND.
8. |: Bitwise OR.
9. =, <=>, <>, !=, <, <=, >, >=, IS, LIKE, REGEXP, IN: Comparison operators.
10. BETWEEN, CASE, WHEN, THEN, ELSE: Control flow operators.
11. NOT: Logical NOT.
12. AND: Logical AND.
13. XOR: Logical XOR.
14. OR: Logical OR.
15. =, :=: Assignment operators.

Associativity

• Left-to-Right Associativity: Most binary operators, such as arithmetic (+, -), comparison (=, <, >) and logical (AND, OR), are left-associative. This means they are evaluated from left to right.
• Right-to-Left Associativity: Unary operators (e.g., !, - in the context of negation), and assignment (:=) are right-associative. This means they are evaluated from right to left.

Scope of SQL Keywords

• SELECT Clause: Only fields, constants, and expressions visible within the SELECT statement are accessible.
• WHERE Clause: Only conditions on fields from the tables specified in the FROM clause are evaluated. Aliases defined in the SELECT clause are not accessible here.
• GROUP BY Clause: Works on fields and expressions visible in the SELECT statement. Aliases can be used in GROUP BY.
• HAVING Clause: Works similarly to WHERE but is used for filtering grouped records.
• ORDER BY Clause: Can use column names, positions, or aliases defined in the SELECT clause.
• LIMIT Clause: Restricts the number of rows returned after the query has been fully processed.

Common Query Patterns and Operator Priority

Basic Arithmetic Operations

SELECT 1 + 2 * 3; -- Output: 7 (Multiplication before addition)        

Logical Operations

SELECT 1 OR 0 AND 0; -- Output: 1 (AND before OR)        

WHERE Clause Conditions

SELECT * FROM users WHERE age > 25 AND (city = 'Patna' OR city = 'Ranchi'); -- AND before OR, hence parentheses are used to change the default precedence.        

Order of Execution in JOINS

SELECT * FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.age > 25;        

• FROM: users table is considered.
• JOIN: orders is joined to users.
• WHERE: Filter is applied on the joined result set.

Grouping and Aggregation

SELECT department, COUNT(*) 
FROM employees 
GROUP BY department 
HAVING COUNT(*) > 5;        

• GROUP BY: Aggregates data by department.
• HAVING: Filters groups with more than 5 employees.

Best Practices

• Use Parentheses: When in doubt, use parentheses to clarify operator precedence.
• Consistent Alias Naming: Aliases should be used consistently to avoid confusion, especially in complex queries with multiple joins or subqueries.
• Beware of Implicit Conversions: Ensure the types of operands are what you expect to avoid unintended behavior.

Further Reading and References

MySQL Documentation:

• Operator Precedence
• SELECT SyntaxSQL
• Query Order of Operations

Books:

• "SQL and Relational Theory" by C.J. Date – Offers a deep dive into SQL's logical foundations."Learning SQL" by Alan Beaulieu – A beginner-friendly guide to mastering SQL.

Online Resources:

• SQLZoo: Interactive SQL Tutorials
• LeetCode SQL Problems: Practice SQL problems with a focus on query logic and efficiency.

Summary

• Operator Precedence: Know the order of operations, especially with arithmetic and logical operators.
• Associativity: Most operators are left-associative; unary operators and assignments are right-associative.
• Query Evaluation Order: Remember the logical order of SQL clause execution: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.
• Scope: Understand the scope and visibility of different parts of your query.