SQL Logical Operators – AND | OR | NOT

Example of using SQL Logical operators viz AND, OR and NOT. Logical operators are used to test two or more conditions in an SQL Select statements, update and delete statements with WHERE condition.

SQL AND Operator

If all two or more conditions are true only then select, update or delete can be performed on data.

 SQL AND Syntax

WHERE [condition_1]
AND [condition_2] ……………..AND [condition_n];

Example:

Find the titles from BOOKS table where price of book <100 AND Title of the book is Animal Form.

SELECT TITLE, PRICE FROM BOOKS WHERE PRICE<100 AND TITLE= ‘Animal Farm’ ;

 Output:

SQL OR Operator

If any one condition out of more than two is / are true, then retrieval of data can be performed.

POINTS TO NOTICE

• OR condition used in to test conditions in a select, insert, update and delete  statements
• SQL logical operators AND and OR condition can be used together to satisfy the required test cases

SQL OR syntax

 WHERE [condition_1] OR[condition_2]  …………….. OR [condition_n]

Example:

Get the book titles with price < 100 or the title with “The Castle”.

SELECT TITLE, PRICE FROM BOOKS WHERE PRICE<100 OR TITLE=’The Castle’;

Output:

SQL NOT Operator

• NOT Condition can be used to perform negation operation in a SELECT, INSERT, UPDATE, DELETE statements.
• NOT operator can be used before IN, OR, AND, BETWEEN, EXISTS, ANY operators.

SQL NOT Syntax

NOT [OPERATOR] [EXPRESSION]