From Clause Data Sources

From Clause Data Sources:

The first logical component of a typical SQL SELECT statement is the FROM clause. In a simple SQL SELECT statement, the FROM clause contains a single table. However, the FROM clause can also combine data from multiple sources and multiple types of data sources. The maximum number of tables that may be accessed within a single SQL SELECT statement is 256.

The FROM clause is the foundation of the rest of the SQL statement. In order for a table column to be in the output, or accessed in the WHERE conditions, or in the ORDER BY, it must be in the FROM clause.

Possible data sources:

SQL is extremely flexible and can accept data from seven distinctly different types of data sources within the FROM clause:

■ Local SQL Server tables

■ Subqueries serving as derived tables, also called subselects or in-line views. Common table expressions (CTEs) are functionally similar to subqueries but may be referenced multiple times within the query. Views, or stored SELECT statements, can be referenced within the FROM clause as if they were tables.

Simple Sub Query as table:

SELECT P.Code,

P.ProductName,

Sales.QuantitySold

FROM dbo.Product AS P

JOIN (SELECT ProductID, SUM(Quantity) AS QuantitySold

FROM dbo.OrderDetail

GROUP BY ProductID) AS Sales

ON P.ProductID = Sales.ProductID

ORDER BY P.Code;

CTE:

WITH CTEQuery (ProductCategoryID)

AS (Select ProductCategoryID

from dbo.ProductCategory

Where ProductCategoryName = ‘Kite’)

SELECT ProductName

FROM dbo.Product

WHERE ProductCategoryID

= (SELECT ProductCategoryID FROM CTEQuery);

■ Table-valued user-defined functions return rows and columns. See Chapter 25, ‘‘Building User-Defined Functions,’’ for more information.

■ Distributed data sources pull in data from other SQL Server databases, other SQL Servers,

other database platforms (e.g., Microsoft Access, Oracle, Foxpro), or applications (e.g., Excel)

using openquery() and other distributed functions, as detailed in Chapter 31, ‘‘Executing

Distributed Queries.’’

■ Full-text search can return data sets with information about which rows contain certain

words, as explained in Chapter 19, ‘‘Using Integrated Full-Text Search.’’

■ Pivot creates a crosstab within the FROM clause and is covered in Chapter 12, ‘‘Aggregating

Data.’’

■ XML data sources using XQuery, as discussed in Chapter 18, ‘‘Manipulating XML Data.’’

SQL Server 2008 adds two new data sources:

■ Row constructors build hard-coded rows using the values() clause.

SELECT a, b

FROM

(VALUES

(1, 2),

(3, 4),

(5, 6),

(7, 8),

(9, 10)

) AS MyRowConstructor (a, b)

■ Inserted and deleted virtual tables from an insert, update, or delete can be passed to an outer

query in the form of a subquery using the output clause.

Table aliases

The readability of a SELECT statement can be improved by giving a table an alias, also known as a correlation name or range variable. A table alias can be assigned either with or without the AS keyword:

·         table_name AS table alias

·         table_name table_alias

A data source may be assigned a table alias within the FROM clause. Once the data source has an alias, it must be referred to by this new name. In some cases the data source must have an alias. The following code accesses the Guide table, but refers to it within the query as table G:

-- From Table [AS] Table Alias

USE CHA2;

SELECT G.lastName, G.FirstName

FROM Guide AS G;

Best Practice

Using the keyword AS, to assign an alias to a column or data source, is optional and is commonly ignored. However, this practice leads to errors in the query, as seen regularly in SQL Server newsgroups. As a rule, always include the AS keyword.

select test3.id from test3  t

          Msg 4104, Level 16, State 1, Line 1

          The multi-part identifier "test3.id" could not be bound.

select t.id from test3  t

          id

          -------------

          1.254

select * from test3  t

          id

          -------------

          1.254

[Table Name]

If the name of a database object, such as a table or column name, conflicts with a SQL reserved keyword, you can let SQL know that it’s the name of an object by placing it inside square brackets.

Note that the square brackets are specific to SQL Server and not part of the ANSI SQL standard. The [Order]table in the OBXKites sample database is a common example of a table name that’s also a keyword:

USE OBXKites;

SELECT OrderID, OrderDate

FROM [Order];

Although it’s an incredibly poor practice to include spaces within the names of database objects, it is possible nevertheless. If this is the case, square brackets are required when specifying the database object. The Order Details table in the Northwind sample database illustrates this:

