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.

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

    By contrast, N1QL permits data reshaping, by embedding statement-attributes in the desired result-object shape. 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.