SQL Operators

Last Updated: 3rd August, 2023

Overview

SQL Operators are used to specify conditions in an SQL statement. The combination of values, operators, and SQL statements can be used to retrieve data from one or more tables in a database. Common operators include comparison operators such as Not Equal and Equal operators (e.g. =, >, <, >=, <=, <>), logical operators (e.g. AND, OR, NOT), arithmetic operators (e.g. +, -, *, /), and set operators (e.g. UNION, INTERSECT, EXCEPT). SQL also includes special operators, such as LIKE and BETWEEN, for additional functionality.

SQL Arithmetic Operators

A retail store uses SQL to track customer purchases. The store could use the WHERE clause to filter customer purchase data by product type and apply the greater than operator (>) to find the customers who have purchased more than a certain amount of a particular product type.Let's help the store employee with it.

1. +: Used to add two values
2. -: Used to subtract one value from another
3. *: Used to multiply two values
4. /: Used to divide one value by another
5. %: Used to calculate the remainder of a division

Examples

SELECT 5 + 5;

This statement will add 5 and 5 together and return the result of 10.

SELECT 10 - 5;

This statement will subtract 5 from 10 and return the result of 5.

SELECT 10 * 5;

This statement will multiply 10 and 5 together and return the result of 50.

SELECT 10 / 5;

This statement will divide 10 by 5 and return the result of 2.

SELECT 10 % 5;

This statement will calculate the remainder of 10 divided by 5 and return the result of 0.

SQL Bitwise Operators

1. Bitwise AND ( & )
2. Bitwise OR ( | )
3. Bitwise XOR ( ^ )

For example, if we wanted to retrieve all records from a table whose ID is either 1, 4, or 5, we could use the following SQL query:

Table:

SELECT * FROM table WHERE ID & (1 | 4 | 5);

This query uses the bitwise OR (|) operator to combine the values 1, 4, and 5 into a single expression. The bitwise AND (&) operator is then used to compare this expression with the values in the ID column. If any of the values match, that record will be returned.

SQL Comparison Operators

1. = (equal to)
2. <> (not equal to)
3. >(greater than)
4. < (less than)
5. >= (greater than or equal to)
6. <= (less than or equal to)

Examples

1. = (equal to)

Table:

SELECT * FROM table WHERE age = 35;

This statement will select all records from the table where the age of the record is equal to 35.

2. <> (not equal to)

SELECT * FROM table WHERE age <> 35;

This statement will select all records from the table where the age of the record is not equal to 35.

3. >(greater than)

SELECT * FROM table WHERE age > 35;

This statement will select all records from the table where the age of the record is greater than 35.

4. < (less than)

SELECT * FROM table WHERE age < 35;

This statement will select all records from the table where the age of the record is less than 35.

5. >= (greater than or equal to)

SELECT * FROM table WHERE age >= 35;

This statement will select all records from the table where the age of the record is greater than or equal to 35.

6. <= (less than or equal to)

SELECT * FROM table WHERE age <= 35;

This statement will select all records from the table where the age of the record is less than or equal to 35.

Key takeaways

1. SQL Operators are used to filtering, group, and sort data within relational databases.
2. The different types of SQL Operators include comparison, logical, arithmetic, and set operators.
3. Comparison operators allow you to compare values within a query and return specific results.
4. Logical operators are used to combining multiple conditions within a query.
5. Arithmetic operators are used to performing mathematical calculations on data from a database.
6. Set operators allow you to combine the results of multiple queries into one.
7. SQL Operators are essential for database querying and manipulation.

Conclusion

The retail store used SQL to filter and sort customer purchase data and successfully identify customers who had purchased more than a certain amount of a particular product type. This allowed them to target those customers with special offers and discounts, which resulted in increased sales.

Quiz

1. Which of the following is a SQL operator? 
   1. AND 
   2. OR 
   3. XOR  
   4. ALL

Answer: A. AND

2. Which of the following is a logical operator? 
   1. AVG  
   2. OR 
   3. BETWEEN 
   4. MAX

Answer: B. OR

3. Which of the following is a comparison operator? 
   1. LIKE 
   2. IN 
   3. MOD 
   4. INT

Answer: A. LIKE

4. Which of the following is an arithmetic operator? 
   1. BETWEEN 
   2. +  
   3. AND 
   4. IS

Answer: B. +