USE Northwind;

SELECT OrderID, ProductID, Quantity

FROM [Order Details];

Fully qualified names

The complete name of an object is made up of four identifiers: the server name, database name, schema name, and object name. They appear in the following format:

server_name.[database_name].[schema_name].object_name

| database_name.[schema_name].object_name

| schema_name.object_name

| object_name

The server, database, and owner names are known as the qualifiers of the object name. When you refer to an object, you do not have to specify the server, database, and owner. The qualifiers can be omitted by marking their positions with a period. The valid forms of object names include the following:

server_name.database_name.schema_name.object_name

server_name.database_name..object_name

server_name..schema_name.object_name

server_name...object_name

database_name.schema_name.object_name

database_name..object_name

schema_name.object_name

Object_name

An object name that specifies all four parts is known as a fully qualified name. Each object that is created in Microsoft SQL Server must have a unique, fully qualified name. For example, there can be two tables named xyz in the same database if they have different owners.

Most object references use three-part names. The default server_name is the local server. The default database_name is the current database of the connection. The default schema_name is the default schema of the user submitting the statement. Unless otherwise configured, the default schema of new users is the dbo schema.

Four-part names are generally used for distributed queries or remote stored procedure calls. They use the following format:

linkedserver.catalog.schema.object_name

The following table shows the part names and their descriptions.

Part name	Description
linkedserver	Name of the linked server that contains the object referenced by the distributed query.
catalog	Name of the catalog that contains the object referenced by the distributed query. When querying a SQL Server database, the catalog is the database name.
schema	Name of the schema that contains the object referenced by the distributed query.
object_name	Object name or table name.

For distributed queries, the server part of a four-part name refers to a linked server. A linked server is a server name that is defined with sp_addlinkedserver. The linked server identifies an OLE DB provider and an OLE DB data source that can return a record set that Microsoft SQL Server can use as part of a Transact-SQL statement.

To determine what components in the OLE DB data source are used for the catalog and schema parts of the name, see the documentation for the OLE DB provider specified for the linked server. If the linked server is running an instance of SQL Server, the catalog name is the database that contains the object, and the schema is the owner of the object. For more information about four-part names and distributed queries, see Distributed Queries.

For remote procedure calls, the server part of a four-part name refers to a remote server. A remote server, which is specified with sp_addserver, is an instance of SQL Server that is accessed through the local server. Execute stored procedures on the remote server by using the following format for the procedure name:

server.database.schema_name.procedure

All four parts of the name are required when you are using a remote stored procedure. For more information about remote servers, see Configuring Remote Servers.

Best Practice

Using the two-part name, schema, and object name is sufficient and the best practice. Including the server

and database name would restrict moving code from one server to another (e.g., from development to production).

Besides just writing cleaner code, there are two specific benefits to using the qualified name:

■ The same table may exist in multiple schemas. If this is the case, then the schema selected is based on the user’s default schema. Qualifying the name avoids accidentally using the wrong table.

■ Qualified tables names are required in order for the Query Engine to reuse the query execution plan, which is important for performance.

Referencing Columns by Using Identifiers

Column names must be unique within a table or view. You can use up to three prefixes to specify columns in a query where more than one table that is referenced may have a column of the same name. Any one of the following formats is acceptable:

database_name.schema_name.object_name.column_name

database_name..object_name.column_name

schema_name.object_name.column_name

object_name.column_name

Referencing Column Properties of User-Defined Type Columns

Properties of user-defined type columns are referenced by using the period separator (.) between the column name and the property name. When property names are referenced, you can also reference the table or view identifiers, but not the database or schema identifiers. The following formats are acceptable:

column_name.property_name1.property_name2[…]

object_name.column_name.property_name1.property_name2[…]

Object Visibility and Qualification Rules:

When you create an object, Microsoft SQL Server uses the following defaults for the parts of the name not specified.

Part not specified	Default
Server	Default is the local server.
Database	Default is the current database.
Schema_name	Default is the default schema of the current user in the current database.

For example, if a user is logged on to AdventureWorks2008R2 as the database owner user, dbo, either of the following two statements creates a table that is named AdventureWorks2008R2.dbo.TableX:

CREATE TABLE TableX (cola INT PRIMARY KEY, colb NCHAR(3));

GO

                                Or

CREATE TABLE AdventureWorks2008R2.dbo.TableX

      (cola INT PRIMARY KEY, colb NCHAR(3));

