Home Forums SQL Server 7,2000 T-SQL Any way to clean up mutually exclusive OR conditions? RE: Any way to clean up mutually exclusive OR conditions?

  • The usual idiom for this is a CASE expression the usual idiom for this is a CASE expression. I am assuming that you want only one of the three predicates to be true (and the other two false).

    The CASE expression in SQL tests the when clauses in left to right order (no, it does not rearrange them unless the rearrangement is logically identical to the way the expression was written), and returns the then clause value if the when clause test true. If they all fail the else clause returns a value.

    CASE

    WHEN a AND NOT(b) AND NOT(c) THEN 'T'

    WHEN NOT(a) AND b AND NOT(c) THEN 'T'

    WHEN NOT(a) AND NOT(b) AND c THEN 'T'

    ELSE 'F' END = 'F'

    Here is the gimmick; you can read if you write your code correctly, then you can arrange the when clauses to put the most likely true clause to the top of the list. This is not quite the same as short-circuit evaluation, but it is a declarative substitute. When you get a really good SQL compiler, it will keep track of this and might be able to do some rearranging for you based on actual statistics as the program executes.

    The optimizer should be able to see common expressions in the when clauses, and evaluate them only once. This does not save you typing, but it might perform better. That is why my skeleton code shows NOT(); this just makes it easier for parser to pick out duplicate code.

    Books in Celko Series for Morgan-Kaufmann Publishing
    Analytics and OLAP in SQL
    Data and Databases: Concepts in Practice
    Data, Measurements and Standards in SQL
    SQL for Smarties
    SQL Programming Style
    SQL Puzzles and Answers
    Thinking in Sets
    Trees and Hierarchies in SQL