Puzzling Query


Came across this little beauty of an SQL statement. It took me a while to work out it was doing, before I realized there was a simpler solution.

Puzzle Environment

Let’s set up the environment using the Oracle XE database.

CONNECT / AS SYSDBA
CREATE USER puzzle
  IDENTIFIED BY puzzle
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;
GRANT
  CREATE SESSION,
  CREATE TABLE
    TO puzzle;

Now for the tables used in the query.

CONNECT puzzle/puzzle
CREATE TABLE master (
  key
    NUMBER
    CONSTRAINT base_pk
      PRIMARY KEY
);
CREATE TABLE trans (
  trans_id
    NUMBER
    CONSTRAINT trans_pk
    PRIMARY KEY,
  description
    VARCHAR2( 30 ),
  foreign_key
    NUMBER
);

I have deliberately left out all of the other constraints.

Test Data

Let’s add some test data.

BEGIN
  FOR l_key IN 1..20 LOOP
    INSERT INTO master( key ) VALUES( l_key );
  END LOOP;
  COMMIT;
END;
/
BEGIN
  FOR l_key IN 1..10 LOOP
    INSERT INTO trans( trans_id, description, foreign_key )
       VALUES( 100 + l_key, 'Trans #' || (100 + l_key) || ' for #' || (2 * l_key), 2*l_key );
  END LOOP;
  COMMIT;
END;
/

The Puzzle Query

The query, in a much simplified form, is:

SELECT
    m.key,
    t.description
  FROM
    master m,
    trans  t
  WHERE
      m.key = t.foreign_key
UNION ALL
SELECT
    m.key,
    'No Trans Found'
  FROM
    master m
  WHERE
    NOT EXISTS (
      SELECT
          1
        FROM
          trans t
        WHERE
            m.key = t.foreign_key
      )
;

And the output is:


       KEY DESCRIPTION
---------- ------------------------------
         2 Trans #101 for #2
         4 Trans #102 for #4
         6 Trans #103 for #6
         8 Trans #104 for #8
        10 Trans #105 for #10
        12 Trans #106 for #12
        14 Trans #107 for #14
        16 Trans #108 for #16
        18 Trans #109 for #18
        20 Trans #110 for #20
         5 No Trans Found

       KEY DESCRIPTION
---------- ------------------------------
         3 No Trans Found
        15 No Trans Found
        19 No Trans Found
        17 No Trans Found
         7 No Trans Found
         9 No Trans Found
        13 No Trans Found
         1 No Trans Found
        11 No Trans Found

20 rows selected.

Examination of Query

The query consists of two parts:

  1. Inner Join on the key and foreign key relationship between MASTER and TRANS tables.
  2. All other rows from the MASTER table that do have matching rows in the TRANS table.

This can be better represented by a Venn diagram.

puzzle_query

The yellow area represents the first part and the green part the second.

Puzzle Solved

The original query was another version of an outer join.

The query can be rewritten as:

SELECT
    m.key,
    NVL( t.description, 'No Trans Found' )
      AS description
  FROM
      master m
    LEFT OUTER JOIN
      trans  t
    ON (
      m.key = t.foreign_key
    )
;

And the result is:

       KEY DESCRIPTION
---------- ------------------------------
         2 Trans #101 for #2
         4 Trans #102 for #4
         6 Trans #103 for #6
         8 Trans #104 for #8
        10 Trans #105 for #10
        12 Trans #106 for #12
        14 Trans #107 for #14
        16 Trans #108 for #16
        18 Trans #109 for #18
        20 Trans #110 for #20
         5 No Trans Found

       KEY DESCRIPTION
---------- ------------------------------
         3 No Trans Found
        15 No Trans Found
        19 No Trans Found
        17 No Trans Found
         7 No Trans Found
         9 No Trans Found
        13 No Trans Found
         1 No Trans Found
        11 No Trans Found

20 rows selected.

Conclusion

This query is yet another example of a procedural approach to SQL. SQL is based upon sets and is best approached from that direction.

Advertisements

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