SQL Operators

Keywords to carry out special actions.

Introduction

SQL operators are reserved keywords or special characters that are used in SQL statements to carry out different actions on data that is kept in relational databases. They support data manipulation, record filtering, calculation, and value comparison. SELECT, UPDATE, DELETE, and WHERE clauses in SQL queries are examples of queries that use SQL operators to further filter the data being altered or retrieved.

Types of SQL Operators

SQL operators in Microsoft SQL Server can be broadly categorized into the following types:

  • Arithmetic Operators
  • Comparison Operators
  • Logical Operators
  • String Operators
  • Bitwise Operators
  • Set Operators

We will explore each category with examples.

Arithmetic Operators

These operators are used to perform mathematical operations on numeric data types.

  • Addition (+): Adds two numbers. Example, SELECT 10 + 5; returns 15.
  • Subtraction (-): Subtracts two numbers. Example, SELECT 10 - 5; returns 5.
  • Multiplication (*): Multiplies two numbers. Example, SELECT 10 * 5; returns 50.
  • Division (/): Divides the first number by the second. Example, SELECT 10/5; returns 2.
  • Modulus (%): Returns the remainder of the division of two numbers. Example, SELECT 10 % 3; returns 1.

Comparison Operators

Comparison operators are used to compare two values. The output of a comparison is either true, false, or unknown (NULL).

  • Equal to (=): Checks if two values are equal.
SELECT * FROM Customers WHERE first_name = 'Alex';
  • Not equal to (<>): Checks if two values are not equal.
SELECT * FROM Customers WHERE customer_status <> 'Active';
  • Greater than (>): Checks if the value on the left is greater than the value on the right.
SELECT * FROM Customers WHERE customer_id > 522;
  • Less than (<): Checks if the value on the left is less than the value on the right.
SELECT * FROM Customers WHERE customer_id < 50000;

Logical Operators

Logical operators are used to combine multiple conditions in a WHERE clause.

  • AND: Combines two conditions and returns true if both are true.
SELECT * FROM Customers WHERE customer_id > 500 AND first_name = 'Alex'
  • OR: Combines two conditions and returns true if either is true.
SELECT * FROM Customers WHERE customer_id > 500 OR first_name = 'Alex'
  • NOT: Reverses the result of a condition.
SELECT * FROM Customers WHERE NOT first_name = 'Alex'

String Operators

String operators are used to concatenate strings or perform operations on strings.

  • Concatenation (+): Joins two strings together.
SELECT first_name + ' ' + last_name from Customers;

Bitwise Operators

Bitwise operators are used to perform operations on binary data at the bit level.

  • Bitwise AND (&): Performs a bitwise AND operation between two numbers. Example, SELECT 5 & 3; returns 1 (since 0101 & 0011 is 0001 in binary).
  • Bitwise OR (|): Performs a bitwise OR operation between two numbers. Example, SELECT 5 | 3; returns 7 (since 0101 | 0011 is 0111 in binary).
  • Bitwise XOR (^): Performs a bitwise XOR (exclusive OR) operation between two numbers. Example, SELECT 5 ^ 3; returns 6 (since 0101 ^ 0011 is 0110 in binary).
  • Bitwise NOT (~): Performs a bitwise NOT operation, flipping all bits. Example, SELECT ~5; returns -6 (inverts the bits of 5).

Set Operators

Set operators are used to combine the results of two or more SELECT queries.

  • UNION: Combines the results of two queries and returns distinct values.
SELECT first_name FROM Customers 
UNION 
SELECT first_name FROM Retailer
  • UNION ALL: Combines the results of two queries and returns all values, including duplicates.
SELECT  first_name, last_name FROM Customers 
UNION ALL
SELECT first_name, last_name FROM Retailer
  • INTERSECT: Returns only the common rows from two SELECT queries.
SELECT  first_name, last_name FROM Customers 
INTERSECT
SELECT first_name, last_name FROM Retailer
  • EXCEPT: Returns rows from the first SELECT query that are not in the second.
SELECT  first_name, last_name FROM Customers 
EXCEPT
SELECT first_name, last_name FROM Retailer

Note: We will discuss more about set operators in SET operations

Other Operators

  • IN: Checks if a value matches any value in a list or subquery.
SELECT * FROM Customers WHERE first_name IN ('Alex', 'Peter')

Caution: IN operator can give wrong result is you have 'null' value in the list or subquery.

  • BETWEEN: Checks if a value is within a range.
SELECT * FROM Orders WHERE total_amount BETWEEN 500 AND 1000

Note: Between can be used for date ranges and text value also.

SELECT * FROM Customers
WHERE date_created BETWEEN '2024-07-01' AND '2024-08-31';

SELECT * FROM Customers WHERE first_name BETWEEN 'Alex' AND 'Ryan';

This SQL statement selects all customers with a first_name alphabeticaly between Alex and Ryan.

  • LIKE: Searches for a specified pattern in a column.
SELECT * FROM Customers WHERE first_name LIKE 'J%';

This SQL statement returns first_names starting with "J".

The LIKE operator is frequently combined with one of the following two wildcards:

  • % The percent symbol (%) can denote one, zero, or more characters.
  • _: the underscore symbol _ represents a single character.

you can find more about wildcards in reources.

  • IS NULL / IS NOT NULL: Checks if a value is null or not.
SELECT * FROM Customers WHERE province IS NULL;

This returns Customers who do not have a province.

SELECT * FROM Orders WHERE customer_id IS NOT NULL;

This returns Orders where customer_id is not null.

Conclusion

Operators in MS SQL play a crucial role in performing various operations within a database. From arithmetic operators used for mathematical calculations, comparison operators for evaluating conditions, to logical operators for combining multiple conditions, each operator type serves a unique function to manipulate and query data effectively.

Asked in Interview

  • Explain the use of the BETWEEN operator in SQL.
  • What is the IN operator, and when would you use it?
  • How does the LIKE operator work in SQL?
  • how do you handle NULL values?
  • What is the IS NULL and IS NOT NULL operator in SQL?
  • What are bitwise operators, and how are they used in SQL?
  • What is the difference between UNION and UNION ALL operators?