Compare two tables and find the missing column using left join

Question

I wanted to compare the two tables employees and employees_a and find the missing columns in the table comployees_a.

 select a.Column_name,
From User_tab_columns a
LEFT JOIN User_tab_columns b
   ON upper(a.table_name) = upper(b.table_name)||'_A'
  AND a.column_name = b.column_name
Where upper(a.Table_name) = 'EMPLOYEES'
AND upper(b.table_name) = 'EMPLOYEES_A'
AND b.column_name is NULL
;

But this doesnt seems to be working. No rows are returned.

My employees table has the below columns

1. emp_name
2. emp_id
3. base_location
4. department
5. current_location
6. salary
7. manager

employees_a table has below columns

1. emp_name
2. emp_id
3. base_location
4. department
5. current_location

I want to find the rest two columns and add them into employees_a table.

I have more than 50 tables like this to compare them and find the missing column and add those columns into their respective "_a" table.

Answer 1

You do not need any joins and can use:

select 'ALTER TABLE EMPLOYEES_A ADD "'
         || Column_name || '" '
         || CASE MAX(data_type)
            WHEN 'NUMBER'
            THEN 'NUMBER(' || MAX(data_precision) || ',' || MAX(data_scale) || ')'
            WHEN 'VARCHAR2'
            THEN 'VARCHAR2(' || MAX(data_length) || ')'
            END
         AS sql
From   User_tab_columns
Where  Table_name IN ('EMPLOYEES', 'EMPLOYEES_A')
GROUP BY COLUMN_NAME
HAVING COUNT(CASE table_name WHEN 'EMPLOYEES'   THEN 1 END) = 1
AND    COUNT(CASE table_name WHEN 'EMPLOYEES_A' THEN 1 END) = 0;

Or, for multiple tables:

select 'ALTER TABLE ' || MAX(table_name) || '_A ADD "'
         || Column_name || '" '
         || CASE MAX(data_type)
            WHEN 'NUMBER'
            THEN 'NUMBER(' || MAX(data_precision) || ',' || MAX(data_scale) || ')'
            WHEN 'VARCHAR2'
            THEN 'VARCHAR2(' || MAX(data_length) || ')'
            END
         AS sql
From   User_tab_columns
Where  Table_name IN ('EMPLOYEES', 'EMPLOYEES_A', 'SOMETHING', 'SOMETHING_A')
GROUP BY
       CASE
       WHEN table_name LIKE '%_A'
       THEN SUBSTR(table_name, 1, LENGTH(table_name) - 2)
       ELSE table_name
       END,
       COLUMN_NAME
HAVING COUNT(CASE WHEN table_name NOT LIKE '%_A' THEN 1 END) = 1
AND    COUNT(CASE WHEN table_name LIKE '%_A'     THEN 1 END) = 0;

fiddle