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