Welcome to our comprehensive guide on SQL (Structured Query Language) operators! In this blog post, we’re going to break down the various operators used in SQL, their syntax, and how to use them effectively.
You will also look at some examples, working with a hypothetical data set to give you a hands-on understanding. Let’s dive in!
What Are SQL Operators?
Operators in SQL are special reserved words used primarily in WHERE clause to perform operation(s) such as comparisons and arithmetic operations. These are the tools that allow us to specify the criteria you’re interested in – the records you want to include, exclude, combine, or compare.
There are several types of operators in SQL:
- Arithmetic operators
-
Comparison operators
-
Logical operators
Now, let’s take a closer look at each of these types of operators with the help of example data Products.
For instance, consider a Products table with the following data:
| ProductID | Price | Quantity | Cost |
|---|---|---|---|
| 1 | 50 | 10 | 25 |
| 2 | 30 | 20 | 17 |
| 3 | 70 | 15 | 40 |
Arithmetic Operators
Arithmetic operators are used to perform mathematical operations. They include + (addition), – (subtraction), * (multiplication), / (division), and % (modulus).
Let’s have a closer look at arithmetic operators. They’re used to perform mathematical operations:
| Operator | Description | Example |
|---|---|---|
| + | Addition | SELECT *, Cost + 5 as New_Cost FROM Products; |
| – | Subtraction | SELECT *, Price - Cost as Proft FROM Products; |
| * | Multiplication | SELECT *, Price * Quantity as Revenue FROM Products; |
| / | Division | SELECT *, Cost / Price as Cost_Price_Ratio FROM Products; |
| % | Modulus | SELECT *, Price % Cost as Price_Cost_Modulus FROM Products; |
Comparison Operators
Comparison operators are used to compare one expression with another. The result of a comparison operation is always a Boolean value – true, false, or unknown. They include = (equal), <> (not equal), > (greater than), < (less than), >= (greater than or equal), and <= (less than or equal).
| Operator | Description | Example |
|---|---|---|
| = | Equal to | SELECT * FROM Products WHERE Price = 50; |
| <> or != | Not equal to | SELECT * FROM Products WHERE Price <> 50; |
| > | Greater than | SELECT * FROM Products WHERE Price > 50; |
| < | Less than | SELECT * FROM Products WHERE Price < 50; |
| >= | Greater than or equal to | SELECT * FROM Products WHERE Price >= 50; |
| <= | Less than or equal to | SELECT * FROM Products WHERE Price <= 50; |
Logical Operators
Logical operators are used to combine two or more conditions. They include AND, OR, NOT, IN, BETWEEN, LIKE, EXISTS, ALL, ANY, and SOME.
| Operator | Description | Example |
|---|---|---|
| AND | True if all conditions are true | SELECT * FROM Products WHERE Price > 20 AND Quantity < 10; |
| OR | True if at least one condition is true | SELECT * FROM Products WHERE Price < 20 OR Quantity > 10; |
| NOT | True if the condition is false | SELECT * FROM Products WHERE NOT Price = 30; |
| IN | True if the value is in the list | SELECT * FROM Products WHERE ProductID IN (1,2,3); |
| BETWEEN | True if the value is within the range | SELECT * FROM Products WHERE Price BETWEEN 20 AND 30; |
| LIKE | True if the value matches the pattern | SELECT * FROM Products WHERE ProductName LIKE 'A%'; |
| EXISTS | True if the subquery returns at least one record | SELECT * FROM Products WHERE EXISTS (SELECT ProductID FROM Inventory WHERE Products.ProductID = Inventory.ProductID); |
Conclusion
SQL operators are fundamental elements of SQL. They allow us to manipulate data, perform calculations, make comparisons, and establish logical conditions. The above examples should provide a solid basis for their usage. Always remember to use the right operator for your specific use case.
