Multi-Tables

There are times when the data you would like to access in a query isn’t stored in one table so it would be nice to be able to combine multiple tables into one in order to run queries against all the data in both at the same time. As we saw in the intermediate section of these docs, this kind of table combination can be accomplished with UNION, but there are instances in which using UNION is like using a sledgehammer to drive a nail. Enter data.world multi-tables. UNION combines queries; multi-tables combine tables. With multi-tables you can easily combine tables if they have the same columns and then run queries against the resulting table. With UNION, queries can be run against the individual tables before they are joined into one table by the UNION. With multi-tables, the tables are combined first and then can be used in queries just like any other table.

In our sample dataset there is one table for the sales pipeline, but there are many tables for orders: one for each month to be precise. To see all the order data for a quarter you could run the following query using multi-tables:

SELECT *
  FROM [apr_2017_orders, may_2017_orders, jun_2017_orders]
 ORDER BY order_value DESC

The first several rows of the resulting table would look like:

If the tables being selected for the query also have similar names (as the ones in the above query do) then you can use a standard Java regular expression to select which tables to included based on pattern-matching their names. For example, to select from all tables in the dataset that contain orders from 2017, the query would be written thusly:

SELECT *
  FROM [/.*2017_orders/]
 ORDER BY order_value DESC

The first several rows of the results from the query would be:

So far we have used a SELECT * clause to pick the columns returned from each of the tables. This syntax only works if all the columns have identical names. However tables grow and change with time, and you might find yourself wanting to compare tables that have some columns which have the same names and formats, and some that don’t match up. The only columns that can be returned in a multi-table query must match. If you want to include matching and non-matching columns in your query, you’ll need to use UNION. If there are matching and non-matching columns, but you only want to query on the ones that match you can specify them by name as in the following query:

SELECT sales_agent,
       account,
       order_value
  FROM [/.*2017_orders/](sales_agent, account, order_value)
 ORDER BY order_value DESC

Multi-tables have one more useful, unique function, and it’s that you can add a column to your results which contains the name of the table from which each row was selected. This column is called table_name.

SELECT sales_agent,
       account,
       order_value,
       table_name
  FROM [/.*2017_orders/] AS orders
 ORDER BY order_value DESC