CBSE XII BOARD

Special Operators

☞ There are some special operators in SQL that perform some specific functions.

☞ These operators are typically used in WHERE clause of SELECT Statements.

☞ The commonly used relational operators in MySQL.

S.No.	Operator	Description
1.	BETWEEN/NOT BETWEEN	Check whether an attribute value is wihin a range or not.
2.	IN/NOT IN	Check whether an attribute value matches any value with a given list or not.
3.	IS NULL/IS NOT NULL	Check whether an attribute value is NULL or not.
4.	LIKE/NOT LIKE	Checks whether an attribute matches a given string pattern or not.
5.	DISTINCT	Permits only unique values. Eliminates duplicate ones.

☞ BETWEEN operator defines a range of values that a column value must fall within for the condition to become true.

☞ It includes both the lower and upper values.

☞ Values are the numbers, text or dates.

☞ NOT BETWEEN is just the reverse of BETWEEN.

☞ Syntax :

SELECT * FROM <table_name>
WHERE <column_name>
BETWEEN/NOT BETWEEN <limit_1> AND <limit_2>;

☞ Example :

SELECT * FROM Student
WHERE dob
BETWEEN"2009-07-10" AND "2010-05-21";

OR

SELECT * FROM Student
WHERE dob
NOT BETWEEN"2009-07-10" AND "2010-05-21";

☞ IN operator helps to reduce the need for multiple OR conditions in a SELECT statement.

☞ NOT IN is just the reverse of IN.

☞ Syntax :

SELECT * FROM <table_name>
WHERE <column_name>
IN/NOT IN (<list_of_values>);

☞ Example :

SELECT * FROM Student
WHERE fees
IN (14000.00, 14020.50, 15000.52);

OR

SELECT * FROM Student
WHERE fees
NOT IN (14000.00, 14020.50, 15000.52);

☞ NULL means missing / unknown / not applicable value.

☞ A NULL value is the one that has been left blank during creation of a record (i.e. it is not a 0 or space).

☞ To check NULL values, we use IS NULL and IS NOT NULL operators.

☞ IS NOT NULL is just the reverse of IS NULL.

☞ Syntax :

SELECT * FROM <table_name>
WHERE <column_name> IS NULL/IS NOT NULL;

☞ Example :

SELECT * FROM Student
WHERE fees IS NULL;

OR

SELECT * FROM Student
WHERE fees IS NOT NULL;