## SQL – Methods of Reformatting into Equivalent Forms 5

2 12 2009

December 2, 2009

A couple of years ago someone posted in the comp.databases.oracle.database Usenet group the following challenge:

Here’s one of my personal favorites and generally a good interview question too.

Assume two identical tables, one named “A” the other “B” with identical column definitions. Assume that some rows in “A” are duplicated in “B” and some in “B” are duplicated in “A” but each table contains rows unique to that table.

Write a single SELECT statement that will retrieve all of the rows from table “A” that are unique to “A”, all the rows from “B” that are unique to “B” and label each retrieved row with the name of the table in which it was found.

Have fun (and yes there are multiple solutions).

This message thread, like several others, generated suggestions from several people.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

EscVector offered the following solution:

```CREATE TABLE A
(    COL1 CHAR(4),
COL2 NUMBER,
COL3 VARCHAR2(10));

begin
for x in 1..10
loop
insert into a values ('ab'||x, x,'NONUNIQUE');
end loop;
end;
/

create table B as select * from a;

begin
for x in 1..10
loop
insert into a values ('a'||x, x,'UNIQUE');
end loop;
end;
/

begin
for x in 1..10
loop
insert into b values ('b'||x, x,'UNIQUE');
end loop;
end;
/

commit;

(select a.col1 ,a.col2 ,a.col3, 'TABA'  from a minus select
b.col1,b.col2,b.col3, 'TABA' from b )
union
(select b.col1,b.col2,b.col3 ,'TABB' from b minus select
a.col1,a.col2,a.col3 ,'TABB' from a );```

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I offered the following solutions:

```CREATE TABLE TABLE_A (
COL1 VARCHAR2(20),
COL2 VARCHAR2(20),
COL3 VARCHAR2(20));

CREATE TABLE TABLE_B (
COL1 VARCHAR2(20),
COL2 VARCHAR2(20),
COL3 VARCHAR2(20));

INSERT INTO TABLE_A VALUES ('TEST1A','TEST1B','TEST1C');
INSERT INTO TABLE_A VALUES ('TEST2A','TEST2B','TEST2C');
INSERT INTO TABLE_A VALUES ('TEST3A','TEST3B','TEST3C');
INSERT INTO TABLE_A VALUES ('TEST4A','TEST4B','TEST4C');
INSERT INTO TABLE_A VALUES ('TEST5A','TEST5B','TEST5C');

INSERT INTO TABLE_B VALUES ('TEST1A','TEST1B','TEST1C');
INSERT INTO TABLE_B VALUES ('TEST2A','TEST1B','TEST1C');
INSERT INTO TABLE_B VALUES ('TEST3A','TEST3B','TEST3C');
INSERT INTO TABLE_B VALUES ('TEST5A','TEST5B','TEST5C');

SELECT DISTINCT
NVL(A.COL1,B.COL1) COL1,
NVL(A.COL2,B.COL2) COL2,
NVL(A.COL3,B.COL3) COL3,
NVL2(A.COL1,'TABLE A','TABLE B') FROM_TABLE
FROM
TABLE_A A
FULL OUTER JOIN
TABLE_B B ON A.COL1=B.COL1 AND A.COL2=B.COL2 AND A.COL3=B.COL3
WHERE
(A.COL1 IS NULL OR B.COL1 IS NULL)
OR (A.COL2 IS NULL OR B.COL2 IS NULL)
OR (A.COL3 IS NULL OR B.COL3 IS NULL);

COL1    COL2    COL3    FROM_TABLE
TEST2A  TEST2B  TEST2C  TABLE A
TEST4A  TEST4B  TEST4C  TABLE A
TEST2A  TEST1B  TEST1C  TABLE B

Extra credit:
SELECT DISTINCT
NVL(A.COL1,B.COL1) COL1,
NVL(A.COL2,B.COL2) COL2,
NVL(A.COL3,B.COL3) COL3,
NVL2(A.COL1,'TABLE A','TABLE B') FROM_TABLE
FROM
TABLE_A A
FULL OUTER JOIN
TABLE_B B ON A.COL1=B.COL1 AND A.COL2=B.COL2 AND A.COL3=B.COL3
WHERE
UTL_I18N.RAW_TO_CHAR(DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW(NVL(A.COL1,'1'),
'AL32UTF8'),1)||DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW(NVL(A.COL2,'1'),
'AL32UTF8'),1)||DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW(NVL(A.COL3,'1'), 'AL32UTF8'),1),'AL32UTF8')<> UTL_I18N.RAW_TO_CHAR(DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW(NVL(B.COL1,'1'),
'AL32UTF8'),1)||DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW(NVL(B.COL2,'1'),
'AL32UTF8'),1)||DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW(NVL(B.COL3,'1'), 'AL32UTF8'),1),'AL32UTF8');

COL1    COL2    COL3    FROM_TABLE
TEST2A  TEST2B  TEST2C  TABLE A
TEST4A  TEST4B  TEST4C  TABLE A
TEST2A  TEST1B  TEST1C  TABLE B

Here are a couple more solutions, using more than one SELECT in a SQL
statement:
SELECT
A.COL1,
A.COL2,
A.COL3,
'TABLE A' FROM_TABLE
FROM
TABLE_A A,
(SELECT
A.COL1,
A.COL2,
A.COL3
FROM
TABLE_A A
INTERSECT
SELECT
B.COL1,
B.COL2,
B.COL3
FROM
TABLE_B B) M
WHERE
A.COL1=M.COL1(+)
AND A.COL2=M.COL2(+)
AND A.COL3=M.COL3(+)
AND M.COL1 IS NULL
UNION ALL
SELECT
B.COL1,
B.COL2,
B.COL3,
'TABLE B' FROM_TABLE
FROM
TABLE_B B,
(SELECT
A.COL1,
A.COL2,
A.COL3
FROM
TABLE_A A
INTERSECT
SELECT
B.COL1,
B.COL2,
B.COL3
FROM
TABLE_B B) M
WHERE
B.COL1=M.COL1(+)
AND B.COL2=M.COL2(+)
AND B.COL3=M.COL3(+)
AND M.COL1 IS NULL;

WITH M AS (
SELECT
A.COL1,
A.COL2,
A.COL3
FROM
TABLE_A A
INTERSECT
SELECT
B.COL1,
B.COL2,
B.COL3
FROM
TABLE_B B)
SELECT
A.COL1,
A.COL2,
A.COL3,
'TABLE A' FROM_TABLE
FROM
TABLE_A A,
M
WHERE
A.COL1=M.COL1(+)
AND A.COL2=M.COL2(+)
AND A.COL3=M.COL3(+)
AND M.COL1 IS NULL
UNION ALL
SELECT
B.COL1,
B.COL2,
B.COL3,
'TABLE B' FROM_TABLE
FROM
TABLE_B B,
M
WHERE
B.COL1=M.COL1(+)
AND B.COL2=M.COL2(+)
AND B.COL3=M.COL3(+)
AND M.COL1 IS NULL;

SELECT
A.COL1,
A.COL2,
A.COL3,
'TABLE A' FROM_TABLE
FROM
TABLE_A A
WHERE
(A.COL1,A.COL2,A.COL3) NOT IN (
SELECT DISTINCT
B.COL1,
B.COL2,
B.COL3
FROM
TABLE_B B)
UNION ALL
SELECT
B.COL1,
B.COL2,
B.COL3,
'TABLE B' FROM_TABLE
FROM
TABLE_B B
WHERE
(B.COL1,B.COL2,B.COL3) NOT IN (
SELECT DISTINCT
A.COL1,
A.COL2,
A.COL3
FROM
TABLE_A A);

SELECT
A.COL1,
A.COL2,
A.COL3,
'TABLE A' FROM_TABLE
FROM
TABLE_A A,
(SELECT DISTINCT
B.COL1,
B.COL2,
B.COL3
FROM
TABLE_B B) B
WHERE
A.COL1=B.COL1(+)
AND A.COL2=B.COL2(+)
AND A.COL3=B.COL3(+)
AND B.COL3 IS NULL
UNION ALL
SELECT
B.COL1,
B.COL2,
B.COL3,
'TABLE B' FROM_TABLE
FROM
TABLE_B B,
(SELECT DISTINCT
A.COL1,
A.COL2,
A.COL3
FROM
TABLE_A A) A
WHERE
B.COL1=A.COL1(+)
AND B.COL2=A.COL2(+)
AND B.COL3=A.COL3(+)
AND A.COL3 IS NULL;

SELECT
COL1,
COL2,
COL3,
FROM_TABLE
FROM
(SELECT
COL1,
COL2,
COL3,
FROM_TABLE,
COUNT(DISTINCT FROM_TABLE) OVER (PARTITION BY COL1,COL2,COL3)
NUM_TABLES
FROM
(SELECT
A.COL1,
A.COL2,
A.COL3,
'TABLE A' FROM_TABLE
FROM
TABLE_A A
UNION ALL
SELECT
B.COL1,
B.COL2,
B.COL3,
'TABLE B' FROM_TABLE
FROM
TABLE_B B))
WHERE
NUM_TABLES=1;```