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