27  SQL WHERE

Operators (Predicates)	Definition
=	Equal
<> !=	Not Equals
> <	greater, lesser
>= <=	greater or equal, lesser or equal
AND	logical AND
OR	logical OR
NOT	logical NOT
BETWEEN	Between given 2 values, limits are included
LIKE	Is given pattern in the string (wildcard are included)

27.1 Examples

Chinook

select * from Employee where EmployeeId = 5;

select * from Employee where EmployeeId != 5;

select * from Employee where EmployeeId <> 5;

select * from Employee where EmployeeId < 5;

select * from Employee where EmployeeId <= 5;

select * from Employee where EmployeeId > 5;

select * from Employee where EmployeeId >= 5;

27.2 logical operators

• AND
• OR
• NOT

p	q	p AND q
T	T	T
T	F	F
F	T	F
F	F	F

p	q	p OR q
T	T	T
T	F	T
F	T	T
F	F	F

27.3 IN Operator

select * from Employee where EmployeeId IN (2,5,9);  

27.4 Between operator

27.5 LIKE Operator

We use the so called wild cards while searching. The characters % and _ are supported by almost all databases while others are supported less.

• sql server wild cards
• oracle wild cards
• sqlite wild cards

Symbol	Description	Oracle	Sql Server	sqlite
%	zero or more characters	✓	✓	✓
_	a single character	✓	✓	✓
[]	any single character within the brackets	❌	✓	❌
^	any character not in the brackets	❌	✓	❌
-	any single character within the specified range	❌	✓	❌

examples in Chinook

SELECT * from Customer c WHERE C.FirstName LIKE 'L%';

SELECT * from Customer c WHERE C.FirstName LIKE 'Lu_s'

Works in SQL Server

SELECT * from Customer c WHERE C.Address LIKE '%[12]%'

-- second character is a
SELECT * from Customer c WHERE  C.FirstName LIKE '_[a]%'

-- second character is NOT a
--SELECT * from Customer c WHERE C.FirstName LIKE '_[^a]%'

27.6 Chaining of conditions

• Use parentheses when you need to chain more than 3 conditions. Like mathematics, parentheses change the order of operations.

• (2+3)*5

• 2+3*5