0

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?

1
  • 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... Sep 25 '13 at 18:50
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
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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