SQL BETWEEN, NOT BETWEEN

Selection with BETWEEN, NOT BETWEEN

  • The BETWEEN operator is used to select values within a range.
  • Be aware of that the selection is based on two values where both are included.
    BETWEEN Example:
    MySQL, Oracle, SQL server, PostgreSQL:
    select isbn_no "ISBN where order is between $100 and $50" ,
      ITEM_QTY * price "Order in $"
      from order_books
      where ITEM_QTY * price BETWEEN 50 and 100;
    (it is the same as : WHERE ITEM_QTY * price <= 100 AND ITEM_QTY * price >= 50 ;)
    The result should be:
    ISBN where order is between $100 and $50 Order in $
    0201703092 78
    1861006314 58
    0764557599 84
    0471777781 64
    NOT BETWEEN Example:
    MySQL, Oracle, SQL server, PostgreSQL:
    select isbn_no "ISBN where order is not between $300 and $50" ,
      ITEM_QTY * price "Order in $"
      from order_books
      where ITEM_QTY * price NOT BETWEEN 50 and 300;
    (it is the same as : WHERE NOT (ITEM_QTY * price <= 300 AND ITEM_QTY * price >= 50);)
    The result should be:
    ISBN where order is not between $300 and $50 Order in $
    0764557599 42
    0672325764 49
    0201703092 390
    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.