w3resource logo


SQL Boolean  operator

SQL Logical Operators

rating Average rating 10 out of 10. Total 1 users rated.

<<PreviousNext>>

Description

The Logical 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 Logical 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....
IN The IN operator checks a value within a set of values separated by commas and retrieve the rows from the table which are matching....
BETWEEN The SQL BETWEEN operator tests an expression against a range. The range consists of a beginning, followed by an AND keyword and an end expression....
ANY ANY compares a value to each value in a list or results from a query and evaluates to true if the result of an inner query contains at least one row....
ALL ALL is used to select all records of a SELECT STATEMENT. It compares a value to every value in a list or results from a query. The ALL must be preceded by the comparison operators and evaluates to TRUE if the query returns no rows....
SOME SOME compares a value to each value in a list or results from a query and evaluates to true if the result of an inner query contains at least one row...
EXISTS The EXISTS checks the existence of a result of a subquery. The EXISTS subquery tests whether a subquery fetches at least one row. When no data is returned then this operator returns 'FALSE'...

Syntax

SELECT [column_name | * | expression] [logical operator]
[column_name | * | expression .....]
FROM <table_name>
WHERE <expressions> [ logical 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 sable.
logical operator AND, OR , NOT etc.
arithmetic operator Plus(+), minus(-), multiply(*) and divide(/).

Table of Contents :

 

SQL Logical 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

Sql Boolean And operator

Pictorial presentation

Sql Boolean And operator

 

SQL Logical 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

Sql Boolean or operator

Pictorial presentation

SQL Boolean or operator image

 

SQL Logical 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 Not operator

 

SQL Logical 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 multiple And

 

SQL Logical 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 And  Or comparison operator

 

SQL Logical 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 operator

 

SQL Logical 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 Not And operator

 

SQL Logical 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  operator

 

SQL Logical 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 Equal to ( = ) operator

 

SQL Logical 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 multiple Not  with Not equal to ( <> ) operator

 

SQL Logical 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

using boolean not and or operator

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

using boolean not and or operator

 

SQL Logical AND NOT OR with EQUAL TO ( = ) operator

In the following topic we are discussing the usage of logical 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 Equal to ( = ) operator

 

SQL Logical AND NOT OR with LESS THAN, GREATER THAN operator

In the following topic we are discussing the usage of logical 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 Not Or with with Less than (< ) Greater than (>) operator

 

SQL Logical 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

Sql  And Or Not with date value

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

Sql  And Or Not with date value

Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

See our Model Database;

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.



<<PreviousNext>>