SQL Operators

SQL Operators

An operator is a reserved word or a character used primarily in an SQL statement's WHERE clause to perform operation(s), such as comparisons and arithmetic operations.

Operators are used to specify conditions in an SQL statement and to serve as conjunctions for multiple conditions in a statement.

•    Arithmetic operators

•    Comparison operators

•    Logical operators

•    Operators used to negate conditions


Arithmetic operators:

Arithmetic operators can perform arithmetical operations on numeric operands involved. Arithmetic operators are addition (+), subtraction (-), multiplication (*) and division (/). The + and - operators can also be used in date arithmetic.

Syntax:

 SELECT <Expression> [arithmetic operator] <expression>...
 FROM [table_name]
 WHERE [expression];

Parameters:

Expression: Expression made up of a single constant, variable, scalar function or column name and can also be the pieces of a SQL query that compare values against other values or perform arithmetic calculations.

arithmetic operator:
Plus (+), minus (-), multiply (*), and divide (/).
table_name: Name of the table.

Comparison operators:

A comparison (or relational) operator is a mathematical symbol which is used to compare between two values.

Comparison operators are used in conditions that compare one expression with another. The result of a comparison can be TRUE, FALSE, or UNKNOWN (an operator that has one or two NULL expressions returns UNKNOWN).

The following table describes different types of comparison operators

Operator                    Description
=                              Equal to
>                              Greater than
<                              Less than
>=                            Greater than equal to
<=                            Less than equal to
                                
Syntax:

SELECT [column_name | * | expression] <comparison operator>
[column_name | * | expression]
FROM <table_name>
WHERE <expression> [comparison operator] <expression>;

Parameters:
column_name: Name of the column of a table.
*: Indicates all the columns of a table.

Expression: Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQL query that compare values against other values or perform arithmetic calculations.
table_name: Name of the table.

comparison operator: Equal to (=), not equal to (<>), greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=).

Logical operators:


There are three Logical Operators namely, AND, OR, and NOT. These operators compare two conditions at a time to determine whether a row can be selected for the output. When retrieving data using a SELECT statement, you can use logical operators in the WHERE clause, which allows you to combine more than one condition.
 
Operator    Description

AND        Logical AND compares between two Booleans as expression and return true when both expressions are true
OR        Logical OR compares between two Booleans as expression and return true when one of the expression is true
NOT        Not takes a single Boolean as an argument and changes its value from false to true or from true to false.

Syntax:

SELECT [column_name | * | expression] [ boolean operator]
[column_name | * | expression .....]
FROM <table_name>
WHERE <expressions> [ boolean operator |
arithmetic operator | ...] <expressions>;

Parameters:

column_name: Name of the column of a table.
*: All the columns of a table.

expression: Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQL query that compare values against other values or perform arithmetic calculations.
table_name: Name of the table.
boolean operator: AND, OR, NOT.

arithmetic operator: Plus (+), minus (-), multiply (*) and divide (/).

Negate operators:

The Negate Operators reverses the meaning of the logical operators with which it is used. Below are the few Examples which tell you about the Negate Operators.

•    NOT LIKE
•    NOT IN
•    NOT BETWEEN
•    IS NOT NULL
•    NOT EQUAL (<>) or (! = ).

NOT EQUAL: Not equal works exactly opposite to the equal operator. Operator (<>) and (! =) works same.

Example:

SQL>select * from emp
where
emp_job <> 'ACCOUNTANT';

Here all records are selected excluding those whose status is accountant in company.

NOT BETWEEN: This operator is used negate the between operator.

Example:
SQL>select * from emp
where
emp_salary NOT BETWEEN 2000 and 3000.

It will select all records excluding those whose salary is between 2000 and 3000, including 2000 and 3000.
IS NOT NULL: It will check whether the selected field is not empty (null).

Example:

SQL>select * from emp
where
email_id IS NOT NULL;

Selects records of those employees who have their email-id.

NOT LIKE: The NOT LIKE operator used wildcard operators to compare a value that is not similar. It supports both wildcard characters that are mentioned in like operators.

Example:
SQL> select * from emp
where
emp_name NOT LIKE 'A%';

Finds any name that does not start with A and continues for any length.

NOT IN: The NOT IN operator search the value that is not mentioned in a predetermined list, if any of the value matches than row will be displayed.

Example:
SQL>select * from emp
where
emp_job

NOT IN ('PROGRAMMER','OPERATOR');

It will display all details of employee excluding those whose status in company is either programmer or operator.

0 comments:

Post a Comment