If you are a data analyst, there’s a good chance that you’ve heard of SQL. Short for Structured Query Language, SQL is a powerful language that allows you to manipulate and analyze data from different databases. It is also one of the most widely used languages in the field of data analytics as it can handle complex queries and calculations with ease.
Understanding SQL and Its Importance in Data Analytics
Harnessing the Flexibility of Custom SQL in Tableau
But what if you want to use SQL in Tableau? Tableau has many built-in data connectors that let you connect to different data sources and explore your data visually. But sometimes you may need more flexibility and control over how you access and prepare your data for analysis. That’s where Custom SQL comes in handy. It is a feature that lets you write your own SQL statements to query data from multiple sources within Tableau. With Custom SQL, you can extract, filter, join, union and alias data according to your needs.
Let me show you how.
Section 1: “SELECT … FROM …” Statement
“SELECT … FROM” is the main statement of any SQL query. It allows you to specify which columns you want to retrieve from a table when you have a large number of columns in a table. For example:
In the above example, we retrieve CustomerName field, OrderAmount field and OrderDate field from the Orders table.
Section 2: “WHERE” Clause
The “WHERE” clause allows you to filter data based on specific conditions. It enables you to extract subsets of data that meet certain criteria. For example:
In this example, we retrieve the same columns from the same Orders table as example in last section, but only for the rows where order amount is greater than 50 and the customer name is either Mike, lily or Peter.
Section 3: “JOIN” Clause
The “JOIN” clause allows you to combine data from two or more tables based on a related column between them. Let’s look at an example:
In this example, we join the “Orders” and “Customers” tables based on the CustomerID column, allowing us to retrieve the OrderID and CustomerName fields from both tables.
Section 4: “UNION ALL” Clause
The “UNION ALL” clause in SQL is used to combine the result of sets of two or more SELECT statements into a single result set. It includes all rows from each SELECT statement, including duplicates. It requires the tables to be combined have same number of columns and have same column names. Here is an example:
If any column is missing in one of the tables. For example, when Employees_ B table doesn’t have Department column, but we still want to union Employees_A and Employees_B. Then we can insert NULL value as a placeholder to make the union happen. Below is the sample code:
Section 5: Aliases
Aliases enable you to provide alternative names for fields in your query results. This is very useful for renaming columns or making them more descriptive. In the last section, we alias NULL as “Department” is one of example, aliases are used quite often when implementing UNION which requires column have exact same names. We can also use aliases in SELECT statement:
In the five sections above, we have seen some commonly used SQL statements, clauses to access, filter, and combine data in diverse sources. Understanding those fundamentals of SQL allows for greater flexibility in data integration and analysis within Tableau.
Hope this blog helps you to quickly master custom SQL.
