Previous Next JavaScript must be enabled to correctly display this content

## WHERE Clause

The WHERE clause filters the rows coming from the FROM clause, returning the rows satisfying a given condition.

Syntax

    where_clause ::= WHERE expression

Semantics

For each context row, the expression in the WHERE clause is evaluated. The result of this expression must have type BOOLEAN ?. If the result is false, or empty, or NULL, the row is skipped; otherwise the row is passed on to the next clause.

Example 6-7 Fetch the list of Female passengers from the airline application

    SELECT
    fullname,
    ticketNo
    FROM Baggageinfo
    WHERE gender="F"

Explanation: In the above query, you list the name and ticket details of the female passengers from the `Baggageinfo` table. The WHERE clause filters the rows based on the `gender` field of each record. You fetch all the records that hold the entry `'F'` in the `gender` field.

Output:

    {"fullname":"Adelaide Willard","ticketNo":1762392135540}
    {"fullname":"Elane Lemons","ticketNo":1762324912391}
    {"fullname":"Michelle Payne","ticketNo":1762330498104}
    {"fullname":"Doris Martin","ticketNo":1762355527825}
    {"fullname":"Rosalia Triplett","ticketNo":1762311547917}
    {"fullname":"Zulema Martindale","ticketNo":1762340579411}
    {"fullname":"Joanne Diaz","ticketNo":1762383911861}
    {"fullname":"Kendal Biddle","ticketNo":1762377974281}
    {"fullname":"Mary Watson","ticketNo":1762340683564}

Example 6-8 Fetch the list of passengers from the airline application whose destination station is MEL

    SELECT
    fullname,
    ticketNo,
    s.bagInfo.routing[] AS ROUTING
    FROM Baggageinfo s
    WHERE regex_like(s.bagInfo.routing[], ".*/MEL")

Explanation: In an airline application, you can fetch the list of passengers bound toward specific destination stations. The routing field in the `bagInfo` array holds the routing details of the passengers in the `Baggageinfo` table. The routing information is stored in the format `Source/Transit/Destination` airport codes.

In this query, you fetch the details of the passengers whose destination station is MEL. You use the `regex_like` function to achieve a pattern match to the destination airport code, MEL. Since you are only looking for a specific destination station, use the combination of the period (. ) metacharacter and greedy quantifier (* ) to allow zero or more occurrences of any source and transit airport code. For more details on the `regex_like` function, see Regular Expression Conditions.

Output:

    {"fullname":"Zulema Martindale","ticketNo":1762340579411,"ROUTING":"MIA/LAX/MEL"}
    {"fullname":"Adam Phillips","ticketNo":1762344493810,"ROUTING":"MIA/LAX/MEL"}
    {"fullname":"Zina Christenson","ticketNo":1762390789239,"ROUTING":"MIA/LAX/MEL"}
    {"fullname":"Joanne Diaz","ticketNo":1762383911861,"ROUTING":"MIA/LAX/MEL"}

Example 6-9 Fetch the list of passengers carrying more than one piece of luggage in the airline application

    SELECT
    fullname,
    ticketNo
    FROM Baggageinfo s
    WHERE EXISTS s.baginfo[1]

Explanation: In this query, you list the name and ticket details of the passengers traveling with more than one piece of luggage. The `bagInfo` array holds the information on all the luggage owned by a passenger. If a passenger owns more than one piece of luggage, there will be more than one element in the `bagInfo` array. You use the EXISTS operator in the WHERE clause to determine whether or not a second element exists in the `baginfo` array.

Output:

    {"fullname":"Lorenzo Phil","ticketNo":1762320369957}

Alternatively, you can use the `size()` built-in function to determine the size of the `baginfo` array, which is the number of bags owned by a passenger. You then use the value comparison operator '>' to check if the return value exceeds 1. You will get the same output as above. For more details on the `size()` function, see Functions on Complex Values.

    SELECT
    fullname,
    ticketNo
    FROM Baggageinfo s
    WHERE size(s.baginfo) > 1