BETWEEN, IN and LIKE Operator

Let’s see, What is SQL Comparison keywords BETWEEN, IN and LIKE operator. In SQL, BETWEEN, IN and LIKE  operator are conditional operators, which are used after where clause to define the conditions in SQL.

 

Difference in BETWEEN, IN and LIKE Operator

BETWEEN Operator in SQL

BETWEEN operator is used to selecting values within a range. The values can be numbers, text, or dates.

SQL Syntax :

SELECT column_name, column_name2
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

 

Example Query –  Find the name and Department ID of the Employees whose salary is in the range of 2000 – 3000 $.

SQL> select First_Name, Department_Id from employees where salary BETWEEN 2000 AND 3000;

 

NOT BETWEEN Operator in SQL

NOT BETWEEN is used to filter the product out of the given range.

Example Query –

Find the name and Department ID of the Employees whose salary is not in the range of 2000 – 3000 $.

SQL> select First_Name, Department_Id from employees where salary NOT BETWEEN 2000 AND 3000;

 

IN Operator in SQL

IN Operator is used to testing the values against the given List. IN operator allows you to specify multiple values in a WHERE clause.

SQL Syntax :

SELECT column_name, column_name2
FROM table_name
WHERE column_name IN (Value, Value1, Value2, …..);

 

Example Query – Find the Employees Details who does belong from the department 50, 110 &  72.

SQL> select * from employees where Department_Id IN (72,110,50);

 

NOT IN Operator in SQL

NOT IN Operator is used if we don’t want to include some conditions in SQL.

Example Query – Find the Employees Details who does not belong from the department 50, 110 &  72.

SQL> select * from employees where Department_Id NOT IN (72,110,50);

 

LIKE Condition in SQL

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. Use LIKE operator to perform wildcard searches of valid search String values.

 

LIKE using WILDCARD(%) Percent Sign

We can use the wildcard(%) before and after the expression to search the result.

Query – Find the Employees name from the Employees table, whose names start with AMI.

SQL> select * from employees where First_Name LIKE ‘AMI%’;

 

Query – Find the Employees name from the Employees table, whose names ends with AMI.

SQL> select * from employees where First_Name LIKE ‘%AMI’;

 

Query – Find the Employees name from the Employees table, whose names contains the string AMI.

SQL> select * from employees where First_Name LIKE ‘%AMI%’;

 

LIKE using WILDCARD(_) Underscore Sign

We can use the wildcard(_) before, between and after the expression to search the result.

Query – Find the Employees name from the Employees table, whose names contains the letter a second place.

SQL> select * from employees where First_Name LIKE ‘_A_ _ _’;

 

Another example –

SQL> select * from employees where First_Name LIKE ‘_A%’;

0 Comment

Leave a Reply