Sql select what is not in

Compound Statement Syntax. Variables in Stored Programs. Local Variable Scope and Resolution. Flow Control Statements. Restrictions on Server-Side Cursors. Restrictions on Condition Handling. Database Administration Statements. Account Management Statements. Resource Group Management Statements.

Table Maintenance Statements. Component, Plugin, and Loadable Function Statements. Other Administrative Statements. This next set of example queries is looking for account persons who have made exactly 6, 8, or 9 sales. This would be true for any column with a numeric data type int, bigint, smallint, tinyint, numeric, decimal, float, real, money as well. The NOT IN operator can be used to search for any date or datetime value except those match that match one from a list of date or datetime values.

Imagine a scenario where the WideWorldImporters wanted to get an average number of items ordered daily per customer for the calendar year , but wanted to exclude some holidays as they may skew the results lower.

The query to determine this output may look something like below. There are several ways to handle entering date and datetime values, but the most common method is to use quotes and type in a string that can be easily converted to a date or datetime. There are 2 important things to remember when building a subquery that will be placed in the parentheses of a NOT IN operator. First, it must return exactly 1 column. An error will be raised if any other number of columns are selected. Second, the query must be able to be run by itself — meaning it has no outside dependencies.

Consider a scenario where an unexpected weather pattern is limiting commerce in the state of California. WideWorldImporters needs to build a list of items that it can order and for which it can get prompt delivery. These would be any stock items that are not sourced from California-based suppliers. Can a query be written to help identify available items? This query lists the 3 suppliers that are based in California.

Notice that the query fits the rules above in that it can be executed and returns exactly one column. Simply place this query inside the parentheses following the NOT IN operator, but do so without the semi colon. The following query does just that. It will search for items in that are sourced from any non-California supplier.

All nine queries give the same results, but does any one approach perform better? Again, the code download file includes the test harness code, and all nine queries. However elegant your SQL, the optimizer merely shrugs and comes up with an efficient plan to execute it.

There is a certain unreality in comparing sets with null values in them, but if it happens in the heat of everyday database reporting, things can go very wrong. Listing 3 demonstrates the problem. For the IN variant of our query, this does not cause a problem because it resolves to the following:. It is worth being cautious. Find out more. Phil Factor demonstrates the use of temporary tables and table variables, and offers a few simple rules to decide if a table variable will give better performance than a temp table ST , or vice-versa ST There are no advantages to using old-style join syntax.

If SQL prompt identifies its use in legacy code, then rewriting the statements to use ANSI-standard join syntax will simplify and improve the code.