Special Operators in SQL
Special operators are used to retrieve/get the values based on the condition specified. Below are the list of special operators.
1. IN and NOT IN
2. BETWEEN and NOT BETWEEN
3. IS NULL and IS NOT NULL
4. LIKE and NOT LIKE
1. IN and NOT IN:
IN operator is used to pick the values one by one from the list of values.
We can also use IN operator in place of OR operator when we are trying to to fetch/retrieve multiple values from a single column. So in this case "IN" operator performance is high compared to OR operator.
Syntax: select * from tableName where columnName IN (list of values);
Example: select * from employee where employee_id IN (1111,1112,1113);
NOT IN operator is used to pick the values other than specified condition values. So we can use this to filter unwanted data.
Syntax: Select * from tableName where columnName NOT IN (list of values);
Example: Select * from employee where employee_id NOT IN (1114,1115,1116);
So the above query fetch all the values except the specified employee numbers.
NOTE: In all databases NOT IN operator doesn't work with null values.
2. BETWEEN and NOT BETWEEN:
BETWEEN operator used to fetch/Retrieve range of values.
Syntax: select * from tableName where columnName BETWEEN low value and high value;
Example: Select * from Employee where salary BETWEEN 10000 and 20000;
So the above query fetches all the employee details whose salary between 10000 to 20000.
NOT BETWEEN operator won't pick/show the specified range of values.
Syntax: Select * from tableName where columnName NOT BETWEEN low value and high value;
Example: Select * from employee where salary NOT BETWEEN 10000 and 20000.
So the above query won't fetch the salary details of between 10000 to 20000.
3. IS NULL and IS NOT NULL:
IS NULL operator is used to fetch the NULL values.
NULL is an un-defined, unknown and unavailable value. It is not same as ZERO.
In all databases if any arithmetic operation performed on null values then system returns "NULL".
Example: NULL + 50 = NULL
Syntax: select * from tableName where columnName IS NULL;
Example: Select * from employee where Employee_name IS NULL;
The above query fetches all the employee details where the employee name is null.
IS NOT NULL operator is used to fetch other than null values.
Syntax: select * from tableName where columnName IS NOT NULL;
Example: Select * from Employee where employee_name IS NOT NULL;
The above query is used to fetch other than NULL values.
4. LIKE and NOT LIKE:
LIKE operator is used to retrieve data based on character pattern. LIKE operator performance is HIGH compared to searching functions.
Along with LIKE operator, we have to use one or two wildcard characters.
These are % and _(underscore).
Syntax: select * from tableName where columnName LIKE 'character patteren';
For example, Assume we have a table called employee and want to display/Fetch all the employee details whose employee name starts with 'M' character.
SQL> select * from employee where employee_name LIKE 'M%';
So the above query returns all the employee names whose name starts with 'M'.
NOT LIKE functionality is opposite to the LIKE operator. This will fetch all the values other than specified values.
If you have any doubts or any suggestion, Please drop a comment below.
Thank You
Useful.. Thanks
ReplyDelete