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.
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.
NOT BETWEEN is opposite of BETWEEN.
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.
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.
NOT IN will give us the opposite result of IN:
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.
LIKE and NOT LIKE
The LIKE operator performs text comparisons with wildcards. The wildcard symbols used are:
%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".
Example of usage with "_" (underscore) and LIKE operator.
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.
There is also a NOT LIKE operator to reverse the results:
SELECT * FROM Employees WHERE LName NOT LIKE 'M%';You will get all the employees name begin with any letter except "M".
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;
By default, SQL will use the expression itself as the column heading. Therefore, it is best to use an Alias for a calculated field:
SELECT FName, LName, Salary + Bonus AS `Gross Pay` FROM Employees;
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:
SELECT FName, LName, Salary + Bonus AS `Gross Pay` FROM Employees WHERE Salary + Bonus >= 80000;