Value Comparison Operators

Value comparison operators are primarily used to compare 2 values, one produced by the left operand and another from the right operand. If any operand returns more than one item, an error is raised. If both operands return the empty sequence, the operands are considered equal (true will be returned if the operator is =, <=, or >=). If only one of the operands returns empty, the result of the comparison is false unless the operator is !=. If an operand returns NULL, the result of the comparison expression is also NULL. Otherwise, the result is a boolean value.

Example 1: Select the full name and routing of all male passengers.

SELECT fullname, bag.bagInfo[].routing 
FROM BaggageInfo bag 
WHERE gender="M"

Explanation: Here the data is filtered based on gender. The value comparison operator "=" is used to filter the data.

Output:

{"fullname":"Lucinda Beckman","routing":"SFO/IST/ATH/JTR"}
{"fullname":"Adelaide Willard","routing":"GRU/ORD/SEA"}
{"fullname":"Raymond Griffin","routing":"MSQ/FRA/HKG"}
{"fullname":"Zina Christenson","routing":"MIA/LAX/MEL"}
{"fullname":"Dierdre Amador","routing":"JFK/MAD"}
{"fullname":"Birgit Naquin","routing":"JFK/MAD"}
{"fullname":"Lorenzo Phil","routing":["SFO/IST/ATH/JTR","SFO/IST/ATH/JTR"]}
{"fullname":"Gerard Greene","routing":"SFO/IST/ATH/JTR"}
{"fullname":"Adam Phillips","routing":"MIA/LAX/MEL"}
{"fullname":"Fallon Clements","routing":"MXP/CDG/SLC/BZN"}
{"fullname":"Lisbeth Wampler","routing":"LAX/TPE/SGN"}
{"fullname":"Teena Colley","routing":"MSQ/FRA/HKG"}

You can rewrite this query with a "!=" comparison operator. To get the details of all male passengers, your query can filter data where gender is not "F". This is valid only with the assumption that there can only be two values in the column gender which is "F" and "M".

SELECT fullname,bag.bagInfo[].routing 
FROM BaggageInfo bag 
WHERE gender!="F";

Example 2: Fetch the passenger name and routing details of passengers with ticket numbers greater than 1762360000000.

SELECT fullname, ticketNo, 
bag.bagInfo[].tagNum,bag.bagInfo[].routing
FROM BaggageInfo bag 
WHERE ticketNo > 1762360000000

Explanation: You need the details of passengers whose ticket number is greater than the given value. You use the ">" operator to filter the data.

Output:

{"fullname":"Adelaide Willard","ticketNo":1762392135540,"tagNum":"17657806224224","routing":"GRU/ORD/SEA"}
{"fullname":"Raymond Griffin","ticketNo":1762399766476,"tagNum":17657806243578,"routing":"MSQ/FRA/HKG"}
{"fullname":"Zina Christenson","ticketNo":1762390789239,"tagNum":"17657806228676","routing":"MIA/LAX/MEL"}
{"fullname":"Bonnie Williams","ticketNo":1762397286805,"tagNum":"17657806216554","routing":"SFO/ORD/FRA"}
{"fullname":"Joanne Diaz","ticketNo":1762383911861,"tagNum":"17657806292518","routing":"MIA/LAX/MEL"}
{"fullname":"Kendal Biddle","ticketNo":1762377974281,"tagNum":"17657806296887","routing":"JFK/IST/VIE"}
{"fullname":"Dierdre Amador","ticketNo":1762376407826,"tagNum":"17657806240229","routing":"JFK/MAD"}
{"fullname":"Birgit Naquin","ticketNo":1762392196147,"tagNum":"17657806240229","routing":"JFK/MAD"}

Example 3: Select all bag tag numbers originating from SFO/transit through SFO.

SELECT bag.bagInfo[].tagNum,
bag.bagInfo[].flightLegs[].fltRouteSrc 
FROM BaggageInfo bag
WHERE bag.bagInfo[].flightLegs[].fltRouteSrc=any "SFO"

