A newer version of this documentation is available.

View Latest

N1QL versus SQL

  • concept
    +
    The most important difference between N1QL and SQL is the data model. Other notable differences relate to the projection, selection, and filtering of data.

    Data Model

    In a relational database, data is constrained to tables with uniform structure. The following example consists of two tables, Employee and Employers, with Name as the primary key.

    EMPLOYEE:
    Name  | SSN | Wage
    -----------------------------------------------------------------------
    Jamie | 234 | 123
    Steve | 123 | 456
    
    SCHEMA:
    Name -> String of width 100
    SSN -> Number of width 9
    Wage -> Number of width 10
    
    EMPLOYERS:
    -----------------------------------------------------------------------
     Name_Key  | Company   | Start | End
     Jamie     | Yahoo     | 2005  | 2006
     Jamie     | Oracle    | 2006  | 2012
     Jamie     | Couchbase | 2012  | NULL

    By contrast, N1QL handles data as free-form documents, gathered into large collections called keyspaces. There is no uniformity; nor is there any logical proximity shared by objects of the same data shape within the keyspace. The relational data from the above example might therefore appear as follows:

     (HRData keyspace)
     {
         'Name': 'Jamie'
         'SSN': 234
         'Wage': 123
         'History':
          [
           ['Yahoo', 2005, 2006],
           ['Oracle', 2006, 2012],
         ]
     },
    
     {
         'Name': Steve
         'SSN':  123,
         'Wage': 456,
     }

    Data Projection

    In a relational database, when an SQL query is run, a set of rows is returned; each row consisting of one or more columns, and the columns being the same for each row. A header can be retrieved, to obtain metadata about each column. The SQL projection may also contain arbitrary expressions, which are returned as fields in the result set.

    Query:
    SELECT Name, Company
        FROM Employee, Employers
        WHERE Name_Key = Name
    Result:
    	Name  | Company
    	----------------
    	Jamie | Oracle
    	Jamie | Yahoo
    	Jamie | Couchbase
    	----------------

    With N1QL, an arbitrary expression in the projection may contain collection operators or construction operators, which enable you to reshape the data in ways that go beyond merely adding an extra field. Like SQL, N1QL allows fields to be renamed using the AS keyword.

    Query:
     SELECT Name, History, {'FullTime': true} AS 'Status'
          FROM HRData
    Result:
    	{
    	'Name': 'Jamie',
    	'History':
    		[
    		['Yahoo', 2005, 2006],
    		['Oracle', 2006, 2012],
    		['Couchbase', 2012, null]
    		],
    	'Status': { 'FullTime': true }
    	}
    	{
    	'Name': 'Steve',
    	'Status': { 'FullTime': true }
    	}

    Data Selection

    In N1QL, the FROM clause is used to select between data sources (keyspaces). If HRData is a keyspace, the following statement selects the Name attribute from all documents in the HRData keyspace that have a Name attribute defined.

    SELECT Name FROM HRData

    Each document can also regard itself as a data source, and run a query over its nested elements. Such nested elements are addressed using the dot (.) operator to descend a level, and the square-bracket ( [ ] ) operator to index into an array element.

      SELECT FullTime FROM HRData.Status
    {
         'FullTime': true
    }

    The selected fields can also be renamed using the AS operator, as in SQL:

    SELECT firstjob FROM HRData.History[0] AS firstjob
    {
         'firstjob': ['Yahoo', 2005, 2006]
    }
    
    SELECT firstjob[2] FROM HRData.History[0] AS firstjob
    {
         'firstjob[2]': 2006
    }

    Data Filtering

    N1QL supports the WHERE clause, but with slight differences from SQL.

    Similarly to SQL, the dot ( . ) and the square bracket ( [] ) operators can be used to access nested elements as they are used in SELECT clauses.

    N1QL data can be irregularly shaped: hence, undefined values are recognized as distinct from null. N1QL provides a complementary set of operators like IS MISSING, in addition to standard operators like IS NULL. New conversions, for example from non-zero integer-values to Boolean value true, are also supported.

    Most standard SQL functions (for example, LOWER()) are defined. In addition to the standard filtering-predicates, N1QL provides new operators to work with arrays in documents: ANY, SOME, and EVERY. ANY and SOME evaluate a condition for each element, and return true if any element meets the condition. EVERY also evaluates a condition for each element; except that it returns true only if all elements matched the condition.