NULL Condition in SQL

1. IS NULL
2. IS NOT NULL

NOTES:

• The NULL conditions includes the IS NULL condition and IS NOT NULL conditions.
• The IS NULL conditions tests for nulls.
• A null value means the value is unavailable, unassigned, unknown or inapplicable. (null and zero both are different)
• Therefore, you cannot test with = because a null cannot be equal or unequal to any value.

Example:

The above example retrieves the last names and managers of all employees who do not have a manager.

Example:
To retrieve the names, salary of employees who don't receive commission we can write the query like:

Example:

The above example shows the name,sal and commission of employees who commission is not null, i.e.

the employee receives commission, it may be zero but not null.

So, remember: null and zero both are different. 

LIKE Condition in SQL

• Use the LIKE condition to perform wildcard searches of valid search string values.
• Search conditions can contain either literal characters or number:
• % denotes zero or many characters.
• _ denotes one character.

Example:

Example: 

We can combine Pattern-Matching characters.

The % and _ symbols can be used in any combination with literal characters. The example on the slide displays the name of all employees whose name have an O as the second character. (in upper example)

The lower example displays the names of employees who has job ending with ER.

IN Condition

Use the IN membership condition to test for values in a list.

Example:

To test for values in a specified set of values, use the IN condition. The IN condition is also known as the "membership condition"

The above example displays employee no, name, salary, deptno from emp table for all employees whose salary is 1000,2000,3000, or 5000.

The IN condition can be used with any datatype.

Example:

BETWEEN Condition in SQL

Use the BETWEEN condition to display rows based on a range of values.

Example: (from emp table)

Note:

• You can display rows based on a range of values using BETWEEN range condition. The range that you display contains a lower limit and upper limit.
• Value specified with the BETWEEN condition are inclusive. 
• You must specify the lower limit first.