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-9 _in Operator

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

For each row in the driver table, the above 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-10 _between Operator

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

This code would access the table driver, checks for those drivers whose points between 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.