Operators in SQL queries: from basics to advanced

Operators in SQL Queries: From Basics to Advanced

Introduction to Operators in SQL

Operators are symbols or keywords used in SQL to perform specific operations on data. They are essential for filtering, sorting, and manipulating data within queries. Operators can be categorized into several types, including arithmetic, comparison, logical, and set operators. Understanding these operators is crucial for writing efficient and accurate SQL statements.

Basic Operators

Arithmetic Operators

Arithmetic operators perform mathematical operations such as addition, subtraction, multiplication, and division.

  • +: Adds two values (e.g., SELECT 10 + 5; returns 15).
  • -: Subtracts one value from another (e.g., SELECT 10 - 5; returns 5).
  • *****: Multiplies two values (e.g., SELECT 10 * 5; returns 50).
  • /: Divides two values (e.g., SELECT 10 / 5; returns 2).

Comparison Operators

Comparison operators compare two values and return a boolean result (TRUE or FALSE).

  • =: Checks for equality (e.g., SELECT * FROM table WHERE age = 25;).
  • >: Checks if the left value is greater than the right (e.g., SELECT * FROM table WHERE salary > 5000;).
  • <: Checks if the left value is less than the right (e.g., SELECT * FROM table WHERE price < 100;).
  • >=: Checks if the left value is greater than or equal to the right (e.g., SELECT * FROM table WHERE price >= 50;).
  • <=: Checks if the left value is less than or equal to the right (e.g., SELECT * FROM table WHERE quantity <= 10;).

Logical Operators

Logical operators combine conditional expressions and return a boolean result.

  • AND: Returns TRUE only if both conditions are true (e.g., SELECT * FROM table WHERE age > 25 AND salary > 5000;).
  • OR: Returns TRUE if at least one condition is true (e.g., SELECT * FROM table WHERE age > 25 OR salary > 5000;).
  • NOT: Negates a condition (e.g., SELECT * FROM table WHERE NOT (age > 25);).

Set Operators

Set operators combine results from multiple queries or tables.

  • UNION: Combines results from two queries (e.g., SELECT name FROM table1 UNION SELECT name FROM table2).
  • INTERSECT: Returns results that are present in both queries (e.g., SELECT name FROM table1 INTERSECT SELECT name FROM table2).
  • EXCEPT: Returns results from the first query that are not in the second (e.g., SELECT name FROM table1 EXCEPT SELECT name FROM table2).

Advanced Operators

Bitwise Operators

Bitwise operators work on binary numbers and are commonly used in low-level data manipulation.

  • AND: Returns 1 if both bits are 1 (e.g., SELECT 5 AND 3; returns 1).
  • OR: Returns 1 if at least one bit is 1 (e.g., SELECT 5 OR 3; returns 5).
  • NOT: Inverts the bits (e.g., SELECT NOT 5; returns 0).
  • XOR: Returns 1 if exactly one bit is 1 (e.g., SELECT 5 XOR 3; returns 6).

Function-Based Operators

Operators can also be based on functions, such as date, string, or aggregate functions.

  • DATEADD: Adds a specified interval to a date (e.g., SELECT DATEADD(day, 5, '2023-01-01'); returns '2023-01-06').
  • COUNT: Counts the number of rows (e.g., SELECT COUNT(*) FROM table;).
  • SUM: Calculates the sum of a column (e.g., SELECT SUM(salary) FROM table;).
  • AVG: Calculates the average of a column (e.g., SELECT AVG(salary) FROM table;).

Conditional Operators

Conditional operators allow for complex logic, such as CASE statements.

  • CASE: Evaluates conditions and returns a specified value (e.g., SELECT CASE WHEN salary > 5000 THEN 'High' ELSE 'Low' END FROM table;).

Special Considerations

NULL Handling

Operators can behave differently when dealing with NULL values.

  • IS NULL: Checks if a value is NULL (e.g., SELECT * FROM table WHERE column IS NULL;).
  • IS NOT NULL: Checks if a value is not NULL (e.g., SELECT * FROM table WHERE column IS NOT NULL;).
  • Comparison with NULL: NULL = NULL returns NULL, and NULL + 5 results in NULL.

String Operators

String operators are used to manipulate text data.

  • CONCAT: Combines two strings (e.g., SELECT CONCAT('Hello', ' ', 'World') FROM table; returns 'Hello World').
  • LIKE: Matches patterns (e.g., SELECT * FROM table WHERE name LIKE 'A%';).
  • IN: Checks if a value matches any in a list (e.g., SELECT * FROM table WHERE name IN ('Alice', 'Bob');).

Conclusion

Operators are fundamental to SQL queries, enabling users to perform complex operations on data. From basic arithmetic and comparison operators to advanced bitwise and function-based operators, understanding these tools is essential for writing efficient and accurate queries. Mastery of operators allows for precise data manipulation, filtering, and analysis, making them a cornerstone of SQL proficiency.