outer join returns multiple copy of join columns

Question

When I do an outer join on mssql, the columns I join on do not combine.

Here is my code:

select top 10 *  from customer_behaviour_1P2014  full outer join
customer_behaviour_2P2014 on customer_behaviour_1P2014.customer_identifier = customer_behaviour_2P2014.customer_identifier full outer join
customer_behaviour_3P2014 on customer_behaviour_2P2014.customer_identifier = customer_behaviour_3P2014.customer_identifier

This returns 3 columns labeled customer_identifier, instead of 1.

What am I doing wrong?

If it makes any difference I made customer identifier a index in each table.

Answer 1

You are selecting all columns from all 3 tables, and each table has a customer_identifier column (deduced from the ON clauses).

Each customer_identifier column in the result is from a different table. The values will be the same when matched or NULL when no row matches.

Specify an explicit column list instead of * to avoid repeating the values. Instead of the 3 separate customer_identifier columns, use a COALESCE function to return the first non-NULL value:

SELECT <other-columns>,
COALESCE(customer_behaviour_1P2014.customer_identifier, customer_behaviour_2P2014.customer_identifier, customer_behaviour_3P2014.customer_identifier) AS customer_identifier
FROM ...