Hi I am using select with union to select ftom 8 tables. These table have some common fields amd some unique fields to each table. I am getting errors when using union or union all due to different num of columns.

I would like to keep all columns from all the tables. Any ideas?

Thanks

CodePudding user response：

Kind of a long comment here. Honestly it sounds like you want a JOIN not a UNION. Unions add rows of data to existing columns and joins add columns of data horizontally.

But, for your UNION question, with differing tables there isn't an easy way to do something like SELECT * UNION SELECT *. You have to explicitly select all the columns.

You have two things to consider in this situation, both due to the fact that each select combined by unions must contain the same amount of columns.

1. Your top SELECT controls the names of the columns, so handle all your aliasing there
2. Each column that doesn't exist in a SELECT will need to be handled. Usually either by NULL or ''
   (ex. col1 is returned from the top SELECT but doesn't exist to be pulled from the second SELECT)

So you will end up with something like this:

SELECT col1 as A,
       NULL as B,
       NULL as C,
FROM table1
UNION
SELECT NULL,
       col2,
       NULL
FROM table2
UNION
SELECT NULL,
       NULL,
       col3
FROM table3

If you need further info then edit your question to include your current query, tables, and error

CodePudding user response：

Let's try to compare join and union from 3 tables with different number of rows and columns. Some are the same others are not.

SAMPLE DATA

WITH
    tbl_1 AS
        (
            Select 1 "ID_NUMBER", 'Name for ID 1' "NAME_VARCHAR", To_Date('27.11.1987', 'dd.mm.yyyy') "BIRTH_DATE" From Dual Union All
            Select 2 "ID_NUMBER", 'Name for ID 2' "NAME_VARCHAR", To_Date('29.05.1989', 'dd.mm.yyyy') "BIRTH_DATE" From Dual 
        ),
/*   ID_NUMBER NAME_VARCHAR  BIRTH_DATE
    ---------- ------------- ----------
             1 Name for ID 1 27-NOV-87  
             2 Name for ID 2 29-MAY-89    */
    tbl_2 AS
        (
            Select 1 "ID_NUMBER", 1 "PROPERTY_ID_NUMBER", 'France' "PROPERTY_STATE_VARCHAR", 150000 "PROPERTY_VALUE_NUMBER",  To_Date('12.11.2018', 'dd.mm.yyyy') "PROPERTY_BUYING_DATE" From Dual Union All
            Select 1 "ID_NUMBER", 2 "PROPERTY_ID_NUMBER", 'Spain'  "PROPERTY_STATE_VARCHAR", 120000 "PROPERTY_VALUE_NUMBER",  To_Date('10.03.2019', 'dd.mm.yyyy') "PROPERTY_BUYING_DATE" From Dual Union All
            Select 2 "ID_NUMBER", 1 "PROPERTY_ID_NUMBER", 'France' "PROPERTY_STATE_VARCHAR", 200000 "PROPERTY_VALUE_NUMBER",  To_Date('11.10.2020', 'dd.mm.yyyy') "PROPERTY_BUYING_DATE" From Dual 
        ),
/*   ID_NUMBER PROPERTY_ID_NUMBER PROPERTY_STATE_VARCHAR PROPERTY_VALUE_NUMBER PROPERTY_BUYING_DATE
    ---------- ------------------ ---------------------- --------------------- --------------------
             1                  1 France                                150000 12-NOV-18            
             1                  2 Spain                                 120000 10-MAR-19            
             2                  1 France                                200000 11-OCT-20          */
    tbl_3 AS
        (
            Select 1 "ID_NUMBER", 'France' "STATE_VARCHAR" From Dual Union All
            Select 2 "ID_NUMBER", 'Spain'   "STATE_VARCHAR" From Dual 
        )
/*   ID_NUMBER STATE_VARCHAR
    ---------- -------------
             1 France        
             2 Spain       */

1. UNION
   You have to ensure the same number of columns and the same datatypes from all unioned select statements. The first select can define it both. If there are null values selected by first select statements then cast it as apropriate datatype. You can also put in the same column data of different origin so, to make it clear, you could add the info of the origin for every row. Here is one of the options to select all of your data using union:

--  UNION
Select 'Table 1 - Name'             "DATA_SOURCE",  ID_NUMBER "ID_1", CAST(Null as Number(5)) "ID_2", NAME_VARCHAR "A_NAME",    BIRTH_DATE "A_DATE",      CAST(Null as Number(14,2)) "A_VALUE"  From tbl_1 Union All
Select 'Table 2 - Ownership',                       ID_NUMBER,        PROPERTY_ID_NUMBER,             PROPERTY_STATE_VARCHAR,   PROPERTY_BUYING_DATE,     PROPERTY_VALUE_NUMBER                 From tbl_2 Union All
Select 'Table 3 - Nationality',                     ID_NUMBER,        NULL,                           STATE_VARCHAR,            NULL,                     NULL                                  From tbl_3

Result

2. JOIN

--  JOIN
Select      t1.ID_NUMBER "ID", t1.NAME_VARCHAR "A_NAME", t1.BIRTH_DATE "BORN",
            t2.PROPERTY_ID_NUMBER "PROPERTY_ID", t2.PROPERTY_STATE_VARCHAR "PROPERTY_STATE", t2.PROPERTY_BUYING_DATE "BOUGHT", t2.PROPERTY_VALUE_NUMBER "PROPERTY_VALUE",
            t3.STATE_VARCHAR "NATIONALITY"
From        tbl_1 t1
Left Join   tbl_2 t2 ON(t2.ID_NUMBER = t1.ID_NUMBER)
Left Join   tbl_3 t3 ON(t3.ID_NUMBER = t1.ID_NUMBER)

Result

Depending on your data structure and what you want to get you will choose one way or another.