T-SQL Code Review - Where Field DOES NOT contain

Question

Post reply

T-SQL Code Review - Where Field DOES NOT contain

carlton 84646

Ten Centuries

Points: 1026

December 5, 2022 at 5:38 pm

#4122166

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

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

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

ratbak Ten Centuries Points: 1333 More actions · Answer 1

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.

Ed B Hall of Fame Points: 3618 More actions · Answer 2

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.

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 3

HoF

Can you provide an example against the table I provided.

thanks.

Ed B Hall of Fame Points: 3618 More actions · Answer 4

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
);

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 5

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

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 6

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.

Change is inevitable... Change for the better is not.

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