T-SQL Code Review - Where Field DOES NOT contain

  • Hello community,

    Quick question to get your opinion on simply T-SQL code.

    I want to generate a table without certain values in a field.

    Using the sample data, I want to create a table without the values 'New York City' or 'Los Angeles' in the field 'airport_city'.

    The code I would use to get the result would be?

    SELECT
    *
    FROM dbo.airports
    WHERE airports.airport_city <> 'New York City'
    AND airports.airport_city <> 'Los Angeles'


    I would like to know if you would take the same approach.

    Sample Data

    CREATE TABLE airports (
    airport_code varchar(10),
    airport_name varchar(70),
    airport_city varchar(20),
    airport_state varchar(20))

    INSERT airports VALUES
    ('MSP',' Minneapolis-St Paul International ','Minneapolis','Minnesota'),
    ('JFK',' John F. Kennedy International','New York City','New York'),
    ('LAX',' Los Angeles International','Los Angeles','California'),
    ('DFW',' Dallas/Fort Worth International','Dallas/Fort Worth','Texas'),
    ('BOS','Logan International ','Boston','Massachusetts'),
    ('SFO',' San Francisco International ','San Francisco','Californiaa'),
    ('ATL',' Hartsfield-Jackson Atlanta International ','Atlanta','Georgia'),
    ('LGA',' LaGuardia','nyc','New York'),
    ('DTW',' Detroit Metro Wayne County','Detroit','Michigan'),
    ('SAN',' San Diego International ','San Diego','Caalifornia'),
    ('IAH','George Bush Intercontinental/Houston','Houston','Tejas'),
    ('LAS','McCarran International','Las Vegas','Nevada'),
    ('ORD',' Chicago O''Hare International','ch','Illinois'),
    ('MDW',' Chicago Midway International','Chicago','Ilynois'),
    ('PDX',' Portland International ','Portland','Oregon'),
    ('MIA','Miami International','Miami','fl'),
    ('PHX',' Phoenix Sky Harbor International','Phoenix','Arizona'),
    ('DEN',' Denver International','Denver','Colorado'),
    ('BWI',' Baltimore/Washington International Thurgood Marshall','Baltimore','Maryland'),
    ('EWR',' Newark Liberty International','Newark','New Jersey'),
    ('SEA','Seattle/Tacoma International',NULL,NULL),
    ('PHL','Philadelphia International','Philadelphia',NULL),
    ('SLC','Salt Lake City International',NULL,'Utah'),
    ('MCO','Orlando International','Orlando','Florida'),
    ('TPA','Tampa International','Tampa','Fl'),
    ('FLL','Fort Lauderdale-Hollywood International','Fort Lauderdale','FL'),
    ('CLT','Charlotte Douglas International','Charlotte','North Carolina'),
    ('carlton','Charlotte Douglas International','Charlotte','North Carolina')

    SELECT * FROM airports

     

  • For a very small set I'd probably use NOT IN instead of anding multiple exclusions --

    WHERE airports.airport_city NOT IN ('New York City','Los Angeles')

    For more than a few, I'd probably insert the list in a temp table and use WHERE NOT EXISTS.

  • There are airports with NULL city names which are not returned by the original query.

    For this example I would probably use ISNULL(airport_city, '') not in

    but generally I avoid NOT IN so I'd use NOT EXISTS against a table or subquery.

  • HoF

    Can you provide an example against the table I provided.

    thanks.

  •  

    WITH exclude AS
    ( SELECT airport_city
    FROM dbo.Airports
    WHERE airport_city IN ('New York City','Los Angeles')
    )
    SELECT *
    FROM dbo.Airports AS a
    WHERE NOT EXISTS
    ( SELECT 1
    FROM exclude
    WHERE airport_city = a.airport_city
    );
  • For a simple query like this - I would also use NOT IN, but I would also use the code instead of the name.  For more complex queries - it depends.

    In some cases I would create a CTE, table variable or temp table of the valid values - then either JOIN or EXISTS or IN.  In some cases I would create a list of exclusions (again, CTE, table variable or temp table) and use NOT EXISTS, NOT IN or OUTER JOIN.  In other cases I might use CROSS APPLY or OUTER APPLY or maybe a table-constructor with VALUES.

    And in some cases - I may build an iTVF if the logic for inclusion/exclusion is complex and/or needed in other scripts.

    So....it depends.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Ed B wrote:

    For this example I would probably use ISNULL(airport_city, '') not in

    That would make sure that you don't ever have to worry about the query using an index to seek. 😀  Since a NULL airport_city can never equate in a NOT IN, just leave the ISNULL off.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply