SQL Operators

Overview

SQL operators are special symbols or keywords used to perform operations on one or more values or expressions. They are essential in constructing SQL queries for data manipulation and retrieval.

Types of SQL Operators

• Arithmetic Operators: Used to perform mathematical operations.
• Comparison Operators: Used to compare two values.
• Logical Operators: Used to combine multiple conditions.
• Bitwise Operators: Used to perform bit-level operations.
• Assignment Operators: Used to assign values to variables.
• String Operators: Used to concatenate strings.

1. Arithmetic Operators

Arithmetic operators are used to perform mathematical calculations.

• Addition (+): Adds two numbers.
• Subtraction (-): Subtracts one number from another.
• Multiplication (*): Multiplies two numbers.
• Division (/): Divides one number by another.
• Modulus (%): Returns the remainder of a division operation.

Example:

SELECT (10 + 5) AS Sum, (10 - 5) AS Difference, (10 * 5) AS Product, (10 / 5) AS Quotient, (10 % 3) AS Remainder;

2. Comparison Operators

Comparison operators are used to compare two values.

• Equal (=): Checks if two values are equal.
• Not Equal (!= or <>): Checks if two values are not equal.
• Greater Than (>): Checks if the left value is greater than the right value.
• Less Than (<): Checks if the left value is less than the right value.
• Greater Than or Equal To (>=): Checks if the left value is greater than or equal to the right value.
• Less Than or Equal To (<=): Checks if the left value is less than or equal to the right value.

Example:

SELECT * FROM Employees WHERE Age >= 30 AND Salary > 50000;

3. Logical Operators

Logical operators are used to combine multiple conditions.

• AND: Returns true if both conditions are true.
• OR: Returns true if at least one condition is true.
• NOT: Reverses the truth value of a condition.

Example:

SELECT * FROM Employees WHERE Department = 'Sales' AND Age < 40;

4. Bitwise Operators

Bitwise operators perform operations on binary numbers at the bit level.

• AND (&&): Performs a bitwise AND operation.
• OR (||): Performs a bitwise OR operation.
• XOR (^): Performs a bitwise XOR operation.
• NOT (~): Performs a bitwise NOT operation.

Example:

SELECT (5 & 3) AS BitwiseAND, (5 | 3) AS BitwiseOR;

5. Assignment Operators

Assignment operators are used to assign values to variables.

• =: Assigns a value to a variable.
• +=: Adds a value to a variable.
• -=: Subtracts a value from a variable.
• *=: Multiplies a variable by a value.
• /=: Divides a variable by a value.

Example:

SET @total = 100; 
SET @total += 50; 
SELECT @total AS NewTotal;

6. String Operators

String operators are used to concatenate strings.

• CONCAT: Concatenates two or more strings.

Example:

SELECT CONCAT('Hello', ' ', 'World') AS Greeting;

Conclusion

SQL operators are fundamental for constructing queries to manipulate and retrieve data from databases. Understanding the various types of operators and how to use them is crucial for effective SQL programming.