Basic SQL that Tableau writes

We’re going to walk through some examples of the SQL that Tableau writes back to the data source. For simplicity sake, we’re going to assume a single table.

Let’s start by looking at a very simple view:

Basic Select Statement — 1 measure

Let’s just bring in Sum([Sales]) into the view:

1 Dimension and 1 Measure — introducing Group By

In this example we have Sum([Sales]) on Columns and [Market] on rows. We have nothing on the marks card of filter shelf.

As a result, Tableau will ask the database to return the Sum of Sales, broken down by each Market. The result set from that query will be one row for each market and the corresponding sum of sales.

We’re going to iterate on that example.

2 Dimensions, 1 Measure

Note this would be the same SQL query as the view below. Despite the different presentation, the result set behind the viz is the same.

The viz below also has produces that same query. The granularity of the result set is still one row for every segment-market combo. In this example, having a dimension on the marks card doesn’t make our result set more granular because that same dimension is already on the row shelf.

Filters

Filtering using row-level fields

In the first example we’ll introduce filtering by dimension values. This will produce a where clause in the SQL. In this case we’re using 2 dimensions to drive our filter — that creates the AND statement. And within the “segment” dimension, we’re using 2 values, which creates the IN statement.

Filtering using aggregate measures

In this example, we’re filtering by Sum([Sales]). Certain Segments and Markets that don’t fall into our filter criteria are getting excluded from the result set. The WHERE clause is only used for non-aggregate (i.e. row-level) fields. This is where the HAVING clause comes into play.

Filtering using row-level measure fields

What if we wanted to filter out all unprofitable orders?

When you drag a measure to the filter shelf, you would select “all values” instead of an aggregation:

This is the equivalent of creating this calculation:

This creates a WHERE clause where Profit >=0.

Wrap up

If you understand how to use Tableau, you know how to write SQL. It’s just that you’re relying on Tableau to handle the syntax.

In all these examples, we’ve assumed we’re querying a single table. In more complex data models (i.e. star schemas or multi-fact tables), there is additional SQL that Tableau writes in order to stitch multiple tables together.