SQL to return column names from 2 tables which have matching data

Question

I am working on a data validation project . I have 2 tables ( like given below) , I should run a query, and the query should return Table1.P1 and Table2.P1 are matching and Table1.P3 and Table2.P3 are matching .

+++++++++++++++++++++++++++++++        +++++++++++++++++++++++++++++++
+            TABLE1           +        +           TABLE2            +
+---------+---------+---------+        +---------+---------+---------+
| P1      | P2      | P3      |        + P1      | P2      | P3      |
+---------+---------+---------+        +---------+---------+---------+
| A       | 1A      | AA      |        | A       | 1A      | AA      |
| B       | 2A      | BB      |        | B       | 2A      | BB      |
| C       | 3A      | CC      |        | C       | 3A      | CC      |
| D       | 4A      | DD      |        | D       | 4B      | DD      |
| E       | 5A      | EE      |        | E       | 5B      | EE      |
+---------+---------+---------+        +---------+---------+---------+


+      output      +           
+---------+--------+        
| Table   |column  |
|Name     |name    |
+---------+---------
| Tabl1   | P1     | 
| Tabl2   | P3     | 
+------------------+        

My query should return Matching columns P1 and P3My query should return Matching columns P1 and P3

Expected Output:

Tablename

Answer 1

You know, if you don't have much data (i.e. the data fits in a varchar column), then you can use listagg():

select (case when t1.p1list = t2.p1list then 'same' end) as p1,
       (case when t1.p2list = t2.p2list then 'same' end) as p2,
       (case when t1.p3list = t2.p3list then 'same' end) as p3       
from (select listagg(p1, ',') within group (order by p1) as p1list,
             listagg(p2, ',') within group (order by p2) as p2list,
             listagg(p3, ',') within group (order by p3) as p3list
      from table1
     ) t1 cross join
     (select listagg(p1, ',') within group (order by p1) as p1list,
             listagg(p2, ',') within group (order by p2) as p2list,
             listagg(p3, ',') within group (order by p3) as p3list
      from table2
     ) t2

Answer 2

I don't know how to nicely do it in SQL, so I'll use plain and stupid PL/SQL. Have a look, use it if you think it helps.

Test case:

SQL> create table table1
  2  (p1 varchar2(10),
  3   p2 varchar2(10),
  4   p3 varchar2(10));

Table created.

SQL> create table table2
  2  (p1 varchar2(10),
  3   p2 varchar2(10),
  4   p3 varchar2(10));

Table created.

SQL> insert into table1
  2    select 'A', '1A', 'AA' from dual union
  3    select 'B', '2A', 'BB' from dual union
  4    select 'D', '4A', 'DD' from dual;

3 rows created.

SQL> insert into table2
  2    select 'A', '1A', 'AA' from dual union
  3    select 'B', '2A', 'BB' from dual union
  4    select 'D', '4B', 'DD' from dual;

3 rows created.

SQL>

A procedure which checks whether two columns match. Probably it could be written in a dynamic manner, using a loop through USER_TAB_COLUMNS, but I'm not going to do it right now - I might, if necessary. Or, even better, do it yourself.

As I've said: plain and stupid:

• compare number of rows in both tables
• intersect should return all rows
• minus shouldn't return anything

I compared the first two columns (P1 and P2) only.

SQL> create or replace procedure p_match as
  2    l_cnt_1     number;
  3    l_cnt_2     number;
  4    l_intersect number;
  5    l_minus_12  number;
  6    l_minus_21  number;
  7  begin
  8    -- Column P1
  9    select count(*) into l_cnt_1 from table1;
 10    select count(*) into l_cnt_2 from table2;
 11
 12    select count(*) into l_intersect
 13    from (select p1 from table1 intersect select p1 from table2);
 14
 15    select count(*) into l_minus_12
 16    from (select p1 from table1 minus select p1 from table2);
 17
 18    select count(*) into l_minus_21
 19    from (select p1 from table2 minus select p1 from table1);
 20
 21    if l_cnt_1 = l_cnt_2 and
 22       l_intersect = l_cnt_1 and
 23       l_minus_12 = 0 and
 24       l_minus_21 = 0
 25    then
 26       dbms_output.put_line('Column P1 matches');
 27    else
 28       dbms_output.put_line('Column P1 does not match');
 29    end if;
 30
 31    -- Column P2
 32
 33    select count(*) into l_cnt_1 from table1;
 34    select count(*) into l_cnt_2 from table2;
 35
 36    select count(*) into l_intersect
 37    from (select p2 from table1 intersect select p2 from table2);
 38
 39    select count(*) into l_minus_12
 40    from (select p2 from table1 minus select p2 from table2);
 41
 42    select count(*) into l_minus_21
 43    from (select p2 from table2 minus select p2 from table1);
 44
 45    if l_cnt_1 = l_cnt_2 and
 46       l_intersect = l_cnt_1 and
 47       l_minus_12 = 0 and
 48       l_minus_21 = 0
 49    then
 50       dbms_output.put_line('Column P2 matches');
 51    else
 52       dbms_output.put_line('Column P2 does not match');
 53    end if;
 54  end;
 55  /

Procedure created.

SQL> set serveroutput on;
SQL> begin
  2    p_match;
  3  end;
  4  /
Column P1 matches
Column P2 does not match

PL/SQL procedure successfully completed.

SQL>

Answer 3

I think you seems to want exists :

select t1.*
from tabel1 t1
where exists (select 1 from table2 t2 where t2.p1 = t1.p1);
or
. . . 
where exists (select 1 from table2 t2 where t2.p3 = t1.p3);

If so, then just modify the subquery as

where exists (select 1 from table2 t2 where t2.p1 = t1.p1 and t2.p3 = t1.p3);