c# print barcode labels Figure 4-3. Execution plan for a NOT IN query in .NET framework

Printing QR in .NET framework Figure 4-3. Execution plan for a NOT IN query

Figure 4-3. Execution plan for a NOT IN query
Denso QR Bar Code Maker In .NET
Using Barcode creator for VS .NET Control to generate, create QR Code image in VS .NET applications.
www.OnBarcode.com
Read Denso QR Bar Code In VS .NET
Using Barcode decoder for .NET Control to read, scan read, scan image in VS .NET applications.
www.OnBarcode.com
[View full size image]
Barcode Printer In Visual Studio .NET
Using Barcode generation for .NET Control to generate, create bar code image in .NET framework applications.
www.OnBarcode.com
Scanning Barcode In VS .NET
Using Barcode decoder for .NET framework Control to read, scan read, scan image in Visual Studio .NET applications.
www.OnBarcode.com
There are some additional operations at the beginning of this plan, compared to the previous plansteps needed to look for NULL CustomerID s. Why is this plan different than the one generated for the NOT EXISTS query And why would SQL Server care particularly about the existence of NULLs in Orders.CustomerID The discrepancy between the plans doesn't affect the result because there's no row in the Orders table with a NULL CustomerID . However, because the CustomerID column allows NULLs, the optimizer must take this fact into consideration. Let's see what happens if we add a row with a NULL CustomerID to the Orders table: INSERT INTO dbo.Orders DEFAULT VALUES;
QR Code JIS X 0510 Generator In Visual C#
Using Barcode maker for .NET Control to generate, create QR Code image in Visual Studio .NET applications.
www.OnBarcode.com
QR Code Encoder In .NET
Using Barcode drawer for ASP.NET Control to generate, create QR-Code image in ASP.NET applications.
www.OnBarcode.com
Now rerun both the NOT EXISTS and NOT IN queries. You will find that the NOT EXISTS query still returns the same output as before, while the NOT IN query now returns an empty set. In fact, when there's a NULL in the Orders.CustomerID column, the NOT IN query will always return an empty set. The reason is that the predicate val IN(val1, val2, ..., NULL) can never return FALSE; rather, it can return only TRUE or UNKNOWN. As a result, val NOT IN(val1, val2, ..., NULL) can only return NOT TRUE or NOT UNKNOWN, neither of which is TRUE. For example, suppose the customer list in this query is (a, b, NULL) . Customer a appears in the list, and therefore the predicate a IN(a, b, NULL) returns TRUE. The predicate a NOT IN(a, b, NULL) returns NOT TRUE, or FALSE, and customer a is not returned by the query. Customer c , on the other hand, does not appear in the list (a, b, NULL) , but the logical result of c IN(a, b, NULL) is UNKNOWN, because of the NULL. The predicate b NOT IN(a, b, NULL) therefore returns NOT UNKNOWN, which equals UNKNOWN, and customer c is not returned by the query, either, even though c does not appear in the customer list. Whether or not a customer appears in the customer list, if the list contains NULL, the customer is not returned by the query. You realize that when NULLs are potentially involved (such as when the queried column allows NULLs), NOT EXISTS and NOT IN are not logically equivalent. This explains the discrepancy between the plans and the potential difference in results. To make the NOT IN query logically equivalent to the EXISTS query, declare the column as NOT NULL (if appropriate) or add a filter to the subquery to exclude NULLs: SELECT CustomerID, CompanyName FROM dbo.Customers AS C WHERE Country = N'Spain' AND CustomerID NOT IN(SELECT CustomerID FROM dbo.Orders WHERE CustomerID IS NOT NULL);
QR Creation In VB.NET
Using Barcode creation for .NET framework Control to generate, create QR Code image in Visual Studio .NET applications.
www.OnBarcode.com
1D Barcode Maker In .NET Framework
Using Barcode generator for .NET framework Control to generate, create Linear image in Visual Studio .NET applications.
www.OnBarcode.com
This query generates the same result as the NOT EXISTS query, as well as the same plan. Once you're done testing the queries, make sure you remove the row with the NULL CustomerID : DELETE FROM dbo.Orders WHERE CustomerID IS NULL;
Code 128B Drawer In .NET Framework
Using Barcode creation for .NET Control to generate, create Code 128 image in Visual Studio .NET applications.
www.OnBarcode.com
Paint Bar Code In .NET
Using Barcode printer for .NET framework Control to generate, create bar code image in Visual Studio .NET applications.
www.OnBarcode.com
Minimum Missing Value
Code-39 Encoder In Visual Studio .NET
Using Barcode creation for Visual Studio .NET Control to generate, create Code39 image in VS .NET applications.
www.OnBarcode.com
Make EAN8 In .NET
Using Barcode printer for .NET framework Control to generate, create GTIN - 8 image in VS .NET applications.
www.OnBarcode.com
To put your knowledge of the EXISTS predicate into action, try to solve the following problem. First create and populate the table T1 by running the code in Listing 4-1 .
Barcode Encoder In None
Using Barcode generation for Excel Control to generate, create barcode image in Excel applications.
www.OnBarcode.com
Code 128 Code Set C Generator In Visual C#.NET
Using Barcode encoder for .NET Control to generate, create Code 128 Code Set B image in .NET framework applications.
www.OnBarcode.com
Listing 4-1. Creating and populating the table T1
Drawing Bar Code In Visual C#
Using Barcode generation for .NET Control to generate, create barcode image in VS .NET applications.
www.OnBarcode.com
Make Quick Response Code In VS .NET
Using Barcode maker for Reporting Service Control to generate, create QR Code ISO/IEC18004 image in Reporting Service applications.
www.OnBarcode.com
USE tempdb; GO IF OBJECT_ID('dbo.T1') IS NOT NULL DROP TABLE dbo.T1; GO CREATE TABLE dbo.T1 ( keycol INT NOT NULL PRIMARY KEY CHECK(keycol > 0), datacol VARCHAR(10) NOT NULL ); INSERT INTO dbo.T1(keycol, datacol) VALUES(3, 'a'); INSERT INTO dbo.T1(keycol, datacol) VALUES(4, 'b'); INSERT INTO dbo.T1(keycol, datacol) VALUES(6, 'c');
Barcode Reader In Visual Studio .NET
Using Barcode scanner for VS .NET Control to read, scan read, scan image in Visual Studio .NET applications.
www.OnBarcode.com
ANSI/AIM Code 39 Encoder In None
Using Barcode generator for Word Control to generate, create Code 39 Extended image in Office Word applications.
www.OnBarcode.com
INSERT INTO dbo.T1(keycol, datacol) VALUES(7, 'd');
Reading PDF417 In None
Using Barcode scanner for Software Control to read, scan read, scan image in Software applications.
www.OnBarcode.com
Printing Universal Product Code Version A In Java
Using Barcode maker for Android Control to generate, create UPCA image in Android applications.
www.OnBarcode.com
Notice that keycol must be positive. Your task is to write a query that returns the lowest missing key, assuming that key values start at 1. For example, the table is currently populated with the keys 3, 4, 6, and 7, so your query should return the value 1. If you insert two more rows, with the keys 1 and 2, your query should return 5. Solution: Here's a suggested CASE expression (incomplete) that I used in my solution: SELECT CASE WHEN NOT EXISTS(SELECT * FROM dbo.T1 WHERE keycol = 1) THEN 1 ELSE (...subquery returning minimum missing value...) END;
If 1 doesn't exist in the table, the CASE expression returns 1; otherwise, it returns the result of a subquery returning the minimum missing value. Here's the subquery that I used to return the minimum missing value: SELECT MIN(A.keycol + 1) as missing FROM dbo.T1 AS A WHERE NOT EXISTS (SELECT * FROM dbo.T1 AS B WHERE B.keycol = A.keycol + 1);
The NOT EXISTS predicate returns TRUE only for values in T1 that are right before a gap (4 and 7 in our case). A value is right before a gap if the value plus one does not exist in the same table. The outer T1 table has the alias A, and the inner T1 table has the alias B. You could use the expression B.keycol 1 = A.keycol in the subquery's filter; although it might be a bit confusing to use such an expression when looking for a value in B that is greater than the value in A by one. If you think about it, for B.keycol to be greater than A.keycol by one, B.keycol minus one must be equal to A.keycol . If this logic confuses you, you can use B.keycol = A.keycol + 1 instead, as I did. Once all points before gaps are isolated, the outer query returns the minimum plus one, which is the first missing value in the first gap. Make a mental note of the technique to identify a point before a gap, as it's a very handy key technique. Now you can incorporate the query returning the minimum missing value in the CASE expression: SELECT CASE WHEN NOT EXISTS(SELECT * FROM dbo.T1 WHERE keycol = 1) THEN 1 ELSE (SELECT MIN(keycol + 1) FROM dbo.T1 AS A WHERE NOT EXISTS (SELECT * FROM dbo.T1 AS B WHERE B.keycol = A.keycol + 1)) END;
If you run this query with the sample data inserted by Listing 4-1 , you should get 1 as the result. If
you then insert two more rows, with the keys 1 and 2 (as shown in the following code), and rerun the query, you should get 5 as the result. INSERT INTO dbo.T1(keycol, datacol) VALUES(1, 'e'); INSERT INTO dbo.T1(keycol, datacol) VALUES(2, 'f');
Here is an example of how you might use the CASE expression for the minimum missing key in an INSERT ... SELECT statement, perhaps in a scenario where you needed to reuse deleted keys: INSERT INTO dbo.T1(keycol, datacol) SELECT CASE WHEN NOT EXISTS(SELECT * FROM dbo.T1 WHERE keycol = 1) THEN 1 ELSE (SELECT MIN(keycol + 1) FROM dbo.T1 AS A WHERE NOT EXISTS (SELECT * FROM dbo.T1 AS B WHERE B.keycol = A.keycol + 1)) END, 'f';
Query the T1 table after running this INSERT, and notice in the output shown in Table 4-8 that the insert generated the key value 5, which was the minimum missing key: SELECT * FROM dbo.T1;
Copyright © OnBarcode.com . All rights reserved.