Save the intermediate result of SQL query

Question

I am wondering if there is any way to save the intermediate result or tables in SQL. For example assume you have two different SQL statements that in the first statement you join two tables, then you want to see how many rows the resulting table has. I know there are many ways to do this but I am interested in seeing how this can be done sequentially. Consider the following example:

select * from order_table left join customer_table on order_table.id = customer_table.id

Then I want to see count of number of rows (as an easy example)

select count(*) from table 

But I do not know what this table should be. How may I save the result of above query in some logical table or how to refer to what was created before in SQL.

Answer 1

You can use WITH like below:

WITH resultTable as ( select * from order_table left join customer_table on order_table.id = customer_table.id )

select count(*) from resultTable

Answer 2

For this particular example you can simply wrap the original query in a sub-query:

select count(*)
from (
    select *
    from order_table
    left join customer_table on order_table.id = customer_table.id
) as x

If you want to store the result in a physical table (temporary or permanent) then the procedure varies for each rdbms. In SQL Server for example you would use SELECT INTO:

select * 
into #temp_table
from order_table
left join customer_table on order_table.id = customer_table.id