CODEWITHSUNDEEP
mysqlpostgresqljoin
speedvees
speedvees

Reputation: 117

PostgreSQL join two tables without multiplying columns

I would like to join two tables, which has many columns. It looks something like this:

-----------------------------------------------
|   gid   |  geom   |  ogc_fid   | nuts |.... |
|    1    |  ...    |    ...     | ...  |.... |
|    2    |  ...    |    ...     | ...  |.... |
|    3    |  ...    |    ...     | ...  |.... |
|    4    |  ...    |    ...     | ...  |.... |

And i would like to join it with other table, that has more columns, but many of them are the same as in the first one, so it looks like this:

 _________________________________________________________
|   gid   |  geom   |  ogc_fid   | nuts |  x  |  y  |  z  |
|    1    |  ...    |    ...     | ...  |.... |  .  |  .  |
|    2    |  ...    |    ...     | ...  |.... |  .  |  .  |
|    3    |  ...    |    ...     | ...  |.... |  .  |  .  |
|    4    |  ...    |    ...     | ...  |.... |  .  |  .  |

After join it suppose to look the same as the second one table. But with all records of the first one and the second one.

I tried to do this:

select *from "migration"."X" as tab1
full outer join "migration"."Y" as tab2 on tab1.gid = tab2.gid;

But my result is this:

-------------------------------------------------------------------------------------------------------
|   gid   |  geom   |  ogc_fid   | nuts |.... |  gid   |  geom   |  ogc_fid   | nuts |  x  |  y  |  z  |
|    1    |  ...    |    ...     | ...  |.... |   1    |  ...    |    ...     | ...  |.... |  .  |  .  |
|    2    |  ...    |    ...     | ...  |.... |   2    |  ...    |    ...     | ...  |.... |  .  |  .  |
|    3    |  ...    |    ...     | ...  |.... |   3    |  ...    |    ...     | ...  |.... |  .  |  .  |
|    4    |  ...    |    ...     | ...  |.... |   4    |  ...    |    ...     | ...  |.... |  .  |  .  |

I tried many different queries, but didn't get result i want. Can somebody help me?

Upvotes: 0

Views: 578

Answers (3)

markusk
markusk

Reputation: 6677

You can use NATURAL to ensure columns with the same name are only listed once:

SELECT * FROM "migration"."X" NATURAL FULL OUTER JOIN "migration"."Y";

Quoting the PostgreSQL documentation for The FROM Clause:

NATURAL is a shorthand form of USING: it forms a USING list consisting of all column names that appear in both input tables. As with USING, these columns appear only once in the output table. If there are no common column names, NATURAL JOIN behaves like JOIN ... ON TRUE, producing a cross-product join.

Note that if there are rows where values don't match for the columns with the same name, NATURAL FULL OUTER JOIN will make those appear multiple times in the result. E.g., if x.geom is different from y.geom for x.gid = y.gid = 1, you will get two rows in the result, one with the values from x and another with the values from y. So you should only use this approach if you know that the shared columns will have matching values for any given gid.

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133380

If you want avoid the select for the same column between the two tables the don't use * (* is an alias fo all columns) but use explict column name in select eg:

Select tab1.gid,  tab1.geom,  tab1.ogc_fid ,tab1.nuts, tab2.geom,  tab2.ogc_fid  
from "migration"."X" as tab1
full outer join "migration"."Y" as tab2 on tab1.gid = tab2.gid;

Upvotes: 0

Nidhi257
Nidhi257

Reputation: 977

try below Query:

select tab1.*, tab2.x,tab2.y,tab2.z from "migration"."X" as tab1
full outer join "migration"."Y" as tab2 on tab1.gid = tab2.gid;

Hope this help.

Upvotes: 1

Related Questions