GO

Note

You should specify the full table or view name to prevent possible confusion relating to the object you are working with.

Similarly, when you refer to an object, SQL Server uses the following defaults for the parts of the name not specified .

Part not specified	Default
Server	Default is the local server.
Database	Default is the current database.
Schema_name	Default is the default schema of the current user that is associated with the login ID of the current connection. If that user does not have a default schema, the schema will be the database owner (dbo) user.

For example, assume LoginX connects to a server that has two databases: DBY and DBZ. LoginX is associated with UserA in database DBY and with UserB in database DBZ.

LoginX executes a SELECT statement in the current database as follows:

USE DBY;

SELECT * FROM DBY..TableX;

Because LoginX is associated with UserA in DBY, SQL Server first looks for DBY.UserA.TableX. If there is no table with this name, SQL Server looks for a table DBY.dbo.TableX.

In the following example, LoginX executes a SELECT statement on a table that is not in the current database:

USE DBY;

GO

SELECT * FROM DBZ..TableY;

GO

Because LoginX is associated with UserB in database DBZ, SQL Server first looks for DBZ.UserB.TableY. If there is no table with this name, SQL Server then looks for a table DBZ.dbo.TableY.

Note: SQL Server does not try to determine the owner of remote tables based on the current login. To make sure that distributed queries execute correctly, you should use fully qualified names.

Delimited Identifiers (Database Engine)

An identifier that complies with all the rules for the format of identifiers can be used with or without delimiters. An identifier that does not comply with the rules for the format of regular identifiers must always be delimited.

Note

Microsoft SQL Server does not recognize variable names and stored procedure parameters that are delimited. These types of identifiers must comply with the rules for regular identifiers.

Delimited identifiers are used in the following situations:

When reserved words are used for object names or parts of object names.

Reserved keywords should not be used as object names. Databases upgraded from earlier versions of SQL Server may contain identifiers that include words not reserved in the earlier version, but that are reserved words for the current version of SQL Server. You can refer to the object by using delimited identifiers until the name can be changed.

When you are using characters that are not listed as qualified identifiers.

SQL Server allows any character in the current code page to be used in a delimited identifier. However, indiscriminate use of special characters in an object name may make SQL statements and scripts difficult to read and maintain. For example, you can create a table with the nameEmployee], where the closing square bracket is part of the name. To do this you have to escape the closing square bracket using two more square brackets as shown in the following:

CREATE TABLE [Employee]]]

(

EmployeeID int IDENTITY (1,1) NOT NULL,

FirstName varchar(30),

LastName varchar(30)

)

Note

Delimiters are for identifiers only. Delimiters cannot be used for keywords, even if they are marked as reserved in SQL Server.

Following are the types of delimiters used in Transact-SQL:

