SQL Operators

Introduction

In this chapter, we will explore SQL operators, which are used to perform operations on data in a database. Operators are essential in SQL as they allow you to filter results, perform calculations, and manipulate data. This chapter will cover the different types of SQL operators, including arithmetic, comparison, logical, and more, with examples to help you understand how to use them.

Types of SQL Operators

1. Arithmetic Operators
2. Comparison Operators
3. Logical Operators
4. Bitwise Operators
5. Compound Operators
6. Other Operators

1. Arithmetic Operators

Arithmetic operators are used to perform mathematical operations on numeric data.

Table of Arithmetic Operators

Operator	Description	Example	Result
+	Addition	5 + 3	8
-	Subtraction	5 - 3	2
*	Multiplication	5 * 3	15
/	Division	5 / 2	2.5
%	Modulus	5 % 2	1

Example

SELECT price, price * 0.1 AS tax
FROM products;

2. Comparison Operators

Comparison operators are used to compare two values and return a boolean value (TRUE or FALSE).

Table of Comparison Operators

Operator	Description	Example	Result
=	Equal to	5 = 3	FALSE
!=	Not equal to	5 != 3	TRUE
>	Greater than	5 > 3	TRUE
<	Less than	5 < 3	FALSE
>=	Greater than or equal to	5 >= 3	TRUE
<=	Less than or equal to	5 <= 3	FALSE

Example

SELECT product_name, price
FROM products
WHERE price > 100;

3. Logical Operators

Logical operators are used to combine multiple conditions in SQL statements.

Table of Logical Operators

Operator	Description	Example	Result
AND	Logical AND	TRUE AND FALSE	FALSE
OR	Logical OR	TRUE OR FALSE	TRUE
NOT	Logical NOT	NOT TRUE	FALSE

Example

SELECT product_name, price
FROM products
WHERE price > 100 AND category = 'Electronics';

4. Bitwise Operators

Bitwise operators are used to perform bit-level operations on binary numbers.

Table of Bitwise Operators

Operator	Description	Example	Result
&	Bitwise AND	5 & 3	1
|	Bitwise OR	5 | 3	7
^	Bitwise XOR	5 ^ 3	6
~	Bitwise NOT	~5	-6
<<	Left Shift	5 << 1	10
>>	Right Shift	5 >> 1	2

Example

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

5. Compound Operators

Compound operators combine arithmetic operations with assignment.

Table of Compound Operators

Operator	Description	Example	Equivalent To
+=	Add and assign	a += 5	a = a + 5
-=	Subtract and assign	a -= 5	a = a - 5
*=	Multiply and assign	a *= 5	a = a * 5
/=	Divide and assign	a /= 5	a = a / 5
%=	Modulus and assign	a %= 5	a = a % 5

Example

UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 5;

6. Other Operators

LIKE Operator

The LIKE operator is used for pattern matching in strings.

Table of LIKE Operator

Pattern	Description	Example	Matches
%	Matches any sequence of characters	'A%'	'Apple', 'Amazing'
_	Matches any single character	'A_'	'An', 'At'

Example

SELECT product_name
FROM products
WHERE product_name LIKE 'A%';

IN Operator

The IN operator is used to specify multiple values in a WHERE clause.

Example

SELECT first_name, last_name
FROM employees
WHERE department_id IN (1, 2, 3);

BETWEEN Operator

The BETWEEN operator selects values within a given range.

Example

SELECT product_name, price
FROM products
WHERE price BETWEEN 100 AND 200;

IS NULL Operator

The IS NULL operator is used to filter rows with NULL values.

Example

SELECT first_name, last_name
FROM employees
WHERE email IS NULL;

Conclusion

SQL operators are used for manipulating and querying data in a database. This chapter covered various types of operators, including arithmetic, comparison, logical, bitwise, compound, and other useful operators. Understanding these operators will help you write more effective and efficient SQL queries.