Temporary Tables

We will take the freedom to consider temporary tables, not only the temporary tables in MariaDB/MySQL, but alsoe the  subqueries used as columns in a select clause or as tables in a from clause.

A temporary table is defined as a table that is created and used in a limited context, being a concrete sentence in SQL (in the generic case of subqueries) or a session or connection . The TEMPORARY tables, for example, are objects that automatically disappear when the user session is ended. A subquery is accessible only when the statement is being executed. In this lesson, we are only treating the subquery cases, the TEMPORARY tables does not need more explanation.

Subqueries as calculated columns

A query can be used as another column in any query, only with the precaution of giving it a name. Although MariaDB/MySQL does not need it, it seems convenient to use the AS reserved word to improve the understanding of the query. 

Notice this two separate queries:

The following statement generates a table with 2 columns from previous queries:

In this case the subqueries are used to perform operations over them and within the query over a set of rows:

Invert a table

What in another systems is known as PIVOT columns (in SQL Server, for example) consists on building columns from the information contained in the rows. Here, we will calculate how many orders/requests have been done in october, november and december of any year. Before, we will check the data we have:

Subqueries in the from

Sometimes it is necessary to include a subquery as another table to link in other query in a upper order. Again, we must name this temporary table in order to be able to make a reference to it. In this case, we take profit of the previous query to use it in a more complex one:

Common Table Expressions

Common Table Expressions (CTE) was introduced in the SQL standard relatively recently (SQL:1999) and is not supported by all systems, although the most important ones have been incorporating it.

Basically, we can understand them as a query that obtains a temporary result on which we can make subsequent queries.

WITH nameCTE as (select...)
select ... from nameCTE... 

After the reserved word WITH we must give a name to the CTE and then, in parentheses, define the query that will act as a result or temporary table. The following query is the one that makes use of the CTE. 

We make use of the previous query again, but making use of a CTE that we will call "calc".

After defining the CTE we make a "query of queries", each column is a subquery on the CTE filtering by the corresponding month.

CTEs have other more sophisticated uses such as recursion, but here we will simply keep in mind that it is another way to simplify complex queries, based on making a first query on which to obtain the result we are really looking for.

TEMPORARY tables

We can also define temporary tables as such, that is, tables that are created at a given time because we need them for later queries, but are not limited to a single use. This table will be available as another table and we will be able to use it whenever we want. The only limitation is that it will disappear automatically when we close the session, when we disconnect.

We take advantage of the previous query to create a temporary table. Here it is defined from a query but these tables can be created like any other with the CREATE TABLE command. As this is the creation of an object, we must be in a database where we have the necessary permissions to do so.

CREATE TEMPORARY TABLE mytable

SELECT article, month(date) month, COUNT(*) times

FROM orrder p

JOIN linorder l ON (p.numOrder=l.numOrder)

WHERE month(date) in (10,11,12) AND article < 'A0031'

GROUP BY article, month(date);

We do not have to worry about eliminating it since the system will do it when the connection is closed, or when it considers it appropriate.

One last example

Let's combine several of the techniques with subqueries shown here. Let's get how many times (we don't care in what quantity) each item is requested in October, November or December of any year.

We have limited the query to the first items (code less than A0031) to reduce the result we are going to work on.

In the CTE there is the previously mentioned query, and the following query is used to format the result in columns by months. From the above result we see that item A0023 has been ordered once in October and once in November. By displaying it as columns get (A0023, 1, 1, null), the same information, but tabulated by month. 

Notice that the second query renames "mycte" as "t2" in the from. Let's say it queries "mycte" and gets t2.article, i.e. it traverses "mycte" to retrieve the article codes resulting from that query. Article by article, the 3 subqueries that define the columns of each month filter again "mycte" looking for the row of that article and of a particular month. For this was to rename the CTE, in order to be able to link the subqueries to each article run.

We can say that "mycte" is used in 4 places in the general query, but we have only defined it once.

Obviously, when formatting in this way, there are certain months in which an item has not been ordered, hence the appearance of null values. If we do not like the aspect obtained, we can make use of the IFNULL() function.