Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In the given schema, there is a table A having column A1,A2,A3,A4

There are three more tables i.e. B,C,D

If the value in A4 is b, i have to lookup table B. If the value in A4 is c, i have to lookup table C. If the value in A4 is d, i have to lookup table D.

Currently, we have implemented using switch case i.e.

select * from A
where
CASE
  WHEN SUBSTRING(A4,4,3) IN ( '023','009','011','013','015','017','019','021')
    THEN A3 NOT IN ( select Z from B) 
  WHEN SUBSTRING(A4,4,3) IN ( '006','024','028','031')
    THEN A3 NOT IN ( select Z from C) 
  WHEN SUBSTRING(A4,4,3) IN ( '004','025')
    THEN A3 NOT IN ( select Z from D) 
END

Is there any better and more efficient way to resolve this problem?

share|improve this question
    
Yes dont use this SQL anti pattern but use an normalised table design. this query will trigger an full table scan on table A and fore every row in A 4 full more full table scans on tables B, C and D... dont try to solve one hard question in 1 query... –  Raymond Nijland Sep 25 '13 at 18:50

1 Answer 1

First of all, append a computed column, say A4Key, set to SUBSTRING(A4, 4, 3).

This column shall be kept up-to-date either by your application code or by triggers, such as:

CREATE TRIGGER ... BEFORE UPDATE ...
...
    SET NEW.A4Key = SUBSTRING(NEW.A4, 4, 3)
...

CREATE TRIGGER ... BEFORE INSERT ...
...
    SET NEW.A4Key = SUBSTRING(NEW.A4, 4, 3)
...

first solution

Then, rewrite your query to:

SELECT * FROM A
WHERE A4Key IN('023', '009', '011', '013', '015', '017', '019', '021')
  AND A3 NOT IN(SELECT Z FROM B)
-- -------
UNION ALL
-- -------
SELECT * FROM A
WHERE A4Key IN('006', '024', '028', '031')
  AND A3 NOT IN(SELECT Z FROM C)
-- -------
UNION ALL
-- -------
SELECT * FROM A
WHERE A4Key IN('004', '025')
  AND A3 NOT IN(SELECT Z FROM D)

Now, evaluate your query plans to devise the best indexes.

second solution

Create a table such as:

CREATE TABLE A4Exceptions(
  A4Key VARCHAR(3),
  Z     `whichever_type_of_Z`
)

With initial data set by:

INSERT INTO A4Exceptions SELECT '023', Z FROM B
INSERT INTO A4Exceptions SELECT '009', Z FROM B
...
INSERT INTO A4Exceptions SELECT '006', Z FROM C
...

And keep it up-to-date with the changing (?) data from B, C and D.

Done that, now you may, with blazing performance (after creating some indexes):

SELECT *
FROM A
WHERE NOT EXISTS(
  SELECT *
  FROM A4Exceptions exc
  WHERE exc.A4Key = A.A4Key
    AND ext.Z     = A.A3
)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.