Labels

admin (1) aix (1) alert (1) always-on (2) Architecture (1) aws (3) Azure (1) backup (3) BI-DWH (10) Binary (3) Boolean (1) C# (1) cache (1) casting (3) cdc (1) certificate (1) checks (1) cloud (3) cluster (1) cmd (7) collation (1) columns (1) compilation (1) configurations (7) Connection-String (2) connections (6) constraint (6) copypaste (2) cpu (2) csv (3) CTE (1) data-types (1) datetime (23) db (547) DB2 (1) deadlock (2) Denali (7) device (6) dotNet (5) dynamicSQL (11) email (5) encoding (1) encryption (4) errors (124) excel (1) ExecutionPlan (10) extended events (1) files (7) FIPS (1) foreign key (1) fragmentation (1) functions (1) GCP (2) gMSA (2) google (2) HADR (1) hashing (3) in-memory (1) index (3) indexedViews (2) insert (3) install (10) IO (1) isql (6) javascript (1) jobs (11) join (2) LDAP (2) LinkedServers (8) Linux (15) log (6) login (1) maintenance (3) mariadb (1) memory (4) merge (3) monitoring (4) MSA (2) mssql (444) mssql2005 (5) mssql2008R2 (20) mssql2012 (2) mysql (36) MySQL Shell (5) network (1) NoSQL (1) null (2) numeric (9) object-oriented (1) offline (1) openssl (1) Operating System (4) oracle (7) ORDBMS (1) ordering (2) Outer Apply (1) Outlook (1) page (1) parameters (2) partition (1) password (1) Performance (103) permissions (10) pivot (3) PLE (1) port (4) PostgreSQL (14) profiler (1) RDS (3) read (1) Replication (12) restore (4) root (1) RPO (1) RTO (1) SAP ASE (48) SAP RS (20) SCC (4) scema (1) script (8) security (10) segment (1) server (1) service broker (2) services (4) settings (75) SQL (74) SSAS (1) SSIS (19) SSL (8) SSMS (4) SSRS (6) storage (1) String (35) sybase (57) telnet (2) tempdb (1) Theory (2) tips (120) tools (3) training (1) transaction (6) trigger (2) Tuple (2) TVP (1) unix (8) users (3) vb.net (4) versioning (1) windows (14) xml (10) XSD (1) zip (1)

NULL in JOIN

Problem: Join 2 tables, but there are some fit cases with NULL value – and they are not return.

Fast fix: ISNULL in the JOIN.

But, let's take a look to pay attention on this.

create table #aaa (id int, thedate datetime)
create table #bbb (id int, thedate datetime)

insert into #aaa (id, thedate) values (1, '2010-01-01'), (2, NULL), (3, getdate())
insert into #bbb (id, thedate) values (1, '2005-07-01'), (2, NULL), (3, getdate())

-- "equel" nulls will not be return
select *
from #aaa a
join #bbb b   on     a.id = b.id
                     and b.thedate = a.thedate






-- only "equel" nulls will be return
select *
from #aaa a
join #bbb b   on     a.id = b.id
where  b.thedate is null
and           a.thedate is null






-- all joins will be return
select *
from #aaa a
join #bbb b   on     a.id = b.id
                     and isnull(b.thedate, '1900-01-01') = isnull(a.thedate, '1900-01-01')







-- PAY ATTENTION: if the "isnull data" is a possible value in your tables - it can be a problem:
insert into #aaa (id, thedate) values (4, '1900-01-01')
insert into #bbb (id, thedate) values (4, NULL)

select *
from #aaa a
join #bbb b   on     a.id = b.id
                     and isnull(b.thedate, '1900-01-01') = isnull(a.thedate, '1900-01-01')
 







drop table #aaa
drop table #bbb


So:
ISNULL(AAA, GETDATE) can't be a 100% solution to join on datetime,
ISNULL on numeric columns MUST take into consideration which value will be in the ISNULL (common mistake is to put zero – it's a valid numeric value!!!),
ISNULL(AAA, N'') can't be a 100% solution to join on strings (empty string it's a valid numeric value!!!),
And so on...

No comments:

Post a Comment