Google Sheets QUERY: Complements

Boolean Values

A database may also contain columns of boolean values TRUE or FALSE. This type of value is generally returned by a function, a formula, or a checkbox.

In this new database, columns F and G contain boolean values.

To retrieve for example all data from users registered for project 1 (F = TRUE) and project 2 (G = checked/TRUE), enter:

=QUERY('DB 2'!A1:H15,"SELECT * WHERE F = TRUE AND G = TRUE")

Or to find out for example the number of registrants for each project depending on the other, enter:

=QUERY('DB 2'!A1:H15,"SELECT F, COUNT(A) GROUP BY F PIVOT G")

Empty Cells

An empty cell can be considered as an empty text value ('') or null (NULL).

To retrieve all the data from users who did not provide an email address (H), enter:

=QUERY('DB 2'!A1:H15,"SELECT * WHERE H = ''")

The test = '' can be replaced by IS NULL for an identical result:

=QUERY('DB 2'!A1:H15,"SELECT * WHERE H IS NULL")

Although with empty cells, one or the other solution can be used, note that an empty text value is not equal to the NULL value. In some particular cases, only one of the solutions will be usable.

An example (just to illustrate this difference) of a request testing the division by 0 to return the data whose rank (D) or the number of messages (E) is equal to 0:

=QUERY('DB 2'!A1:H15,"SELECT * WHERE A / D / E IS NULL")

Range returned by a function

In all the examples in this course, the data range (containing the database) was a range of cells.

In some cases, this data range is not a range of cells but a range returned by another function. In this case, it is not possible to use the column letters in the query.

If the range specified in the QUERY function is not a range of cells, enter Col followed by the column number (instead of using the column letters):

=QUERY(UNIQUE('DB 2'!C1:H15),"SELECT Col1, Col3 WHERE Col4 = TRUE")

Order

Finally, remember the order to follow to build a query: