SQL Operators: If you’re new to SQL, this article will help you understand what operators are and the different types of operators available. You’ll also find code examples showing how they can be used.
We use SQL queries to access the database table’s data and view that time. Still, to manipulate and retrieve the data in a better way, we are utilizing arithmetic operations, logical operations, comparison operations, compound operations, etc.
Note: SQL operators filter the table’s data by a specific condition in the SQL statement.
What are SQL Operators?
Operators in SQL are keywords or symbols that can perform specific logical and mathematical operations on operands. Operators often use the SQL WHERE clause to retrieve results based on a specific logical or mathematical operation.
An important part of creating a SQL query is connecting two or more conditions with operators. Operators are used to manipulating data and producing a resultset based on the operator’s function. There are various types of SQL operators depending on the operation they perform.
Types of SQL Operators
SQL operator is categorized into different categories based on the operation they are responsible for performing. Here are the different types of operators in SQL:
- SQL Arithmetic Operators
- SQL Logical Operators
- SQL Comparison Operators
- SQL Set Operators
Now we are going to every operator and discuss their use in detail and understand how these operators in SQL work in a query with the help of examples.
SQL Arithmetic Operators
The Arithmetic operator in SQL is used to carry out arithmetic computations and operations on numerical data found in database tables. This operator can perform all arithmetic operations, such as addition, subtraction, multiplication, division, and modulus.
| Operator | Description |
|---|---|
| Plus (+) | used for adding the numeric operands on either side of the operator |
| Minus (-) | used for subtracting the right numeric operand from the left numeric operand. |
| Multiplication (*) | used for multiplying a given numeric value to the operand given on the other side of the operator or multiplying the numeric columns provided on either side of the operator. |
| Division (/) | used for returning a quotient by dividing the first operand by the second or one numeric column by another. |
| Modulus (%) | used to return the remainder by dividing the first operand by the second or one numeric column by another. |
Note: SQL Arithmetic Operators are used with the WHERE clause.
SQL Logical Operators
The Logical operator in SQL is used to compare two data values in the database tables and determine whether they meet specific conditions. If both values meet the specified conditions, the Logical operator will return True. Otherwise, it will return False.
| Operator | Description |
|---|---|
| AND | This operator returns TRUE if all the mentioned conditions separated by AND are met; otherwise returns FALSE. |
| OR | This operator returns TRUE if at least one of the given conditions separated by OR is fulfilled, otherwise returns FALSE. |
| NOT | This operator has been used for the negation of the output of any logical operator. It checks for the reverse of a given logical expression. |
Note: SQL Logical Operators are used with the WHERE clause.
We have some special logical operators used to select records from the table. These operators are commonly used inside the WHERE clause or with the HAVING statement.
Here is the list of special logical operators with descriptions.
| Operator | Description |
|---|---|
| ALL | This operator compares the provided value to all the values of a column returned from the sub-query. It selects all the records of an inner SELECT statement. |
| ANY | This operator returns records when any one value returned from the sub-query satisfies the provided condition. It must match with at least one record of the inner query. |
| SOME | This operator is used to compare each value of the data set. It checks whether a particular value matches some values in the given data set. |
| IN | This operator is a short-hand for multiple OR operations where you can compare the values of selected columns to those in a given list. It returns TRUE if at least one match from the list is found, else returns FALSE. |
| BETWEEN | This operator is used to retrieve records within the provided range. BETWEEN operator can work with numbers, characters, dates, and times. The range is specified using another logical operator AND. |
| IS NULL | This operator is useful in fetching the rows for which the value for a certain field is NULL. |
| EXISTS | This operator determines if a certain row exists in a database table. |
| LIKE | The LIKE operator retrieves those records from a table that matches the provided pattern. The LIKE operator often uses two wild cards, % (percentage) and _ (underscore). The % sign represents 0 or more characters, _ sign represents a single character. |
Comparison Operator In SQL
The SQL Comparison operator is used to compare two data values present in database tables. The comparison operators can also compare one expression to another expression.
A comparison operation can produce the results TRUE, FALSE, or UNKNOWN. If any one of the expressions or both of them are NULL, the operation returns UNKNOWN.
| Operator | Description |
|---|---|
| Greater than (>) | This operator shows data greater than the query’s provided value. |
| Less than(<) | This operator shows data that are less than the provided value in the query. |
| Equal to (=) | This operator is used to show data that matches the provided value in the query. |
| Greater than or equal to(>=) | This operator is used to show data that are greater than and equal to the provided value in the query. |
| Less than or equal to (<=) | This operator shows data that are less than and equal to the provided value in the query. |
| Not equal to (<> or!=) | This operator shows data that do not match the query’s provided value. |
Note: Comparison Operator In SQL are used with the WHERE clause.
SQL Set Operators
The SQL Set operators are used to combine two or more similar data present in different SQL databases. The SQL Set operators merge the result retrieved from each query is combined into a single query result.
| Data Type | Description |
|---|---|
| UNION | This operator merges the result from two or more SELECT statements into a single query result. The data type and the number of columns used with the UNION operator must be the same for each SELECT statement. |
| UNION ALL | This operator merges the result from two or more SELECT statements into a single query result. The UNION ALL operator also shows duplicate or repeated values from both queries. |
| INTERSECT | This operator is used to fetch the common records from two or more SELECT statements. The data type and the number of columns used with the INTERSECT operator must be the same for each SELECT statement. |
| MINUS | This operator combines the result retrieved from two or more SELECT statements but only shows the results from the first data set. |
Note: SQL Set Operators are used with the SELECT statements.
Conclusion:
We hope this article on SQL operators will guide you with basic ideas on different types of SQL operators like SQL Arithmetic Operators, SQL Logical Operators, Comparison Operators In SQL, and SQL Set Operators. If you want more details about various SQL operators, you can follow the links above.
Leave a Reply