SQL – Methods of Reformatting into Equivalent Forms 5

2 12 2009

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;

Actions

Information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 140 other followers

%d bloggers like this: