SQL Between,like,in operaters

SQL Comparison Keywords

There are other comparison keywords available in sql which are used to enhance the search capabilities of a sql query. They are "IN", "BETWEEN...AND", "IS NULL", "LIKE".

Comparision Operators	Description
LIKE	column value is similar to specified character(s).
IN	column value is equal to any one of a specified set of values.
BETWEEN...AND	column value is between two values, including the end values specified in the range.
IS NULL	column value does not exist.

SQL LIKE Operator

The LIKE operator is used to list all rows in a table whose column values match a specified pattern. It is useful when you want to search rows to match a specific pattern, or when you do not know the entire value. For this purpose we use a wildcard character '%' 

For example: To select all the students whose name begins with 'S'

SELECT first_name, last_name
FROM student_details
WHERE first_name LIKE 'S%';


The output would be similar to:

first_name	last_name
-------------	-------------
Stephen	Fleming
Shekar	Gowda

The above select statement searches for all the rows where the first letter of the column first_name is 'S' and rest of the letters in the name can be any character.
There is another wildcard character you can use with LIKE operator. It is the underscore character, ' _ ' . In a search string, the underscore signifies a single character.
For example: to display all the names with 'a' second character,

SELECT first_name, last_name
FROM student_details
WHERE first_name LIKE '_a%';


The output would be similar to:

first_name	last_name
-------------	-------------
Rahul	Sharma

NOTE:Each underscore act as a placeholder for only one character. So you can use more than one underscore. Eg: ' __i% '-this has two underscores towards the left, 'S__j%' - this has two underscores between character 'S' and 'i'.

SQL BETWEEN ... AND Operator

The operator BETWEEN and AND, are used to compare data for a range of values.
For Example: to find the names of the students between age 10 to 15 years, the query would be like,

SELECT first_name, last_name, age
FROM student_details
WHERE age BETWEEN 10 AND 15;


The output would be similar to:

first_name	last_name	age
-------------	-------------	------
Rahul	Sharma	10
Anajali	Bhagwat	12
Shekar	Gowda	15

SQL IN Operator:

The IN operator is used when you want to compare a column with more than one value. It is similar to an OR condition.
For example: If you want to find the names of students who are studying either Maths or Science, the query would be like,

SELECT first_name, last_name, subject
FROM student_details
WHERE subject IN ('Maths', 'Science');


The output would be similar to:

first_name	last_name	subject
-------------	-------------	----------
Anajali	Bhagwat	Maths
Shekar	Gowda	Maths
Rahul	Sharma	Science
Stephen	Fleming	Science

You can include more subjects in the list like ('maths','science','history')
NOTE:The data used to compare is case sensitive.

SQL IS NULL Operator

A column value is NULL if it does not exist. The IS NULL operator is used to display all the rows for columns that do not have a value.
For Example: If you want to find the names of students who do not participate in any games, the query would be as given below
SELECT first_name, last_name 




FROM student_details
WHERE games IS NULL 

SQL BETWEEN Condition - Numeric example

The following is the SQL BETWEEN condition that retrieves values within a numeric range.
For example:

SELECT *
FROM suppliers
WHERE supplier_id BETWEEN 5000 AND 5010;

This SQL BETWEEN condition example would return all rows where the supplier_id is between 5000 and 5010, inclusive. It is equivalent to the following SQL SELECT statement:

SELECT *
FROM suppliers
WHERE supplier_id >= 5000
AND supplier_id <= 5010;

SQL BETWEEN Condition - Date example

You can also use the SQL BETWEEN condition to retrieve values within a date range.
For example:

SELECT *
FROM orders
WHERE order_date between to_date ('2003/01/01', 'yyyy/mm/dd')
AND to_date ('2003/12/31', 'yyyy/mm/dd');

This SQL BETWEEN condition example would return all orders where the order_date is between Jan 1, 2003 and Dec 31, 2003 (inclusive). It would be equivalent to the following SQL SELECT statement:

SELECT *
FROM orders
WHERE order_date >= to_date('2003/01/01', 'yyyy/mm/dd')
AND order_date <= to_date('2003/12/31','yyyy/mm/dd');

SQL BETWEEN Condition - Using the NOT operator

The SQL BETWEEN condition can also be combined with the SQL NOT operator.
For example:

SELECT *
FROM suppliers
WHERE supplier_id not between 5000 and 5500;

This SQL BETWEEN condition example would return all rows where the supplier_id was not between 5000 and 5500, inclusive. It would be equivalent to the following SQL SELECT statement:

SELECT *
FROM suppliers
WHERE supplier_id < 5000
OR supplier_id > 5500;