Using BETWEEN, IN, LIKE, IS NULL
In SQL database management systems, BETWEEN, IN, LIKE, and IS NULL are SQL operators that help database users filter and display table data in a more flexible order based on specific user-defined conditions. Database users can use operators like BETWEEN, IN, LIKE, and IS NULL in the WHERE clause to improve the results of database table queries.
BETWEEN SQL Operator.
In SQL database management systems, the BETWEEN operator is used to filter table data records by setting results within a particular range. The BETWEEN operator is used for numeric, date, or time values in tables that include multiple boundary values.
Syntax of the BETWEEN SQL Operator.
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
The BETWEEN operator here includes the lower and upper boundaries of the range value1 and value2.
The BETWEEN operator here includes the range, meaning it includes both value1 and value2.
Example – In the existing EMPLOYEE table, if the database user wants to display employee records whose age is between 20 and 44, then:
SELECT * FROM employe WHERE emp_age BETWEEN 20 AND 44;
This query displays all employee records whose age is between 20 and 44, including all employees between 20 and 44 years old.
Result of the BETWEEN SQL Operator.
Employee_id Emp_name Emp_age Department Salary
101 Siddhi Deora 21 Marketing 74000
201 Harry Deora 23 Development 44000
301 Bhavshi Deora 37 Design 63000
IN SQL Operator.
In SQL database management systems, the IN operator is used to display multiple possible values for a table column. Here, the IN operator helps database users test whether a column’s value in the current table properly matches any value in the list.
Syntax of the IN SQL Operator.
SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, value3, …);
Here, the IN operator returns a list of table column values that can be numeric, string, or a combination of both.
Example – Here, the database user wants to find employee records in the Employee table that are associated with the Marketing, Development, or Design departments.
SELECT * FROM employe WHERE department IN (‘Marketing’, ‘Development’, ‘Design’);
This query displays all employee records that are associated with the Marketing, Development, or Design departments listed above.
Result of the IN SQL Operator.
employee_id emp_name emp_age department salary
101 Siddhi deora 21 Marketing 74000
201 Harry deora 23 Development 44000
301 Bhavshi deora 37 Design 63000
LIKE SQL Operator.
The LIKE operator is used in SQL database management systems to match patterns with string values. It is specifically used when database users want to find a specific pattern in a table column.
Syntax of the LIKE SQL Operator.
SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;
The LIKE operator pattern includes some wildcard characters.
- % – Displays zero or more characters.
- _ – Displays a single character.
Example 1 – Finds employees in the current table whose name starts with “B”.
SELECT * FROM employe WHERE emp_name LIKE ‘B%’;
Here, the like operator query displays all employee records whose name starts with “B”, for example, “Bhavshi deora”.
Result of the LIKE SQL Operator.
employe_id emp_name emp_age department salary
301 Bhavshi deora 37 Design 63000
Example 2 – This finds employee records in the Employee table whose name contains the character word “Siddhi”.
SELECT * FROM employe WHERE emp_name LIKE ‘%Siddhi%’;
This query in the existing database table displays all employee records whose name contains the character word “Siddhi”.
Result of the LIKE SQL Operator.
employe_id emp_name emp_age department salary
101 Siddhi deora 21 Marketing 74000
IS NULL SQL Operator.
In SQL database management systems, the IS NULL operator is used to filter table rows where a NULL value is defined in a table column. Here, the IS NULL operator checks whether a table column contains a NULL value, i.e., a NULL value.
Syntax of the IS NULL SQL Operator.
SELECT column_name(s) FROM table_name WHERE column_name IS NULL;
The IS NULL operator checks for missing or undefined values in a column in a database table.
Example – If a database user wants to display employee records in the current table that do not have a department assigned, i.e., a NULL value is defined in the department column.
SELECT * FROM employe WHERE department IS NULL;
This table query displays all employee records whose department column is defined as NULL.
Result of the IS NULL SQL Operator.
employee_id emp_name emp_age department salary
104 Amit Sukla 24 Null 34000
Explanation of BETWEEN, IN, LIKE, IS NULL of Operators.
| Operator | BETWEEN, IN, LIKE, IS NULL Description | Example of BETWEEN, IN, LIKE, IS NULL |
| BETWEEN operator | It Filters table values within a specific range (inclusive) | SELECT * FROM employe WHERE emp_age BETWEEN 20 AND 44; |
| IN operator | It Checks in table if a column’s value matches any value in a list of existing table record | SELECT * FROM employe WHERE department IN (‘Marketing’, ‘Development’); |
| LIKE operator | It Performs table record pattern matching with the given string values | SELECT * FROM employe WHERE emp_name LIKE ‘B%’; |
| IS NULL operator | It checks or Filters table rows where a column has NULL values define | SELECT * FROM employe WHERE department IS NULL; |
