Common Filtering Scenarios

In this section we cover the basic yet most common scenarios of filtering table rows by column values.

Logical Column

We wish to filter by a logical column to return the rows where the logical column is TRUE.

SELECT *
FROM refcon.dataset.table_1
WHERE col_4;

Here is how this works:

  • A column of a logical data type (comprised of TRUE and/or FALSE values) can be used in a WHERE clause.
  • Rows, where the passed column (in this example: col_4) takes a value of TRUE, are retained.

Compare to Scaler

We wish to obtain (filter) the rows of a table where the values of a particular column meet a condition that involves comparison to a scaler value.

In this example, we wish to obtain the rows of the table table_1 where the numerical column col_6 takes a value greater than 0.

SELECT *
FROM refcon.dataset.table_1
WHERE col_6 > 0;

Here is how this works:

  • The logical expression we wish to carry out is comparing the value of a column col_6 to a scaler value 0 via col_6 > 0.
  • We use this after the WHERE clause which will only keep rows that match this condition.
  • In this example we used a greater than comparison >. In general, we can use any comparison operation on numerical columns including smaller than <, equal ==, greater than or equal >=, or smaller than or equal <=. See Numerical Operations for more details.
  • In addition to applying comparison operations to numerical columns, we can apply the appropriate comparison operations to non-numeric columns i.e. String, Bool, and Date-Time. We cover those in their respective operations section.

Compare Columns

We wish to obtain rows where a comparison involving two of the table’s columns evaluates to TRUE.

In this example, we wish to return rows where the value of one column col_6 is larger than the value of another column col_3.

SELECT *
FROM refcon.dataset.table_1
WHERE col_6 > col_3;

Here is how this works:

  • This works similarly to the Compare to Scaler filtering example described above.
  • We change the condition to compare between two columns instead of a scalar and a column.

Complement

We wish to obtain rows where a condition is not satisfied.

In this example, we wish to obtain rows where the value of a string column col_10 is neither ‘S’ nor ‘XS’.

SELECT *
FROM refcon.dataset.table_1
WHERE NOT col_10 IN ('S', 'XS');

Here is how this works:

  • We use the IN operator to check whether each value of col_10 is either ‘S’ or ‘XS’. We cover IN in detail in General Operations.
  • The expression col_10 IN ('S', 'XS') returns TRUE for rows where the value of col_10 is either ‘S’ or ‘XS’.
  • We are looking for rows where column col_10 is neither ‘S’ nor ‘XS’. In other words, we are looking for the * complement* of the condition col_10 IN ('S', 'XS'). In SQL, we obtain that by using the complement operator NOT which we add right before the condition.
SQL
I/O