2

I have two tables. Both have lot of columns. Now I have a common column called ID on which I would join.

Now since this variable ID is present in both the tables if I do simply this

select a.*,b.*
from table_a as a
left join table_b as b on a.id=b.id

This will give an error as id is duplicate (present in both the tables and getting included for both).

I don't want to write down separately each column of b in the select statement. I have lots of columns and that is a pain. Can I rename the ID column of b in the join statement itself similar to SAS data merge statements?

I am using Postgres.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Baktaawar
  • 7,086
  • 24
  • 81
  • 149

1 Answers1

3

Postgres would not give you an error for duplicate output column names in a plain SELECT, but some clients do. (Duplicate names are not very useful either way.)

Use the USING clause as join condition to fold the two join columns into one:

SELECT *
FROM  tbl_a  a
LEFT  JOIN tbl_b b USING (id);

While you join the same table (self-join) there will be more duplicate column names. The query would make hardly any sense to begin with. This starts to make sense for different tables. Like you stated in your question to begin with: I have two tables ...

To avoid all duplicate column names, you have to list them in the SELECT clause explicitly - possibly dealing out column aliases to get both instances with different names.

Or you can use a NATURAL join - if that fits your unexplained use case:

SELECT *
FROM  tbl_a  a
NATURAL LEFT JOIN tbl_b b;

This joins on all columns that share the same name and folds those automatically - exactly the same as listing all common column names in a USING clause. You need to be aware of rules for possible null values ...

Details in the manual.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I used using but did select a.*,b.* and it gave the same error – Baktaawar Nov 10 '15 at 21:53
  • @Manish: With `SELECT a.*, b.*` you would instruct Postgres to output the column twice anyway. Also .. I added more above. – Erwin Brandstetter Nov 10 '15 at 21:54
  • 1
    @Manish: if you use `SELECT *` with `USING` the common fields (ID in this case) will be merged and there will only be a single `ID` in the result set. [SQLFiddle here](http://sqlfiddle.com/#!15/43ced7/7). (This is also an advertisement for why using a common field name such as `ID` in many tables is a Bad Idea. Better to use a constructed field name such as `ID_TBL_A`, `ID_TBL_B`, etc). Best of luck. – Bob Jarvis - Слава Україні Nov 10 '15 at 22:06
  • For anyone wondering, USING and ON are mutually exclusive join constraints, at least in SQLite. So, for example, you **cannot** do something like `JOIN USING(id) ON time BETWEEN date1 AND date2`. You would have to resort to `JOIN ON a.id=b.id AND time BETWEEN date1 AND date2` and then you still have OP's problem. – Attila the Fun Sep 28 '22 at 07:49