sqlansi-sql

SQL Different conditions based on where clause

I am interested in if there is an easier way to perform the following query than looping over unions.

What I want to do is have separate where clauses based on different filtering fields. Say I have a table TBL_SAMPLE

create table TBL_SAMPLE
(
    DATE DATETIME,
    ID varchar(32),
    ATTRIBUTE varchar(32),
    VALUE int
);

insert into TBL_SAMPLE (DATE, ID, ATTRIBUTE, VALUE)
values
    ('1-1-2024', 'A', 'FIELD_1', 1),
    ('1-2-2024', 'A', 'FIELD_1', 2),
    ('1-3-2024', 'A', 'FIELD_1', 3),
    ('1-4-2024', 'A', 'FIELD_1', 4),
    ('1-1-2024', 'B', 'FIELD_1', 10),
    ('1-2-2024', 'B', 'FIELD_1', 11),
    ('1-3-2024', 'B', 'FIELD_1', 12),
    ('1-4-2024', 'B', 'FIELD_1', 13);

Which I could get by performing the following query:

create table TBL_WANT
(
    DATE DATETIME,
    ID varchar(32),
    ATTRIBUTE varchar(32),
    VALUE int
);

insert into TBL_WANT (DATE, ID, ATTRIBUTE, VALUE)
values
    ('1-2-2024', 'A', 'FIELD_1', 2),
    ('1-3-2024', 'A', 'FIELD_1', 3),
    ('1-4-2024', 'A', 'FIELD_1', 4),
    ('1-3-2024', 'B', 'FIELD_1', 12),
    ('1-4-2024', 'B', 'FIELD_1', 13);

I could accomplish this result with the following query:

(
    select
        *
    from
        TBL_SAMPLE
    where
        id = 'A'
        and ATTRIBUTE ='FIELD_1'
        and DATE >= '1-2-2024'
)
union
(
    select
        *
    from
        TBL_SAMPLE
    where
        id = 'B'
        and ATTRIBUTE ='FIELD_1'
        and DATE >= '1-3-2024'
);

But, this seems rather clunky. In a more realistic example, there will be a large number of these fields. Is there a better way to do this?

Solution

  • You can just use the OR operator to combine the mutually exclusive filter criteria, replacing the union:

       select
        *
    from
        TBL_SAMPLE
    where
        id = 'A'
        and ATTRIBUTE ='FIELD_1'
        and DATE >= '1-2-2024'
        OR
        id = 'B'
        and ATTRIBUTE ='FIELD_1'
        and DATE >= '1-3-2024'

    If you have many common comparisons in your criteria, then you can can refactor to isolate the common elements from the variable comparisons.

    In this case ATTRIBUTE ='FIELD_1' is common, but due to standard order of operations we will need to use parentheses to control the flow:

       select
        *
    from
        TBL_SAMPLE
    where
        ATTRIBUTE ='FIELD_1'
        AND
        (
            id = 'A' and DATE >= '1-2-2024'
            OR
            id = 'B' and DATE >= '1-3-2024'
        )

    It is not necessary to put additional parentheses around the criteria that are ANDed together but if it helps your understanding or readability then it can be useful for large criteria lists.

       select
        *
    from
        TBL_SAMPLE
    where
        ATTRIBUTE ='FIELD_1'
        AND
        (
            ( id = 'A' and DATE >= '1-2-2024' )
            OR
            ( id = 'B' and DATE >= '1-3-2024' )
        )