IN, NOT IN and BETWEEN QBE Operators

Learn to use QBE operators _in, _nin, and _between to filter specific data from the car racing dataset through simple examples.

You can use the _in and the _nin operator to compare and check if the predicate is present or otherwise in the specified array.

Example 3-14 _in Operator

SELECT JSON_SERIALIZE(data PRETTY) AS data FROM GRAPHQL('
    query {
        driver (
            check: {
                name: {_in: ["Oscar Piastri", "Max Verstappen"]}
            }
        ){
            id: driver_id
            name
            points
        }
    }
');

For each row in the driver table, the preceding example would check if the name field is one of ["Oscar Piastri", "Max Verstappen"]. If the outcome is TRUE, then the corresponding driver details, in this case, id, name, and points are fetched:

DATA
--------------------------------------------------------------------------------
{
  "id" : 105,
  "name" : "Max Verstappen",
  "points" : 456
}

{
  "id" : 102,
  "name" : "Oscar Piastri",
  "points" : 384
}


2 rows selected.

_between QBE operator checks if the predicate is in between the two values specified in the array:

Example 3-15 _between Operator

SELECT JSON_SERIALIZE(data PRETTY) AS data FROM GRAPHQL('
    query {
        driver (
            check: {
                points: {_between: [300, 400]}
            }
        ){
            id: driver_id
            name
            points
        }
    }
');

This code would access the table driver, checks for those drivers whose pointsbetween 300 and 400, and retrieves their id, name and points:

DATA
--------------------------------------------------------------------------------
{
  "id" : 102,
  "name" : "Oscar Piastri",
  "points" : 384
}

{
  "id" : 103,
  "name" : "Charles Leclerc",
  "points" : 312
}

{
  "id" : 104,
  "name" : "Carlos Sainz Jr.",
  "points" : 340
}


3 rows selected.