How to put join result into a new table-CodePudding

How do I put the result of this join query into a new table on the same schema?

SELECT census2010.district,census2010.total,census2010.male, 
    census2010.female,census2010.region, districts.area 
FROM ghana.census2010 LEFT JOIN ghana.districts 
ON census2010.region ILIKE districts.region

CodePudding user response：

Considering the above code, use the CREATE TABLE ___ AS ( ) command. like this

CREATE TABLE ghana.TableName AS (
    SELECT census2010.district,census2010.total,census2010.male,
            census2010.female,census2010.region, districts.area  
    FROM ghana.census2010 
        LEFT JOIN ghana.districts  ON census2010.region ILIKE districts.region)

The result should be a table in the same database schema

CodePudding user response：

If you want your join table to evolve after new entries are added or removed from your two starting tables, creating a view is the appropriate choice.

CREATE VIEW view_name AS
SELECT census2010.district, census2010.total, census2010.male,
       census2010.female, census2010.region, districts.area  
FROM ghana.census2010 
LEFT JOIN ghana.districts 
ON census2010.region ILIKE districts.region);

Then, you'll be able to run queries like the following ones

SELECT * FROM view_name;

in order to obtain the always up-to-date joined table. You can remove a view like any other table by running:

DROP VIEW view_name;