SQL Operators : Complete List

In this article, we will explore the complete list of SQL Operators with examples. Let’s get started.

Table of Contents

SQL operators are keywords used in SQL queries to perform operations on data. They are categorized into several types based on their functionality, including arithmetic operators, comparison operators, logical operators, and more. Operators allow you to filter, manipulate, and retrieve data from databases effectively.

Types of Operators in SQL

Arithmetic Operators

Arithmetic operators perform mathematical operations on numeric data types. They include:

Addition (+): Adds two values.
Subtraction (-): Subtracts the right-hand operand from the left-hand operand.
Multiplication (*): Multiplies two values.
Division (/): Divides the left-hand operand by the right-hand operand.
Modulus (%): Returns the remainder of dividing the left-hand operand by the right-hand operand.

Example

SELECT 5 + 3 AS AdditionResult;
-- Output: AdditionResult
--        8

SELECT 10 - 4 AS SubtractionResult;
-- Output: SubtractionResult
--        6

SELECT 6 * 7 AS MultiplicationResult;
-- Output: MultiplicationResult
--        42

SELECT 20 / 4 AS DivisionResult;
-- Output: DivisionResult
--        5

SELECT 17 % 5 AS ModulusResult;
-- Output: ModulusResult
--        2

Comparison Operators

Comparison operators are used to compare values in SQL. These operators are used in WHERE clauses to filter data based on specified conditions. They include:

Equal to (=): Compares if two values are equal.

SELECT * FROM products WHERE price = 50;
-- This query retrieves all products with a price of 50.

Not equal to (!= or <>): Checks if two values are not equal.

SELECT * FROM employees WHERE department_id != 3;
-- This query retrieves all employees whose department ID is not equal to 3.

Greater than (>): Compares if the left value is greater than the right value.

SELECT * FROM orders WHERE total_amount > 1000;
-- This query retrieves all orders with a total amount greater than 1000.

Less than (<): Compares if the left value is less than the right value.

SELECT * FROM students WHERE age < 18;
-- This query retrieves all students who are younger than 18 years old.

Greater than or equal to (>=): Checks if the left value is greater than or equal to the right value.

SELECT * FROM inventory WHERE quantity >= 10;
-- This query retrieves all items in the inventory with a quantity greater than or equal to 10.

Less than or equal to (<=): Compares if the left value is less than or equal to the right value.

SELECT * FROM scores WHERE score <= 80;
-- This query retrieves all scores that are less than or equal to 80.

Logical Operators

Logical operators allow you to combine multiple conditions in SQL queries. They include:

AND: Returns true if both conditions are true.

SELECT * FROM employees
WHERE department = 'Sales' AND salary > 50000;
-- This query retrieves all employees who work in the Sales department AND have a salary greater than 50,000.

OR: Returns true if at least one condition is true.

SELECT * FROM products
WHERE category = 'Electronics' OR category = 'Appliances';
-- This query retrieves all products that belong to either the Electronics category OR the Appliances category.

NOT: Reverses the result of a condition, making true conditions false and vice versa.

SELECT * FROM customers
WHERE NOT status = 'Inactive';
-- This query retrieves all customers whose status is NOT Inactive, meaning it fetches all active customers.

Concatenation Operator in SQL

In SQL, the concatenation operator is used to combine two or more strings into a single string. The concatenation operator varies between different database systems, but the most commonly used concatenation operator is the plus sign (+) or double pipe (||). Here’s how it works:

Using the Plus Sign (+) as Concatenation Operator (Microsoft SQL Server, MySQL, and others):

SELECT first_name + ' ' + last_name AS full_name
FROM employees;
-- This query concatenates the first_name and last_name columns, separating them with a space, and renames the result as "full_name."

Using the Double Pipe (||) as Concatenation Operator (Oracle Database):

SELECT first_name || ' ' || last_name AS full_name
FROM employees;
-- This query accomplishes the same concatenation as the previous example but uses the double pipe (||) operator.

