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.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)
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply