In SQL Data Analysis, you can run queries on the data sets that you are using. For example, you may have retrieved a data set from a SQL Server database, and another data set from a PostgreSQL database. You can run a query to retrieve data from these 2 data sets as if they were tables in a database.
To run an internal query, first click on the Run SQL query item in the main window. In our example, we have already ran and use data sets from SQL Server and PostgreSQL databases.
Select the Query data sets item on the Database Connections panel on the left. There are 3 ‘tables’ we can query from – ds1, ds2, and ds3. You can write a query to retrieve rows across these 3 tables e.g.
The new data set can then be used in your analysis tables and pivot tables.
The SQLite SQL syntax is used for the internal query. Thus, you have access to almost all the standard SQL92 syntax, except for RIGHT OUTER JOIN and FULL OUTER JOIN.
What are the limitations and implications of not being able to use RIGHT OUTER JOIN and FULL OUTER JOIN in SQL Data Analysis when running internal queries?
To simulate a RIGHT OUTER JOIN, reverse the order of the tables and use a LEFT OUTER JOIN instead. To simulate a FULL OUTER JOIN, use 2 LEFT OUTER JOIN queries and UNION to combine both queries’ results.