Tell me more ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.
up vote 3 down vote favorite
1

I have some entity that is spread across three tables.

I have to get in a single result the following values:

  1. All the values that are in the first table but not on the second nor the third.
  2. All the values that are in the second table but not the third.
  3. All the values that are in the third table.
  4. Filtered by Col1.

For example, if I have the following data:

TableA

Id | Col1 | Col2 | Col3
 1 | ValX | DatA | DatA
 2 | ValZ | DatA | DatA
 3 | ValX | DatA | DatA
 5 | ValX | DatA | DatA

TableB

Id | Col1 | Col2 | Col3
 1 | ValX | DatB | DatB
 4 | ValZ | DatB | DatB
 5 | ValX | DatB | DatB
 6 | ValX | DatB | DatB

TableC

Id | Col1 | Col2 | Col3
 1 | ValX | DatC | DatC
 4 | ValZ | DatC | DatC
 6 | ValX | DatC | DatC
 7 | ValZ | DatC | DatC

If my filter is ValX, I have to obtain this result:

Result

Id | Col1 | Col2 | Col3
 1 | ValX | DatC | DatC <-- Notice I got Id 1 from TableC, not from TableA or TableB.
 3 | ValX | DatA | DatA
 5 | ValX | DatB | DatB
 6 | ValX | DatC | DatC

The following code does exactly what I'm trying to accomplish, but I feel that it is ugly and inefficient:

SELECT Id, Col1, Col2, Col3
FROM TableA 
WHERE Col1 = @Val AND 
 Id NOT IN (SELECT Id FROM TableB WHERE Col1 = @Val
            UNION
            SELECT Id FROM TableC WHERE Col1 = @Val)

UNION

SELECT Id, Col1, Col2, Col3     
FROM TableB 
WHERE Col1 = @Val AND 
 Id NOT IN (SELECT Id FROM TableC WHERE Col1 = @Val)

UNION

SELECT Id, Col1, Col2, Col3
FROM TableC
WHERE Col1 = @Val

.

How do you suggest I can improve or optimize this query?

.

share|improve this question
1  
Looking at what your expected result is and the query that accomplishes what you want, I am a bit confused. If @Val is the same in each statement, how could you have a result that contains different values in Col1? – pstrjds May 10 '12 at 19:54
You are right, actually Col1 is the same for a single entity between tables, let me fix that in the question. – macrobug May 10 '12 at 20:40
Thanks @prtsjds! It is clearer now? Care to give it a shot? – macrobug May 11 '12 at 14:15
add comment (requires an account with 50 reputation)

1 Answer

up vote 2 down vote accepted

Depending on the size of the tables, you may be better off using Common Table Expressions (CTEs) so you don't have to filter the same table repeatedly.

I would also suggest using a UNION ALL instead of UNION. UNION will remove duplicates from the result set, but there will be no duplication here, pretty much by definition. Using UNION in this case results in an extra, unnecessary sort operation, which slows down the query.

WITH TableAFiltered AS (
    SELECT *
      FROM TableA
     WHERE Col1 = @Val
),
TableBFiltered AS (
    SELECT *
      FROM TableB
     WHERE Col1 = @Val
),
TableCFiltered AS (
    SELECT *
      FROM TableC
     WHERE Col1 = @Val
)
SELECT Id, Col1, Col2, Col3
  FROM TableAFiltered
 WHERE Id NOT IN (
           SELECT Id FROM TableBFiltered
           UNION
           SELECT Id FROM TableCFiltered
       )
UNION ALL
SELECT Id, Col1, Col2, Col3     
  FROM TableBFiltered 
 WHERE Id NOT IN (
           SELECT Id FROM TableCFiltered
       )
UNION ALL
SELECT Id, Col1, Col2, Col3
FROM TableCFiltered;
share|improve this answer
1  
Thanks for taking your time Cheran! Your query performs exactly the same as mine, same execution plan and everything. +1 for improved readability of the code! (sorry cannot upvote you yet) – macrobug May 14 '12 at 13:45
add comment (requires an account with 50 reputation)

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.