Skip to main content
Engineering LibreTexts

10.2.1: SELECT with WHERE criteria

  • Page ID
    93694
  • \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)

    \( \newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\)

    ( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\)

    \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)

    \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\)

    \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)

    \( \newcommand{\Span}{\mathrm{span}}\)

    \( \newcommand{\id}{\mathrm{id}}\)

    \( \newcommand{\Span}{\mathrm{span}}\)

    \( \newcommand{\kernel}{\mathrm{null}\,}\)

    \( \newcommand{\range}{\mathrm{range}\,}\)

    \( \newcommand{\RealPart}{\mathrm{Re}}\)

    \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)

    \( \newcommand{\Argument}{\mathrm{Arg}}\)

    \( \newcommand{\norm}[1]{\| #1 \|}\)

    \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)

    \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\AA}{\unicode[.8,0]{x212B}}\)

    \( \newcommand{\vectorA}[1]{\vec{#1}}      % arrow\)

    \( \newcommand{\vectorAt}[1]{\vec{\text{#1}}}      % arrow\)

    \( \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vectorC}[1]{\textbf{#1}} \)

    \( \newcommand{\vectorD}[1]{\overrightarrow{#1}} \)

    \( \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} \)

    \( \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} \)

    \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)

    Sometimes you might want to focus on a portion of the Publishers table, such as only publishers that are in Vancouver. In this situation, you would use the SELECT statement with the WHERE criterion, i.e., WHERE city = ‘Vancouver’.

    These first two examples illustrate how to limit record selection with the WHERE criterion using BETWEEN. Each of these examples give the same results for store items with between 20 and 50 items in stock.

    Example #1 uses the quantity, qty BETWEEN 20 and 50.

    SELECT StorID, qty, TitleID
    FROM Sales
    WHERE qty BETWEEN 20 and 50  (includes the 20 and 50)

    Example #2, on the other hand, uses qty >=20 and qty <=50 .

    SELECT StorID, qty, TitleID
    FROM Sales
    WHERE qty >= 20 and qty  <= 50

    Example #3 illustrates how to limit record selection with the WHERE criterion using NOT BETWEEN.

    SELECT StorID, qty, TitleID
    FROM Sales
    WHERE qty NOT BETWEEN 20 and 50

    The next two examples show two different ways to limit record selection with the WHERE criterion using IN, with each yielding the same results.

    Example #4 shows how to select records using province= as part of the WHERE statement.

    SELECT *
    FROM Publishers
    WHERE province = ‘BC’ OR province = ‘AB’ OR province = ‘ON’

    Example #5 select records using province IN as part of the WHERE statement.

    SELECT *
    FROM Publishers
    WHERE province IN (‘BC’, ‘AB’, ‘ON’)

    The final two examples illustrate how NULL and NOT NULL can be used to select records. For these examples, a Books table (not shown) would be used that contains fields called Title, Quantity, and Price (of book). Each publisher has a Books table that lists all of its books.

    Example #6 uses NULL.

    SELECT price, title
    FROM Books
    WHERE price IS NULL

    Example #7 uses NOT NULL.

    SELECT price, title
    FROM Books
    WHERE price IS NOT NULL

    10.2.1: SELECT with WHERE criteria is shared under a not declared license and was authored, remixed, and/or curated by LibreTexts.

    • Was this article helpful?