How to make a query to a table generated by a select?

Question

As an example:

SELECT name, address, phone FROM USER;

Generates the table

name | address | phone 
...  |   ...   |  ...

How can i use this table generated to make another query to it?

For example:

SELECT count(address) FROM (SELECT name, address, phone, from user);

I know it's not the best example, but is there any way to do this?

Answer 1

Use the same syntax but give your derived table an alias

SELECT count(subadd.address) 
FROM (SELECT name, address, phone, from user) as subadd;

Answer 2

The best way is to create a view:

CREATE VIEW my_view AS SELECT name, address, phone FROM USER;

Then you can sue it as a table:

SELECT count(address) FROM my_view;

Answer 3

You could use temporary tables:

create temporary table t select name, address, phone;
select count(address) from t;

Few characteristics:
- the table will only be accessible by the current thread,
- the table will be dropped when the current thread will be finished
- creating such table requires appriopriate privileges

Answer 4

I'd avoid using temporary tables. I'd probably go with creating a view. However, I'd run an explain to see what is faster if speed is needed.