Quoted identifiers are delimited by double quotation marks ("):

SELECT * FROM "Blanks in Table Name"

Bracketed identifiers are delimited by brackets ([ ]):

SELECT * FROM [Blanks In Table Name]

Quoted identifiers are valid only when the QUOTED_IDENTIFIER option is set to ON. By default, the Microsoft OLE DB Provider for SQL Server and SQL Server ODBC driver set QUOTED_IDENTIFIER to ON when they connect.

Regardless of the interface used, individual applications or users may change the setting at any time. SQL Server provides several ways to specify this option. For example, in SQL Server Management Studio, the option can be set in a dialog box. In Transact-SQL, the option can be set at various levels by using SET QUOTED_IDENTIFIER, the QUOTED_IDENTIFIER option of ALTER DATABASE, or the user options option of sp_configure.

When QUOTED_IDENTIFIER is set to ON, SQL Server follows the ISO rules for the use of double quotation marks (") and the single quotation mark (') in SQL statements. For example:

Double quotation marks can be used only to delimit identifiers. They cannot be used to delimit character strings.

To maintain compatibility with existing applications, SQL Server does not fully enforce this rule. Character strings can be enclosed in double quotation marks if the string does not exceed the length of an identifier. We do not recommend this practice.

Single quotation marks must be used to enclose character strings. They cannot be used to delimit identifiers.

If the character string contains an embedded single quotation mark, you should insert an additional single quotation mark in front of the embedded mark. For example:

SELECT * FROM "My Table"

WHERE "Last Name" = 'O''Brien'

Note

If delimited identifiers are used when naming an object and the object name contains trailing spaces, SQL Server stores the name without the trailing spaces.

When QUOTED_IDENTIFIER is set to OFF, SQL Server uses the following rules for single and double quotation marks:

Quotation marks cannot be used to delimit identifiers. Instead, brackets have to be used as delimiters.

Single or double quotation marks can be used to enclose character strings.

If double quotation marks are used, embedded single quotation marks do not have to be denoted by two single quotation marks. For example:

SELECT * FROM [My Table]

WHERE [Last Name] = "O'Brien"

Delimiters in brackets can always be used, regardless of the setting of QUOTED_IDENTIFIER.

Rules for Delimited Identifiers:

The rules for the format of delimited identifiers include the following:

Delimited identifiers can contain the same number of characters as regular identifiers. This can be from 1 through 128 characters, not including the delimiter characters. Local temporary table identifiers can be a maximum of 116 characters.

The body of the identifier can contain any combination of characters in the current code page, except the delimiting characters themselves. For example, delimited identifiers can contain spaces, any characters valid for regular identifiers, and any one of the following characters.

tilde (~)	hyphen (-)
Exclamation point (!)	left brace ({)
Percent (%)	right brace (})
caret (^)	apostrophe (')
ampersand (&)	Period (.)
left parenthesis (()	backslash (\)
right parenthesis ())	accent grave (`)

The following examples use quoted identifiers for table names and column names. Both methods for specifying delimited identifiers are shown in the following:

SET QUOTED_IDENTIFIER ON;

GO

CREATE TABLE "$Employee Data"

(

 "^First Name"   varchar (25) NOT NULL,

 "^Last Name"   varchar (25) NOT NULL,

 "^Dept ID"   int

);

GO

-- INSERT statements go here.

SET QUOTED_IDENTIFIER OFF;

GO

CREATE TABLE [^$Employee Data]

(

 [^First Name]   varchar (25) NOT NULL,

 [^Last Name]   varchar (25) NOT NULL,

 [^Dept ID]   int

);

GO

-- INSERT statements go here.

After the $Employee Data and ^$Employee Data tables are created and data is entered, rows can be retrieved as shown in the following:

SET QUOTED_IDENTIFIER ON;

GO

SELECT *

FROM "$Employee Data"

SET QUOTED_IDENTIFIER OFF;

GO

-- Or

SELECT *

FROM [^$Employee Data]

In the following example, a table named table contains columns tablename, user, select, insert, update, and delete. Because TABLE, SELECT, INSERT, UPDATE, and DELETE are reserved keywords, the identifiers must be delimited every time the objects are accessed.

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE "table"

(

 Tablename char (128) NOT NULL,

 "USER"     char (128) NOT NULL,

 "SELECT"  char(128) NOT NULL,

 "INSERT"  char(128) NOT NULL,

 "UPDATE"  char(128) NOT NULL,

 "DELETE"  char (128) NOT NULL

);

GO

If the SET QUOTED_IDENTIFIER option is OFF, the table and columns cannot be accessed unless bracket delimiters are used. For example:

SET QUOTED_IDENTIFIER OFF;

GO

SELECT *

FROM "table";

GO

Here is the result set.:

Msg 170, Level 15, State 1

Line 1: Incorrect syntax near 'table'.

The following works, because of the bracket delimiters:

SET QUOTED_IDENTIFIER OFF;

GO

SELECT *

FROM [table];

GO

Delimiting Identifiers with Multiple Parts:

When you are using qualified object names, you may have to delimit more than one of the identifiers that make up the object name. Each identifier must be delimited individually. For example:

/* ISO quoted identifier syntax */

SELECT *

FROM "My DB"."My#UserID"."My.Table";

GO

Or

/* Transact-SQL bracketed identifier syntax */

/* Not available in SQL Server 6.5 or earlier */

SELECT *

FROM [My DB].[My#UserID].[My.Table];

GO

There are some special rules regarding how you delimit multipart stored procedure names in the ODBC CALL statement. For more information, see Calling a Stored Procedure.

Using Identifiers As Parameters in SQL Server

Many system stored procedures, functions, and DBCC statements take object names as parameters. Some of these parameters accept multipart object names, while others accept only single-part names. Whether a single-part or multipart name is expected determines how a parameter is parsed and used internally by SQL Server.

Single-part Parameter Names

If the parameter is a single-part identifier, the name can be specified in the following ways:

Without quotation marks or delimiters

Enclosed in single quotation marks

Enclosed in double quotation marks

Enclosed in brackets

For single-part names, the string inside the single quotation marks represents the object name. If delimiters are used inside single quotation marks, the delimiter characters are treated as part of the name.

If the name contains a period or another character that is not part of the character set defined for regular identifiers, you must enclose the object name in single quotation marks, double quotation marks, or brackets.

Multipart Parameter Names

Multipart names are qualified names that include the database or schema name and also the object name. When a multipart name is used as a parameter, SQL Server requires that the complete string that makes up the multipart name be enclosed in a set of single quotation marks.

EXEC MyProcedure @name = 'dbo.Employees'

If individual name parts require delimiters, each part of the name should be delimited separately as required. For example, if a name part contains a period, double quotation mark, or a left or right bracket, use brackets or double quotation marks to delimit the part. Enclose the complete name in single quotation marks.

For example, the table name, tab.one, contains a period. To prevent the name from being interpreted as a three-part name, dbo.tab.one, delimit the table name part.

EXEC sp_help 'dbo.[tab.one]'

The following example shows the same table name delimited with double quotation marks:

SET QUOTED_IDENTIFIER ON

GO

EXEC sp_help 'dbo."tab.one"'

GO

Where Conditions:

The WHERE conditions filter the output of the FROM clause and restrict the rows that will be returned in the result set. The conditions can refer to the data within the tables, expressions, built-in SQL Server scalar functions, or user-defined functions. The WHERE conditions can also make use of several possible comparison operators and wildcards, as listed in Table 8-1. In addition, multiple WHERE conditions may be combined using Boolean AND, OR, and NOT operators.

Best Practice:

One sure way to improve the performance of a client/server database is to let the Database Engine do the work of restricting the rows returned, rather than make the client application wade through unnecessary data.

Standard Comparison Operators

Description                        Operator                 Example

Equals                              =                           Quantity = 12

Greater than                      >                           Quantity > 12

Greater than or equal to      >=                        Quantity >= 12

Less than                          <                           Quantity < 12

Less than or equal to          <=                        Quantity<= 12

Not equal to                      <> , !=                  Quantity <> 12 , Quantity != 12

Not less than                     !<                          Quantity !< 12

Not greater than                !>                          Quantity !> 12

The comparison operators that include an exclamation point are not ANSI standard SQL.

<> is portable; != is not.

In addition to the standard comparison operators, which are no doubt familiar, SQL provides four special comparison operators: BETWEEN, IN, LIKE, and IS. The first three are explained in this section. Testing for nulls using the IS keyword and handling nulls.

Best Practice

The best way to find a thing is to look for it, rather than to first eliminate everything it isn’t. It’s far easier to locate a business in a city than it is to prove that the business doesn’t exist. The same is true of database searches. Proving that a row meets a condition is faster than first eliminating every row that doesn’t meet that condition. In general (but not always), restating a negative WHERE condition as a positive condition will improve performance.

Using the between search condition:

The BETWEEN search condition tests for values within a range. The range can be deceiving, however, because it is inclusive. For example, BETWEEN 1 and 10 would be true for 1 and 10. When using the BETWEEN search condition, the first condition must be less than the latter value because in actuality, the BETWEEN search condition is shorthand for ‘‘greater than or equal to the first value, and less than or equal to the second value.’’

In this example, the BETWEEN is used to select all the work orders with a quantity greater than 9 and less than 20:

USE AdventureWorks2008

SELECT WorkOrderID

FROM Production.WorkOrder

WHERE OrderQty BETWEEN 10 and 19

The BETWEEN search condition is commonly used with dates. However, BETWEEN without a time will look for the beginning of the final day, or with a time will round up the final millisecond to possibly include 12:00:00.000 of the next day. The solution is to use the following:

WHERE Col >= StartDay AND Col < Ending Day + 1

For example,

WHERE SalesDate >= ‘6/1/2008’ AND SalesDate < ‘7/1/2008’

Comparing with a list:

The WHERE condition can compare the test value against the values in a list using IN, SOME, ANY, or ALL. Each operator can also be mixed with a NOT to reverse the condition.

Best Practice - It Turns Out Algebra Actually Is Useful:

As much fun as algebra class was, while we thought algebra might improve our logical minds, few of us believed we’d actually use algebra in our chosen profession.

Enter the SQL WHERE clause.

Here’s the problem: If a function is applied to the test column in the WHERE clause, then SQL Server is forced to calculate that function on every row before it can filter the WHERE clause. This is a sure setup for ‘‘Gee, I don’t know, it worked OK on my notebook’’ syndrome.

For a simple example, assume there’s an index on Coll. The following WHERE clause will generate an unnecessary scan, reading every row, as every column is modified and then compared to 130:

SELECT Col2, Col3

FROM table

WHERE Col11 + 30 = 130;

Algebra to the rescue. Somehow figure out a way to move that function to the parameter on the right side of the ‘‘=’’ and off the column so that the column on the left side is unencumbered by any calculation or functions:

SELECT Col2, Col3

FROM table

WHERE Col11 = 130 – 30;

Now SQL Server can evaluate 130 – 30 and perform a blazingly fast index seek on the rows with 100 in Col1. Although this is a simple example, the principle is true. How you write your WHERE clauses has a significant effect on the performance of your queries.

This is only a small taste of the Query Optimizer and whether or not WHERE clause expressions are searchable Arguments, known as sargs. Reading query execution plans and tuning queries and indexes are covered in greater detail in Chapters 63, 64, and 65.

SOME and ANY search conditions are functionally similar to IN — all are true if any value in the list is true — with three significant differences:

■ SOME and ANY require a subquery. A list of literal values won’t do.

■ SOME and ANY are used with a mathematical operator (=, >, <, =>, etc.).

■ IN, SOME, and ANY function differently when used with a NOT condition.

The AND search condition also requires a true subquery and returns a true when the search condition is true for every value in the list.

IN:

Determines whether a specified value matches any value in a subquery or a list.

Syntax

test_expression [ NOT ] IN  

    ( subquery | expression [ ,...n ] 

    )  

Arguments

test_expression
Is any valid expression.

subquery
Is a subquery that has a result set of one column. This column must have the same data type as test_expression.

expression[ ,... n ]
Is a list of expressions to test for a match. All expressions must be of the same type as test_expression.

Result Types

Boolean

Result Value

If the value of test_expression is equal to any value returned by subquery or is equal to any expression from the comma-separated list, the result value is TRUE; otherwise, the result value is FALSE.

Using NOT IN negates the subquery value or expression.

Any null values returned by subquery or expression that are compared to test_expression using IN or NOT IN return UNKNOWN. Using null values in together with IN or NOT IN can produce unexpected results.

Remarks

Explicitly including an extremely large number of values (many thousands of values separated by commas) within the parentheses, in an IN clause can consume resources and return errors 8623 or 8632. To work around this problem, store the items in the IN list in a table, and use a SELECT subquery within an IN clause.

Error 8623:

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

Error 8632:

Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

IN is similar to the EQUALS comparison operator, as it searches for an exact match from a list. If the value is in the list, then the comparison is true. For instance, if region data were entered into the database, the following code finds any Cape Hatteras Adventures base camps in North Carolina or West Virginia:

USE CHA2;

SELECT BaseCampname

FROM dbo.BaseCamp

WHERE Region IN (’NC’, ‘WV’);

OR

USE CHA2;

SELECT BaseCampname

FROM dbo.BaseCamp

WHERE Region = ‘NC’

OR Region = ‘WV’;

The IN operator may also search for a value in a list of columns. The following example searches for the text ‘NC’ in either the Name, City, Region, or Country columns:

USE CHA2;

SELECT Name

FROM dbo.BaseCamp

WHERE ‘NC’ IN (Name, City, Region, Country)

The IN operator may be combined with NOT to exclude certain rows. For example, WHERE NOT IN (’NC’, ‘SC’) would return all rows except those in the Carolinas:

USE CHA2;

SELECT BaseCampname

FROM dbo.BaseCamp

WHERE Region NOT IN (’NC’, ‘SC’);

It’s difficult to prove a negative, especially when a null value is involved. Because the meaning of null is ‘‘unknown,’’ the value being searched for could be in the list. The following code sample demonstrates how a null in the list makes it impossible to prove that ‘A’ is not in the list:

SELECT ‘IN’ WHERE ‘A’ NOT IN (’B’,NULL);

There’s no result because the unknown null value might simply be an ‘‘A.’’ Because SQL can’t logically prove that ‘‘A’’ is not in the list, the WHERE clause returns a false. Anytime a NOT IN condition is mixed with a null in the list, every row will be evaluated as false.