Profile Picture
Written ByMd Tanveer

Bunch of SQL Operators and Calculated field

Many SQL operators go unnoticed or do not seem to be used widely. They can be handy to pull data from your database. Like: BETWEEN and NOT BETWEEN, IN and NOT IN, LIKE and NOT LIKE, NULL and NOT NULL, Calculated or Virtual field.

Coding
258
Sep 15, 2019 @ 1:15 PM

BETWEEN and NOT BETWEEN

BETWEEN checks if a value is between two other values. The comparison is always inclusive, and the value of the arrange must be arranged from lower to higher.

SELECT * FROM Employees WHERE Salary BETWEEN 50000 AND 70000;

This is pull all the Employees details who have salary starting from 50000 to 70000.
SELECT * FROM Employees WHERE Salary NOT BETWEEN 50000 AND 70000;

This will bring the details of all Employees who's salary is less than 50000 or greater than 70000.

NOT BETWEEN is opposite of BETWEEN.

IN and NOT IN

The IN operator checks if a given value is found within a list of values (Placed in parentheses). The value to check must match a full (not partial) value on the list. The list could be in any order:

SELECT * FROM Employees WHERE Dept IN ('SL', 'IT', 'MK');

This will give us all the Employees details who belong to Sales, Information Technology or Marketing department.
SELECT * FROM Employees WHERE Dept NOT IN ('SL', 'IT', 'MK');

Will fetch all data for Employees who's department is not Sales, Information Technology or Marketing department.

NOT IN will give us the opposite result of IN:

LIKE and NOT LIKE

The LIKE operator performs text comparisons with wildcards. The wildcard symbols used are:


Example of usage with "_" (underscore) and LIKE operator.
%

Any number of characters (Including none)
_ (Underscore)

One single character (Exactly)
SELECT * FROM Employees WHERE LName LIKE 'M%';

It will fetch all the employees name begins with letter "M".
SELECT * FROM Employees WHERE LName LIKE '_M%';

It will fetch all the employees name where "M" is the second letter. Some implementations (including MySQL) allow us to perform LIKE comparisons with numeric and data values, but this is not standard SQL.
SELECT * FROM Employees WHERE LName NOT LIKE 'M%';

You will get all the employees name begin with any letter except "M".

There is also a NOT LIKE operator to reverse the results:

NULL and NOT NULL

The "=" operator cannot be used to match NULL values. It will evaluate to unknown (NULL in MySQL), even the value being compared to is in fact NULL.

SELECT * FROM Employees WHERE Phone = NULL;

The result will be an empty set even if you have some NULL in Phone column.
SELECT * FROM Employees WHERE Phone <> NULL;

If you use the above statement to find whose phone number is not NULL it will not work. Again you will get an empty set.
SELECT * FROM Employees WHERE Phone IS NULL;

This is the correct statement. It will fetch the employee details where phone is NULL.
SELECT * FROM Employees WHERE Phone IS NOT NULL;

Correct way to find where phone is not NULL.

Calculated or Virtual field

Besides including the fields (Columns) from your tables in the SELECT statement, you can define Calculated/Virtual fields "On the fly". For instance, assume that you want to see what each employee's total earnings will be by adding their salary and bonus columns:

SELECT FName, LName, Salary + Bonus FROM Employees;

The statement builds a new temporary column based on the given expression as part of the output for this query:
SELECT FName, LName, Salary + Bonus FROM Employees;
SELECT FName, LName, Salary + Bonus AS `Gross Pay` FROM Employees;
SELECT FName, LName, Salary + Bonus AS `Gross Pay` FROM Employees WHERE Salary + Bonus >= 80000;

By default, SQL will use the expression itself as the column heading. Therefore, it is best to use an Alias for a calculated field:


It's not allowed to refer to a column Alias in a WHERE clause, so we would have to repeat the expression in the following case: