December 2, 2009
(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)
A couple of years ago someone posted in the comp.databases.oracle.database Usenet group the following challenge:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/fbe588a109b3de20
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;
Leave a Reply