In both examples, the concatenation operator combines the first_name and last_name columns with a space in between, creating a new column named full_name that holds the concatenated values.

Bitwise Operators

Bitwise operators work with binary representations of data. They include:

Bitwise AND (&): Performs a bitwise AND operation.

-- Bitwise AND example
SELECT 5 & 3 AS result;
-- Result: 1
-- Explanation: Binary representation of 5 is 101, and 3 is 011.
-- Bitwise AND of 101 and 011 results in 001, which is 1 in decimal.

Bitwise OR (|): Performs a bitwise OR operation.

-- Bitwise OR example
SELECT 5 | 3 AS result;
-- Result: 7
-- Explanation: Binary representation of 5 is 101, and 3 is 011.
-- Bitwise OR of 101 and 011 results in 111, which is 7 in decimal.

Bitwise XOR (^): Performs a bitwise XOR (exclusive OR) operation.

-- Bitwise XOR example
SELECT 5 ^ 3 AS result;
-- Result: 6
-- Explanation: Binary representation of 5 is 101, and 3 is 011.
-- Bitwise XOR of 101 and 011 results in 110, which is 6 in decimal.

Bitwise NOT (~): Inverts the bits of a binary number.

-- Bitwise NOT example
SELECT ~5 AS result;
-- Result: -6
-- Explanation: Binary representation of 5 is 0000 0000 0000 0000 0000 0000 0000 0101.
-- Bitwise NOT inverts all bits, resulting in 1111 1111 1111 1111 1111 1111 1111 1010, which is -6 in two's complement representation.

Left Shift (<<): Shifts the bits of a binary number to the left.

-- Bitwise Shift Left example
SELECT 5 << 2 AS result;
-- Result: 20
-- Explanation: Binary representation of 5 is 101.
-- Shifting left by 2 positions results in 10100, which is 20 in decimal.

Right Shift (>>): Shifts the bits of a binary number to the right.

-- Bitwise Shift Right example
SELECT 16 >> 2 AS result;
-- Result: 4
-- Explanation: Binary representation of 16 is 10000.
-- Shifting right by 2 positions results in 100, which is 4 in decimal.

Special Operators

Special operators perform unique functions in SQL:

IS NULL: Checks if a value is NULL.

-- Example: Find employees with no email addresses
SELECT employee_name
FROM employees
WHERE email IS NULL;

IS NOT NULL: Checks if a value is not NULL.

-- Example: Find employees with email addresses
SELECT employee_name
FROM employees
WHERE email IS NOT NULL;

BETWEEN: Tests if a value falls within a specified range.

-- Example: Find orders with order amounts between $100 and $500
SELECT order_id, order_amount
FROM orders
WHERE order_amount BETWEEN 100 AND 500;

LIKE: Compares a value to a pattern using wildcard characters.

-- Example: Find products with names containing 'Widget'
SELECT product_name
FROM products
WHERE product_name LIKE '%Widget%';

IN: Checks if a value matches any value in a list.

-- Example: Find orders from specific customers
SELECT order_id, customer_name
FROM orders
WHERE customer_name IN ('Customer A', 'Customer B', 'Customer C');

Conclusion: SQL Operators

In this comprehensive article, we’ve explored SQL operators, essential for various data operations. Let’s summarize the key points:

SQL Operators:

These keywords perform operations on data in SQL queries.

Types of Operators:

Arithmetic Operators: Perform math operations on numbers.
Comparison Operators: Compare values for filtering.
Logical Operators: Combine conditions.
Concatenation Operator: Merge strings.
Bitwise Operators: Work with binary data.
Special Operators: Handle unique tasks like NULL checks, range tests, and pattern matching.

Examples:

We provided illustrative examples for each operator.

SQL operators are vital for effective data manipulation and querying in relational databases, enabling mathematical calculations, value comparisons, string concatenation, and more. Understanding their usage is essential for proficient SQL query writing.

Related Articles :