FULL OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join

Question

I have 3 tables like

table_4

• name
• phone
• email
• additionalColumns4

table_5

• phone
• additionalColumns5

table_6

• phone
• email
• additionalColumns6

Each table have different pieces of data.

I want to create one big table, with all possible data connected(one person is in 1 and 3 table , second just in 2 table etc) – and want to merge it in one big table like .

• table4.name
• table4.phone
• table4.email
• table4.additionalColumns4
• table5.phone
• table5.additionalColumns5
• table6.phone
• table6.email

• table6.additionalColumns6

  SELECT * FROM table_4 FULL JOIN table_5 ON table_5_field_s_mail_dmp_id =table_4_field_email_dmp_id FULL JOIN table_6 ON table_6_field_phone2_dmp_id =table_4_field_phone_dmp_id

works excellent,but

  SELECT
    *
  FROM
    table_4 
  FULL JOIN
    table_5
  ON
    table_5_field_s_mail_dmp_id =table_4_field_email_dmp_id
  FULL JOIN
    table_6
  ON
       table_6_field_phone2_dmp_id     =table_4_field_phone_dmp_id
    OR table_6_field_phone1_dmp_id     =table_4_field_phone_dmp_id
    OR table_6_field_workphone_dmp_id  =table_4_field_phone_dmp_id
    OR table_6_field_addphone_dmp_id   =table_4_field_phone_dmp_id
    OR table_6_field_email_dmp_id      =table_5_field_s_mail_dmp_id
    OR table_6_field_email_dmp_id      =table_4_field_email_dmp_id 

returns

Error: FULL OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join.

I can'not undestand this error and how to solve my problem

Added I checked

SELECT
  *
FROM
  table_4
FULL JOIN
  table_6
ON
  (table_6_field_phone2_dmp_id =table_4_field_phone_dmp_id
  OR table_6_field_phone1_dmp_id =table_4_field_phone_dmp_id
  )

And it's return the same error too. Big query not likes OR in full join conditions?

Answer 1

Write separate queries, then use COALESCE. (Credit goes to Ajit Deshmukh, who posted a nice example.)