Inicio‎ > ‎SQL‎ > ‎SQL (english)‎ > ‎

T08 Temporary Tables

Temporary tables

We will take the freedom to consider temporary tables, not only the temporary tables in 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 MySQL does not need it, it seems convenient to use the AS reserved word to improve the understanding of the query. For example. the following statement generates a table with 2 columns from complete subqueries:

use tiendaonline;
select
    (select max(pvp) from articulo) as mart,
    (select max(precio) from linped) as mlinped;

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

select cod,pvp,
       pvp/(select max(pvp) from articulo)*100 as tpcpvp,
       pvp/(select max(precio) from linped)*100 as tpcprecio
from articulo
where pvp between 1000 and 1200; 

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:

select tv.cod,resolucion,tdt,tpcpvp
from tv,
  (select cod,pvp,
       pvp/(select max(pvp) from articulo)*100 as tpcpvp,
       pvp/(select max(precio) from linped)*100 as tpcprecio
   from articulo
   where pvp between 1000 and 1200) as calc 
where tv.cod=calc.cod;




Reversing 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:

select month(fecha) mes, count(*)
from pedido
where month(fecha) in (10,11,12)
group by month(fecha)
order by month(fecha);

What we want to get is that the months will be converted in columns. For that:

select
    (select count(*)
     from pedido
     where month(fecha) =10) as octubre,
    (select count(*)
     from pedido
     where month(fecha) =11) as noviembre,
    (select count(*)
     from pedido
     where month(fecha) =12) as diciembre;




Doing it more complex, we will see the how many times each article is asked for in each of these months:


select articulo, month(fecha) mes, count(*) veces
from pedido p join linped l on (p.numpedido=l.numpedido)
where month(fecha) in (10,11,12)
group by articulo,month(fecha)
order by articulo,month(fecha);


With the same objective as before, we will convert the months in columns using subqueries:

select distinct articulo,
    (select count(*)
     from pedido p join linped l on (p.numpedido=l.numpedido)
     where month(fecha) =10 and l.articulo=ll.articulo) octubre,
    (select count(*)
     from pedido p join linped l on (p.numpedido=l.numpedido)
     where month(fecha) =11 and l.articulo=ll.articulo) noviembre,
    (select count(*)
     from pedido p join linped l on (p.numpedido=l.numpedido)
     where month(fecha) =12 and l.articulo=ll.articulo) diciembre
from linped ll;


The same query but slightly changing the data sources, avoiding the use of  distinct. Note that now the subqueries are referencing to the table of the external select:

select cod as articulo,
  (select count(*)
   from pedido p join linped l on (p.numpedido=l.numpedido)
   where month(fecha) =10 and l.articulo=cod) octubre,
  (select count(*)
   from pedido p join linped l on (p.numpedido=l.numpedido)
   where month(fecha) =11 and l.articulo=cod) noviembre,
  (select count(*)
   from pedido p join linped l on (p.numpedido=l.numpedido)
   where month(fecha) =12 and l.articulo=cod) diciembre
from articulo
where cod in (select articulo
              from pedido p join linped l on (p.numpedido=l.numpedido)
              where month(fecha) in (10,11,12));

             

TEMPORARY tables


We use the previous sentence to create a temporary table. Remember that this table will disappear when the connection with the server is ended. Here it is defined from the query but these tables can be created as a normal table with the statement CREATE TABLE.

create temporary table mitabla
    select cod articulo,
      (select count(*)
       from pedido p join linped l on (p.numpedido=l.numpedido)
       where month(fecha) =10 and l.articulo=cod) octubre,
      (select count(*)
       from pedido p join linped l on (p.numpedido=l.numpedido)
       where month(fecha) =11 and l.articulo=cod) noviembre,
      (select count(*)
       from pedido p join linped l on (p.numpedido=l.numpedido)
       where month(fecha) =12 and l.articulo=cod) diciembre
    from articulo
    where cod in (select articulo
                  from pedido p join linped l on (p.numpedido=l.numpedido)
                  where month(fecha) in (10,11,12));

Now, we will query the table to check its content

select * from mitabla where octubre>0 and noviembre>0;


We do not take care of deleting the table because the system will do it.