Whenever the column returned by the subquery contains a NULL value, NOT IN will always return an empty set. Filtering out the NULL values in the subquery will solve this problem.
select
*
from
table1
where
col1 not in(select col2 from table2 where col2 is not null)
Read SQL Server: JOIN vs IN vs EXISTS - the logical difference for a detailed explanation.
To keep track of favourite web sites, useful links, articles and own experiences which may be helpful in future software development.
Subscribe to:
Post Comments (Atom)
SQL Server
- 101 Things I wish you knew about SQL Server
- 102 SQL Server 2005 Tutorials and Articles
- Audit Trails and Logging Part 1 (sqlservercentral.com)
- Audit Trails and Logging Part 2 (sqlservercentral.com)
- Audit data using SQL Server 2005's COLUMNS_UPDATED function
- Calling a web service from a CLR proc
- Choosing between SQL Server Compact 3.5 and SQL Server 2005 Express Edition
- Deploying Database Developments
- Download SQL Server 2005 Express Edition
- Download SQL Server Compact 3.5
- How to return a result set from a SQL Server 2005 CLR stored procedure (mssqltips.com)
- How to return a result set from a SQL Server 2005 CLR stored procedure (mssqltips.com)
- Online Training (sqlservercentral.com)
- SQL Server 2008 Transparent Data Encryption getting started (MSSQLTips.com)
- SQL Server Audit in SQL Server 2008 – Part 1 (Database Journal)
- SQL Server Data Structure
- SQL Server Indexes: The Basics
- T-SQL Enhancement - APPLY Join Operator (drsql)
- T-SQL Ranking Functions (MSDN)
- T-SQL: Calendar tables
- T-SQL: Correlated Joins Using "Apply" (sqlservercentral.com)
- T-SQL: Identify Modified Columns in a Trigger (SQL Server Magazine)
- T-SQL: Identity Columns (simple-talk)
- T-SQL: More with SQL Server 2005 : Top n Per Group, Paging, and Common Table Expressions
- T-SQL: Obtaining Data as XML from SQL Server (MSDN)
- T-SQL: SQL Server 2005 Programmability Enhancements — Common Table Expressions (codeguru)
- T-SQL: SQL Server 2005: Creating User-defined Functions (Database Engine) (MSDN)
- T-SQL: SQL Server: JOIN vs IN vs EXISTS - the logical difference
- T-SQL: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.
- T-SQL: The Curse and Blessings of Dynamic SQL
- T-SQL: The ultimate guide to the datetime datatypes
- T-SQL: Understanding SQL 2005's new PIVOT clause
- T-SQL: Use Dynamic SQL to Improve Query Performance
- T-SQL: Using the OUTPUT clause for practical SQL Server applications
- T-SQL: What's New in FOR XML in Microsoft SQL Server 2005 (MSDN)
- TechNet: Improving Performance with SQL Server 2005 Indexed Views
- The Truth about "Cursor Busting" in SQL
- Thinking Set-Based .... or not?
- Upgrading a Database SQL 2000 to SQL 2005 (sqlservercentral.com)
DTS and SSIS
- Download SQL Server 2005 DTS add-in
- Dynamically Generate Folders for File Output using Variables in SSIS (sqlservercentral.com)
- How to create an XML config file for a SSIS package
- SQL Server Integration Services (SSIS), ETLs and Best Practices (Brian's Blog)
- Transactions in SQL Server 2005 Integration Services
No comments:
Post a Comment