SQL Logical Operators
Logical Operators
The Logical operators are those that are true or false. They return 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 returns true when both expressions are true... |
| OR | Logical OR compares between two Booleans as expression and returns 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.... |
| Special operators | ||
| Operator | Description | Operates on |
|---|---|---|
| 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.... | Any set of values of the same datatype |
| 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.... | Numeric, characters, or datetime values |
| 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.... | A value to a list or a single - columns set of values |
| 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.... | A value to a list or a single - columns set of values |
| SOME | SOME compare a value to each value in a list or results from a query and evaluate to true if the result of an inner query contains at least one row... | A value to a list or a single - columns set of values |
| 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'... | Table |
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(/). |
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 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 :
SQL Code:
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
Relational Algebra Expression:
Relational Algebra Tree:
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 :
SQL Code:
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
Relational Algebra Expression:
Relational Algebra Tree:
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 :
SQL Code:
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 .....
Relational Algebra Expression:
Relational Algebra Tree:
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 :
SQL Code:
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
Relational Algebra Expression:
Relational Algebra Tree:
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 :
SQL Code:
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
Relational Algebra Expression:
Relational Algebra Tree:
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 :
SQL Code:
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
Relational Algebra Expression:
Relational Algebra Tree:
SQL Logical NOT AND comparison operator
In the following 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 :
SQL Code:
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
Relational Algebra Expression:
Relational Algebra Tree:
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 :
SQL Code:
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
Relational Algebra Expression:
Relational Algebra Tree:
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 :
SQL Code:
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
Relational Algebra Expression:
Relational Algebra Tree:
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 :
SQL Code:
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
Relational Algebra Expression:
Relational Algebra Tree:
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 :
SQL Code:
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
Relational Algebra Expression:
Relational Algebra Tree:
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 :
SQL Code:
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
Relational Algebra Expression:
Relational Algebra Tree:
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 :
SQL Code:
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
Relational Algebra Expression:
Relational Algebra Tree:
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 :
SQL Code:
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
Relational Algebra Expression:
Relational Algebra Tree:
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 :
SQL Code:
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
200107 4500 900 30-AUG-08 C00007 A010
200113 4000 600 10-JUN-08 C00022 A002
Relational Algebra Expression:
Relational Algebra Tree:
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 :
SQL Code:
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
Relational Algebra Expression:
Relational Algebra Tree:
Practice SQL Exercises
- SQL Exercises, Practice, Solution
- SQL Retrieve data from tables [33 Exercises]
- SQL Boolean and Relational operators [12 Exercises]
- SQL Wildcard and Special operators [22 Exercises]
- SQL Aggregate Functions [25 Exercises]
- SQL Formatting query output [10 Exercises]
- SQL Quering on Multiple Tables [8 Exercises]
- FILTERING and SORTING on HR Database [38 Exercises]
- SQL JOINS
- SQL SUBQUERIES
- SQL Union[9 Exercises]
- SQL View[16 Exercises]
- SQL User Account Management [16 Exercise]
- Movie Database
- BASIC queries on movie Database [10 Exercises]
- SUBQUERIES on movie Database [16 Exercises]
- JOINS on movie Database [24 Exercises]
- Soccer Database
- Introduction
- BASIC queries on soccer Database [29 Exercises]
- SUBQUERIES on soccer Database [33 Exercises]
- Hospital Database
- Employee Database
- More to come!
Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.
SQL: Tips of the Day
Drop all tables whose names begin with a certain string
DECLARE @cmd varchar(4000)
DECLARE cmds CURSOR FOR
SELECT 'drop table [' + Table_Name + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Name LIKE 'prefix%'
OPEN cmds
WHILE 1 = 1
BEGIN
FETCH cmds INTO @cmd
IF @@fetch_status != 0 BREAK
EXEC(@cmd)
END
CLOSE cmds;
DEALLOCATE cmds
Ref: https://bit.ly/3t83Goj
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises