w3resource logo


SQL Boolean  operator

SQL Logical Operators

<<PreviousNext>>

Seondary Nav

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


CUST_CODE CUST_NAME            CUST_CITY                           CUST_COUNTRY           GRADE
--------- -------------------- ----------------------------------- -------------------- ----------
C00013    Holmes               London                              UK                    2
C00024    Cook                 London                              UK                    2

Go Top

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

CUST_CODE       CUST_NAME          CUST_CITY                           CUST_COUNTRY              GRADE
--------------- ----------------- ----------------------------------- -------------------- ----------
C00001          Micheal            New York                            USA                   2
C00020          Albert             New York                            USA                   3
C00002          Bolt               New York                            USA                   3
C00010          Charles            Hampshair                           UK                    3
C00012          Steven             San Jose                            USA                   1
C00009          Ramesh             Mumbai                              India                 3
C00011          Sundariya          Chennai                             India                 3

Go Top

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

CUST_CODE       CUST_NAME       CUST_CITY       WORKING_AREA   CUST_COUNTRY         GRADE 
--------------- --------------- --------------- -------------- -------------------- ---------- ---------
C00015          Stuart          London          London         UK                    1        .....
C00021          Jacks           Brisban         Brisban        Australia             1        ....
C00019          Yearannaidu     Chennai         Chennai        India                 1       .....
C00005          Sasikant        Mumbai          Mumbai         India                 1       .....
C00007          Ramanathan      Chennai         Chennai        India                 1        .....
C00004          Winston         Brisban         Brisban        Australia             1        .....
C00023          Karl            London          London         UK                    0        .....
C00006          Shilton         Torento         Torento        Canada                1       .....
C00012          Steven          San Jose        San Jose       USA                   1        .....
C00008          Karolina        Torento         Torento        Canada                1        .....

Go Top

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

CUST_CODE       CUST_NAME       CUST_CITY       CUST_COUNTRY              GRADE
--------------- --------------- --------------- -------------------- ----------
C00013          Holmes          London          UK                            2
C00024          Cook            London          UK                            2

Go Top

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


CUST_CODE       CUST_NAME       CUST_CITY       CUST_COUNTRY              GRADE
--------------- --------------- --------------- -------------------- ----------
C00013          Holmes          London          UK                            2
C00024          Cook            London          UK                            2
C00015          Stuart          London          UK                            1
C00023          Karl            London          UK                            0

Go Top

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

CUST_CODE       CUST_NAME       CUST_CITY       CUST_COUNTRY              GRADE
--------------- --------------- --------------- -------------------- ----------
C00025          Ravindran       Bangalore       India                         2
C00017          Srinivas        Bangalore       India                         2
C00014          Rangarappa      Bangalore       India                         2
C00016          Venkatpati      Bangalore       India                         2
C00019          Yearannaidu     Chennai         India                         1
C00007          Ramanathan      Chennai         India                         1
C00005          Sasikant        Mumbai          India                         1
C00022          Avinash         Mumbai          India                         2

Go Top

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


CUST_CODE       CUST_NAME       CUST_CITY       CUST_COUNTRY              GRADE
--------------- --------------- --------------- -------------------- ----------
C00020          Albert          New York        USA                           3
C00002          Bolt            New York        USA                           3
C00010          Charles         Hampshair       UK                            3

Go Top

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

CUST_CODE       CUST_NAME       CUST_CITY       CUST_COUNTRY              GRADE
--------------- --------------- --------------- -------------------- ----------
C00001          Micheal         New York        USA                           2
C00020          Albert          New York        USA                           3
C00002          Bolt            New York        USA                           3
C00018          Fleming         Brisban         Australia                     2
C00010          Charles         Hampshair       UK                            3
C00003          Martin          Torento         Canada                        2

Go Top

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

CUST_CODE       CUST_NAME       CUST_CITY       CUST_COUNTRY              GRADE
--------------- --------------- --------------- -------------------- ----------
C00021          Jacks           Brisban         Australia                     1
C00004          Winston         Brisban         Australia                     1
C00006          Shilton         Torento         Canada                        1
C00012          Steven          San Jose        USA                           1
C00008          Karolina        Torento         Canada                        1

Go Top

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

CUST_CODE       CUST_NAME       CUST_CITY       CUST_COUNTRY              GRADE
--------------- --------------- --------------- -------------------- ----------
C00021          Jacks           Brisban         Australia                     1
C00004          Winston         Brisban         Australia                     1
C00006          Shilton         Torento         Canada                        1
C00012          Steven          San Jose        USA                           1
C00008          Karolina        Torento         Canada                        1

Go Top

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

CUST_CODE       CUST_NAME       CUST_CITY       CUST_COUNTRY              GRADE
--------------- --------------- --------------- -------------------- ----------
C00001          Micheal         New York        USA                           2
C00020          Albert          New York        USA                           3
C00002          Bolt            New York        USA                           3
C00018          Fleming         Brisban         Australia                     2
C00022          Avinash         Mumbai          India                         2
C00003          Martin          Torento         Canada                        2
C00009          Ramesh          Mumbai          India                         3
C00011          Sundariya       Chennai         India                         3

Go Top

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

CUST_CODE       CUST_NAME       CUST_CITY       CUST_COUNTRY              GRADE
--------------- --------------- --------------- -------------------- ----------
C00013          Holmes          London          UK                            2
C00001          Micheal         New York        USA                           2
C00020          Albert          New York        USA                           3
C00025          Ravindran       Bangalore       India                         2
C00024          Cook            London          UK                            2
C00015          Stuart          London          UK                            1
C00002          Bolt            New York        USA                           3
C00018          Fleming         Brisban         Australia                     2
C00021          Jacks           Brisban         Australia                     1
C00022          Avinash         Mumbai          India                         2
C00004          Winston         Brisban         Australia                     1
C00023          Karl            London          UK                            0
C00006          Shilton         Torento         Canada                        1
C00010          Charles         Hampshair       UK                            3
C00017          Srinivas        Bangalore       India                         2
C00012          Steven          San Jose        USA                           1
C00008          Karolina        Torento         Canada                        1
C00003          Martin          Torento         Canada                        2
C00009          Ramesh          Mumbai          India                         3
C00014          Rangarappa      Bangalore       India                         2
C00016          Venkatpati      Bangalore       India                         2
C00011          Sundariya       Chennai         India                         3

Go Top

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

CUST_CODE       CUST_NAME       CUST_CITY       CUST_COUNTRY              GRADE
--------------- --------------- --------------- -------------------- ----------
C00001          Micheal         New York        USA                           2
C00018          Fleming         Brisban         Australia                     2
C00022          Avinash         Mumbai          India                         2
C00003          Martin          Torento         Canada                        2

Go Top

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

CUST_CODE  CUST_NAME         CUST_CITY           CUST_COUNTRY      GRADE
---------- ----------------- ------------------ ---------------- ------------
C00001     Micheal           New York            USA                   2
C00018     Fleming           Brisban             Australia             2
C00022     Avinash           Mumbai              India                 2
C00003     Martin            Torento             Canada                2

Go Top

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

   ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE  CUST_CODE  AGENT_CODE
---------- ---------- -------------- --------- ---------- ----------
    200119       4000            700 16-SEP-08 C00007     A010
    200134       4200           1800 25-SEP-08 C00004     A005
    200108       4000            600 15-FEB-08 C00008     A004
    200132       4000           2000 15-AUG-08 C00013     A013
    200107       4500            900 30-AUG-08 C00007     A010
    200113       4000            600 10-JUN-08 C00022     A002

Go Top

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

   ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE  CUST_CODE  AGENT_CODE
---------- ---------- -------------- --------- ---------- ----------
    200117        800            200 20-OCT-08 C00014     A001
    200123        500            100 16-SEP-08 C00022     A002
    200116        500            100 13-JUL-08 C00010     A009
    200124        500            100 20-JUN-08 C00017     A007
    200126        500            100 24-JUN-08 C00022     A002
    200131        900            150 26-AUG-08 C00012     A012

Go Top

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

 

Looking for some other tutorial?