• To print ename starting with A and B

  select * from emp where ename >= 'A' and ename < 'C';

Special Operators (Like, Between, Any, In)

Like

• To print row starting with A

  select * from emp where ename like ‘A%’;

• here % means wildcard (used for pattern matching), any character and any number of character.

• Query:

  select * from emp where ename = ‘A%’; ← this search for string ‘A%’

  select * from emp where ename like ‘A%’ or ename like ‘B%’;

  select * from emp where ename like ‘%A’; ← rows ending with A

  select * from emp where ename like ‘%A%’; ← all rows which contains the letter A

  select * from emp where ename like ‘A%S’; ← all rows starting with A and ending with S

  select * from emp where ename not like ‘A%’;

  select * from emp where ename like ‘_ _ A%’; ← underscore means one character, A is the 3rd character

  select * from emp where ename like ‘_ _ _ _’; ← any four letter word

  select * from emp where ename like ‘_ _ R _’; ← any four letter word with R as 3rd letter

Between

• Easier to write
• Works faster because it is precompiled (already has execution plan)

select * from emp where hiredate ≥ ‘2024-01-01’ and hiredate ≤ ‘2024-12-31’;

seleect * from emp where hiredate between ‘2024-01-01’ and ‘2024-12-31’;

Any → Logical OR (recommended)

• We cannot use ANY directly in MySQL, ANY is available in ORACLE.
• You can only use ANY with subqueries.
• In ANY syntax = means operator overloading, It is slower than In.
• Use ANY if in your requirement you want to use relational operator while if you don’t need to use relational operators then us IN OPERATOR