SQL AND, OR, NOT.
Selection with logical AND, OR, NOT
-
SQL provides the ability to select the table rows using a combination
of logical expressions. The operators AND, OR,
NOT or a combination of these are used to achieve this.
-
AND and OR are used in a logical comparison of two conditions.
-
The AND operator returns true if both the first condition and the second condition are true.
-
The OR operator returns true if either the first condition or the second condition are true.
-
The NOT operator is a negation so AND NOT operator returns true if the first condition is true and the second condition is false.
-
The NOT operator is a negation so OR NOT operator returns true if the first condition is true or the second condition is false.
AND Example:
MySQL, Oracle, SQL server, PostgreSQL:
select isbn_no "ISBN for order < $100 >= $50" ,
ITEM_QTY * price "Order in $"
from order_books
where ITEM_QTY * price < 100 AND ITEM_QTY * price >= 50 ;
The result should be:
ISBN for order < $100 >= $50
Order in $
0201703092
78
1861006314
58
0764557599
84
0471777781
64
OR Example:
MySQL, Oracle, SQL server, PostgreSQL:
select isbn_no "ISBN for order > $100 OR < $50" ,
ITEM_QTY * price "Order in $"
from order_books
where ITEM_QTY * price > 100 OR ITEM_QTY * price < 50 ;
The result should be:
ISBN for order > $100 OR < $50
Order in $
0764557599
42
1861002025
190
0672325764
49
0764579088
140
0201703092
390
1861002025
190
1861006314
29
Example:
MySQL, Oracle, SQL server, PostgreSQL:
select isbn_no "ISBN for order < $100 AND NOT < $70" ,
ITEM_QTY * price "Order in $"
from order_books
where ITEM_QTY * price < 100 AND NOT ITEM_QTY * price < 70 ;
The result should be:
ISBN for order < $100 AND NOT < $70
Order in $
0201703092
78
0764557599
84
Example:
MySQL, Oracle, SQL server, PostgreSQL:
select isbn_no "ISBN for order > $100 OR NOT > $30" ,
ITEM_QTY * price "Order in $"
from order_books
where ITEM_QTY * price > 100 OR NOT ITEM_QTY * price > 30 ;
The result should be:
ISBN for order > $100 OR NOT > $30
Order in $
1861002025
190
0764579088
140
0201703092
390
1861002025
190
1861006314
29
AND Example:
MySQL, Oracle, SQL server, PostgreSQL:
select isbn_no "ISBN for order < $100 >= $50" ,
ITEM_QTY * price "Order in $"
from order_books
where ITEM_QTY * price < 100 AND ITEM_QTY * price >= 50 ;
The result should be:
ISBN for order < $100 >= $50 | Order in $ |
---|---|
0201703092 | 78 |
1861006314 | 58 |
0764557599 | 84 |
0471777781 | 64 |
OR Example:
MySQL, Oracle, SQL server, PostgreSQL:
select isbn_no "ISBN for order > $100 OR < $50" ,
ITEM_QTY * price "Order in $"
from order_books
where ITEM_QTY * price > 100 OR ITEM_QTY * price < 50 ;
The result should be:
ISBN for order > $100 OR < $50 | Order in $ |
---|---|
0764557599 | 42 |
1861002025 | 190 |
0672325764 | 49 |
0764579088 | 140 |
0201703092 | 390 |
1861002025 | 190 |
1861006314 | 29 |
Example:
MySQL, Oracle, SQL server, PostgreSQL:
select isbn_no "ISBN for order < $100 AND NOT < $70" ,
ITEM_QTY * price "Order in $"
from order_books
where ITEM_QTY * price < 100 AND NOT ITEM_QTY * price < 70 ;
The result should be:
ISBN for order < $100 AND NOT < $70 | Order in $ |
---|---|
0201703092 | 78 |
0764557599 | 84 |
Example:
MySQL, Oracle, SQL server, PostgreSQL:
select isbn_no "ISBN for order > $100 OR NOT > $30" ,
ITEM_QTY * price "Order in $"
from order_books
where ITEM_QTY * price > 100 OR NOT ITEM_QTY * price > 30 ;
The result should be:
ISBN for order > $100 OR NOT > $30 | Order in $ |
---|---|
1861002025 | 190 |
0764579088 | 140 |
0201703092 | 390 |
1861002025 | 190 |
1861006314 | 29 |
© 2010 by Finnesand Data.
All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the
correctness in the pages or source codes.
The risk of using this web-site pages or any program
codes from this website is entirely at the individual user.