SQL Boolean operator
Description
The Boolean operators are those that are true or false. They returns a true or false values to combine one or more true or false values.
The Boolean operators are :
| Operator | Description |
|---|---|
| AND | Logical AND compares between two Booleans as expression and return true when both expressions are true |
| OR | Logical OR compares between two Booleans as expression and return true when one of the expression is true |
| NOT | Not takes a single Boolean as an argument and changes its value from false to true or from true to false. |
Syntax
SELECT [column_name | * | expression] [boolean operator]
[column_name | * | expression .....]
FROM <table_name>
WHERE <expressions> [ boolean operator |
arithmetic operator | ...] <expressions>;
Parameters
| Name | Description |
|---|---|
| column_name | Name of the column of a table. |
| * | All the columns of a table. |
| expression | Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQL query that compare values against other values or perform arithmetic calculations. |
| table_name | Name of the table. |
| boolean operator | AND, OR , NOT. |
| arithmetic operator | Plus(+), minus(-), multiply(*) and divide(/). |
Table of Contents :
- And operator
- Or operator
- Not operator
- Using multiple And operator
- Using And Or comparison operator
- Using Not And operator
- Using Not And comparison operator
- Using multiple Not operator
- Using multiple Not with Equal to operator
- Using multiple Not with Not equal to operator
- Using Not And Or operator
- Using And Not Or with Equal to operator
- Using And Not Or with greater than, less than operator
- Using And Or Not with date value
SQL Boolean AND operator
Logical AND compares two Booleans as expression and returns TRUE when both of the conditions are TRUE and returns FALSE when either is FALSE; otherwise returns UNKNOWN (an operator that has one or two NULL expressions returns UNKNOWN).
Example
Sample table : customer
To get data of 'cust_code', 'cust_name', 'cust_city', 'cust_country' and 'grade' from the 'customer' table with following conditions -
1. 'cust_country' must be ’UK’,
2. and 'grade' of the 'customer' must be 2,
the following sql statement can be used :
SELECT cust_code, cust_name, cust_city,cust_country,grade FROM customer WHERE cust_country = 'UK' AND grade = 2;
Output
Pictorial presentation
SQL Boolean OR operator
Logical OR compares two Booleans as expression and returns TRUE when either of the conditions is TRUE and returns FALSE when both are FALSE. otherwise, returns UNKNOWN (an operator that has one or two NULL expressions returns UNKNOWN).
Example
To get data of 'cust_code', 'cust_name', 'cust_city', 'cust_country' and 'grade' from the 'customer' with following conditions -
1. either 'cust_country' is ’USA’,
2. or 'grade' of the 'customer' is 3,
the following sql statement can be used :
SELECT cust_code,cust_name, cust_city,cust_country,grade FROM customer WHERE cust_country = 'USA' OR grade = 3;
Output
Pictorial presentation
SQL Boolean NOT operator
Logical NOT takes a single Boolean as an argument and changes its value from false to true or from true to false.
Example
To get all columns from the 'customer' table with following condition -
1. grade for the customer not greater than 1,
the following sql statement can be used :
SELECT * FROM customer WHERE NOT grade>1;
Output
SQL boolean multiple AND operator
In the following topics we are discussing the usage of multiple AND operator.
In the following example more than one 'AND' operators along with the SQL SELECT STATEMENT is used.
Example
To get data of 'cust_code', 'cust_name', 'cust_city', 'cust_country' and 'grade' from the 'customer' table with following conditions -
1. 'cust_country' is ’UK',
2. and 'cust_city' is 'London',
3. and 'grade' of the 'customer' must be greater than 1,
the following sql statement can be used :
SELECT cust_code,cust_name,cust_city,cust_country,grade
FROM customer
WHERE cust_country='UK'
AND cust_city='London' AND grade>1;
Output
SQL boolean AND OR comparison operator
In the following topic we are discussing the usage of 'AND' and 'OR' operator.
Using AND OR comparison operator with the select statement an example have shown.
Example
To get data of 'cust_code', 'cust_name', 'cust_city', 'cust_country' and 'grade' from the 'customer' table with following conditions -
1. 'cust_country' is ’UK’ or cust_city is 'London' ,
2. and 'grade' of the 'customer' must be other than 3 ,
the following sql statement can be used :
SELECT cust_code, cust_name, cust_city, cust_country, grade FROM customer WHERE (cust_country = 'UK' OR cust_city = 'London') AND grade <> 3;
Output
SQL boolean NOT AND operator
In the following example NOT, AND operator along with the SQL SELECT STATEMENT have used.
Example
To get data of 'cust_code', 'cust_name', 'cust_city', 'cust_country' and 'grade' from the 'customer' table with following conditions -
1. 'cust_country' must be 'India',
2. and 'grade' of the 'customer' must be other than 3,
the following sql statement can be used :
SELECT cust_code, cust_name, cust_city, cust_country ,grade FROM customer WHERE cust_country = 'India' AND NOT grade = 3;
Output
SQL boolean NOT AND comparison operator
In the collowing example we are going to discuss the usage of NOT and AND comparison operator along with the SQL SELECT STATEMENT.
Example
To get data of 'cust_code', 'cust_name', 'cust_city', 'cust_country' and 'grade' from the 'customer' table with following conditions -
1. 'cust_country' is other than 'India',
2. and 'grade' of the 'customer' must be 3,
the following sql statement can be used :
SELECT cust_code, cust_name, cust_city, cust_country, grade FROM customer WHERE NOT cust_country = 'India' AND grade = 3;
Output
SQL boolean multiple NOT operator
In the following topics we are discussing the usage of multiple NOT operator.
In the following example more than one NOT operators with the SQL SELECT STATEMENT have used.
Example
To get data of 'cust_code', 'cust_name', 'cust_city', 'cust_country' and 'grade' from the 'customer' table with following conditions -
1. 'cust_country' is other than 'India',
2. and 'cust_city' must be other than 'London',
3. and 'grade' of the 'customer' must be other than 1,
the following sql statement can be used :
SELECT cust_code, cust_name, cust_city, cust_country, grade FROM customer WHERE NOT cust_country = 'India' AND NOT cust_city = 'London' AND NOT grade = 1;
Output
SQL boolean multiple NOT with equal to (=) operator
In the following topic we are discussing the usage of multiple NOT operator with EQUAL TO operator.
In the following example more than one Not operators and comparison operator equal to ( = ) with the SQL SELECT STATEMENT have used.
Example
To get data of 'cust_code', 'cust_name', 'cust_city', 'cust_country' and 'grade' from the 'customer' table with following conditions -
1. 'cust_country' is other than 'India',
2. and 'cust_city' must be other than 'London',
3. and 'grade' of the 'customer' must be 1 ,
the following sql statement can be used :
SELECT cust_code, cust_name, cust_city, cust_country, grade FROM customer WHERE NOT cust_country = 'India' AND NOT cust_city = 'London' AND grade = 1;
Output
SQL boolean multiple NOT with not equal to operator
In the following topic we are discussing the usage of multiple NOT operator with NOT EQUAL TO operator.
In the following example 'NOT' operator and comparison operator 'not equal to' ( < > ) along with the SQL SELECT STATEMENT have used.
Example
To get data of 'cust_code', 'cust_name', 'cust_city', 'cust_country' and 'grade' from the 'customer' table with following conditions -
1. 'cust_country' is other than 'India',S
2. and 'cust_city' must be other than 'London' ,
3. and 'grade' of the 'customer' must be not equal to other than 1,
the following sql statement can be used :
SELECT cust_code,cust_name, cust_city,cust_country,grade FROM customer WHERE NOT cust_country='India' AND NOT cust_city='London' AND NOT grade<>1;
Output
SQL boolean NOT AND OR operator
In the example 'NOT' 'AND' 'OR' operator along with the SQL SELECT STATEMENT have used.
Example - 1
To get data of 'cust_code', 'cust_name', ' cust_city', 'cust_country' and 'grade' from the 'customer' table with following conditions -
1. 'cust_country' is not other than 'UK',
2. or 'cust_city' must be other than 'Bangalore' ,
3. and 'grade' of the 'customer' must be greater than 1,
the following sql statement can be used :
SELECT cust_code, cust_name, cust_city, cust_country, grade FROM customer WHERE NOT (cust_country = 'UK' OR cust_city = 'Bangalore') AND grade > 1;
Output
Example - 2
To get data data of all 'cust_code', 'cust_name', 'cust_city', 'cust_country' and 'grade' from the 'customer' table with following conditions -
1. grade is 1 or 'agent_code' is A003 will not come,
2. and 'cust_country' is 'India' will not come,
here is the sql statement can be used :
SELECT cust_code,cust_name,cust_city,cust_country,grade FROM customer WHERE NOT((grade=1 OR agent_code='A003') AND cust_country='India');
Output
SQL boolean AND NOT OR with EQUAL TO ( = ) operator
In the following topic we are discussing the usage of boolean AND, NOT, OR and comparison operator EQUAL TO (=) in a select statement.
Example
To get data of 'cust_code', 'cust_name', 'cust_city', 'cust_country' and 'grade' from the 'customer' table with following conditions -
1. 'cust_country' is not other than 'UK' ,
2. or 'cust_city' must be not other than 'Bangalore' ,
3. and 'grade' of the 'customer' must be greater than 1 and other than 3,
the following sql statement can be used :
SELECT cust_code, cust_name, cust_city, cust_country, grade FROM customer WHERE NOT (cust_country = 'UK' OR cust_city = 'Bangalore') AND grade > 1 AND NOT grade = 3;
Output
SQL boolean AND NOT OR with LESS THAN, GREATER THAN operator
In the following topic we are discussing the usage of Sql boolean AND NOT OR with LESS THAN (< ) GREATER THAN (>) operator.
Example
To get data of 'cust_code', 'cust_name', 'cust_city', 'cust_country' and 'grade' from the 'customer' table with following conditions -
1. 'cust_country' is not other than 'UK',
2. or 'cust_city' must be not other than 'Bangalore',
3. and 'grade' of the customer must be within the range 1 to 3,
the following sql statement can be used :
SELECT cust_code, cust_name, cust_city, cust_country, grade FROM customer WHERE NOT (cust_country = 'UK' OR cust_city = 'Bangalore') AND grade > 1 and grade < 3;
Output
SQL boolean AND OR NOT with date value
In the following topic we are discussing the usage of three operators 'AND', 'OR' and 'NOT' with date value.
Using AND , OR, NOT and comparison operator with the select statement an example have shown
Example - 1
Sample table : orders
To get data of 'ord_num', 'ord_amount', 'advance_amount', 'ord_date', 'cust_code', 'agent_code' from the 'orders' table with following conditions -
1. combination of 'ord_date' is '20-Jul-08' and 'ord_num' is greater than 200120 will not appear,
2. or 'ord_amount' must be greater than or equal to 4000,
the following sql statement can be used :
SELECT ord_num, ord_amount, advance_amount, ord_date, cust_code, agent_code FROM orders WHERE NOT ((ord_date = '20-Jul-08' AND ord_num > 200120) OR ord_amount < 4000);
Output
Example - 2
To get data of 'ord_num', 'ord_amount', 'advance_amount', 'ord_date', 'cust_code', 'agent_code' from the 'orders' table with following conditions -
1. 'ord_amount' will be below 1000,
2. combination of and 'ord_date' is '20-Jul-08' and ord_num is greater than 200108 will not appear
the following sql statement can be used :
SELECT ord_num,ord_amount,advance_amount,ord_date, cust_code,agent_code FROM orders WHERE(ord_amount<1000 AND NOT(ord_date='20-Jul-08' AND ord_num>200108));
Output
Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.
Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.
