SQL Operators

7
1465

SQL Operators :– Comparison, Arithmetic and Logical Operators

Question – What is an Operator?

Answer As per simple definition, Operator are defined as a symbol which will perform some specific operation. Without operators, we can’t implement some operations, which is why operators are very helpful. Every operator is significant and performs a specific operation. The main purpose of using SQL Operators is to filter the data to be selected and are widely used in Expressions or Conditional statements of the WHERE and HAVING clause. Operators are the reserved words and used to perform operations like comparison, mathematical, etc.

We have different types of SQL operators.

  1. Arithmetical operators.
  2. Assignment operators.
  3. Comparison operators.
  4. Logical operators.
  5. String concatenation operators.
  6. Unary operators.

Among all the above mentioned SQL operators, we will mainly concentrate on 3 types of SQL Operators i.e. Comparison operators, Arithmetic operators and Logical operators. So let’s look into the various SQL operators one by one with practical implementation.

SQL Comparison Operators : –

This SQL operator is used to compare the column data with specific values.

Operators Name
Description Example
 = (Equal)  Value of Left operand is Equal (=) to Value of Right operand. A=B
 != Or <> (Not equal)  Value of Left operand is Not equal (!= Or <>) to Value of Right operand.   A!=B
 > (Greater than)  Value of Left operand is Greater than (>) Value of Right operand. A>B
 < (Less than)  Value of Left operand is Less than (<) Value of Right operand.  A<B
 >= (Greater than or Equal to)  Value of Left operand is Greater than or Equal to (>=) Value of Right operand.  A>=B
 <= (Less than or Equal to)  Value of Left operand is Less than or Equal to (<=) Value of Right operand.  A<=B

Let’s see how Comparison operator works practically by considering some examples. We will create one table and will give name as employee_phpring in our database (PHPRING). After creating the mentioned table, we will insert some dummy data to look the working of Comparison SQL operator.

–Creating a table employee_phpring in the PHPRING database

–Insert few rows into employee_phpring to perform Comparison operations.

–Applying comparison operators on the above created table.

1. (=) – Equal to operator :-
Query to find the employee whose salary is equal to 20000.

Equal to operator

2. (!= or <>) – Not Equal to operator :-
Query to find the employee whose salary is Not equal to 20000.

Not equal to operator

3. (>) – Greater than operator :-
Query to find the employee whose salary is greater than 20000.

Greater than Operator

4. (<) – Less than operator :-
Query to find the employee whose salary is greater than 20000.

Less than Operator

5. (<=) – Lessthan or Equal to operator :-
Query to find the employee whose salary is Less than or equa lto 20000*/

Less than or equal to opeartor

6. (>=) – Greater than or Equal to operator :-
Query to find the employee whose salary is greater than or equal to 20000*/

SQL Arithmetic Operators: –

This SQL operator is used with Number and Date data types.

Operators Name Description Example
+ (Addition) Adds the Value of A to the Value of B. A+B
–  (Subtraction) Subtracts the Value A from the Value of B. A-B
* (Multiplication) Multiply the Value of A and the Value of B. A*B
/ (Division) Divide the Value of A with the value of B. A/B

Finally, let’s see how Arithmetic SQL operator is useful in the real world by working out with some examples. Before we start with the demonstration of this SQL operator, we will declare 3 variables out of which 2 variables acts as inputs and 1 will give us the output.

–Declaring variables to perform Arithmetic operations.

Input Variables

–Perform Arithmetical operations by using above variables.

1. (+) – For Addition :-

2. (-) – For Subtraction :-

3. (*) – For Multiplication :-

4. (/) – For Division :-

Arithmetical SQL operator

SQL Logic Operators:-

This SQL operator is used to compare 2 conditions at a time to determine whether a row can be selected for the output or not.

Operators Name Description
AND True if all of a set of comparisons are true
OR True if either Boolean expression is true
NOT Reverse the value of any other Boolean operator
ALL True if all of a set of comparisons are true
ANY True if any one of a set of comparisons are true
BETWEEN True if the operand is within a range
IN True if the operand is equal to one of a list of expressions
LIKE True if the operand matches pattern
EXISTS True if a sub query contains any rows
IS NULL See if any nulls are existed in columns
SOME True if some of a set of comparisons are true

Tips and Tricks :-

  • Operators in the WHERE clause, such as “IS NULL”, “<>”, “!=”, “!>”, “!<”, “NOT”, “NOT EXISTS”, “NOT IN”, “NOT LIKE”, and “LIKE ‘%500’” generally  prevents the query optimizer from using an index to perform a search.
  • Exists is much faster than IN, when sub query results is very large.
  • IN is faster than Exists when sub query returns small values.
  • BETWEEN operator is much more useful than IN operator as Query optimizer can locate a range of numbers much faster (using BETWEEN) than it can find a series of numbers using the IN clause (which is really just another form of the OR clause).
  • Always try to position the most expensive AND clause first in the WHERE clause sequencing. Oracle evaluates un-indexed equations, linked by the AND verb in a bottom-up fashion. This means that the first clause (last in the AND list) is evaluated, and if it is found true, then only second clause is tested.
  • Always try to position the most expensive OR clause last in the WHERE clause sequencing. Oracle evaluates un-indexed equations, linked by the OR verb in a top-down fashion. This means that the first clause (first in the OR list) is evaluated, and if it is found false, then only second clause is tested
VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 10.0/10 (21 votes cast)
VN:F [1.9.22_1171]
Rating: +18 (from 18 votes)
SQL Operators, 10.0 out of 10 based on 21 ratings

7 COMMENTS

Comments are closed.