POSTGIS error in getting proper intersected output (St_intersect, st_buffer)

Question

I need to get gid, area and geometry of table1 which are intersecting with 200 buffer of table2. I tried to structure the query. Please refer below:

create table SMI_1 as 
SELECT ST_Buffer(geom, 200) from table2;

create table SAH_2 as 
SELECT a.gid,a.shape_area, a.geom 
FROM table1 a 
INNER JOIN SMI_1 b ON ST_Intersects(a.geom, b.st_buffer);

With this I am not getting desired output. It is not clipping the features of table1 with the shape of the table2.

Answer 1

This query will return geometries from SAH_2 which are intersecting with geometries from SMI_1. If you need a geometry of this intersection you need to use ST_Intersection function:

create table SAH_2 as (
  WITH SMI_1 as ( 
    SELECT ST_Buffer(geom, 200) from table2)
  SELECT a.gid,a.shape_area, ST_Intersection(a.geom, b.st_buffer) as geom
  FROM table1 a 
  INNER JOIN SMI_1 b ON ST_Intersects(a.geom, b.st_buffer));