Explanation: You fetch the tag number of bags that either originate from SFO or pass through SFO. Though you are using the value comparison operator =, since the flightLegs is an array, the left operand of comparison operator = is a sequence with more than one item. That is the reason to use the sequence operator any in addition to the value comparison operator =. Else you get the following error.

Error handling command SELECT bag.bagInfo[].tagNum,bag.bagInfo[].flightLegs[].fltRouteSrc 
FROM BaggageInfo bag WHERE bag.bagInfo[].flightLegs[].fltRouteSrc= "SFO": 
Error: at (3, 6) The left operand of comparison operator = is a sequence with more than one items. 
Comparison operators cannot operate on sequences of more than one items.

Output:

{"tagNum":"17657806240001","fltRouteSrc":"SFO"}
{"tagNum":"17657806216554","fltRouteSrc":"SFO"}
{"tagNum":["17657806240001","17657806340001"],"fltRouteSrc":["SFO","SFO"]}
{"tagNum":"1765780626568","fltRouteSrc":"SFO"}
{"tagNum":"17657806247861","fltRouteSrc":"SFO"}

Example 4: Select all bag tag numbers which did not originate from JFK.

SELECT bag.bagInfo[].tagNum,
bag.bagInfo[].flightLegs[0].fltRouteSrc 
FROM BaggageInfo bag
WHERE bag.bagInfo.flightLegs[0].fltRouteSrc!=ANY "JFK"

Explanation: The assumption here is that the first record of the flightLegs array has the details of the source location. You fetch the tag number of bags that did not originate from JFK and so using a != operator here. Though you are using the value comparison operator !=, since the flightLegs is an array, the left operand of the comparison operator != is a sequence with more than one item. That is the reason to use the sequence operator any in addition to the value comparison operator !=. Else you get the following error.

Error handling command SELECT bag.bagInfo[].tagNum,bag.bagInfo[].flightLegs[0].fltRouteSrc 
FROM BaggageInfo bag WHERE bag.bagInfo.flightLegs[0].fltRouteSrc!="JFK": 
Failed to display result set: Error: at (2, 0) The left operand of comparison operator != is a sequence with 
more than one items. Comparison operators cannot operate on sequences of more than one items.

Output:

{"tagNum":"17657806240001","fltRouteSrc":["SFO","IST","ATH"]}
{"tagNum":"17657806224224","fltRouteSrc":["GRU","ORD"]}
{"tagNum":"17657806243578","fltRouteSrc":["MSQ","FRA"]}
{"tagNum":"1765780623244","fltRouteSrc":["MXP","CDG","SLC"]}
{"tagNum":"17657806228676","fltRouteSrc":["MIA","LAX"]}
{"tagNum":"17657806234185","fltRouteSrc":["MEL","LAX"]}
{"tagNum":"17657806255507","fltRouteSrc":["MXP","CDG","SLC"]}
{"tagNum":"17657806292229","fltRouteSrc":["LAX","TPE"]}
{"tagNum":"17657806255823","fltRouteSrc":["MSQ","FRA"]}
{"tagNum":"17657806247861","fltRouteSrc":["SFO","IST","ATH"]}
{"tagNum":"17657806299833","fltRouteSrc":["YYZ","HKG"]}
{"tagNum":"17657806288937","fltRouteSrc":["MIA","LAX"]}
{"tagNum":"17657806216554","fltRouteSrc":["SFO","ORD"]}
{"tagNum":["17657806240001","17657806340001"],"fltRouteSrc":["SFO","IST","ATH","SFO","IST","ATH"]}
{"tagNum":"1765780626568","fltRouteSrc":["SFO","IST","ATH"]}
{"tagNum":"17657806255240","fltRouteSrc":["MIA","LAX"]}
{"tagNum":"17657806232501","fltRouteSrc":["BZN","SEA","CDG"]}
{"tagNum":"17657806292518","fltRouteSrc":["MIA","LAX"]}