How to run a SQL query with “where is null”

Sometimes you need to see the items in a SQL table that does not match when left joining two or more tables together.

Here’s an example

whereisnull

Say we have a customer table and an invoicing header table and you would like to see which customers are created but has never bought anything via your ERP.

Customer Setup Table
Table name:
[ARCUS]
IDCUST
NAMECUST
CODECTRY
CODEPSTL

Invoice Header Table.
Table Name:
[OEINVH]
CUSTOMER
INVNUMBER
INVDATE
REFERENCE
DESCRIPTION

The following query will return all customers who has not been used in the accounting system.
Select ARCUS.IDCUST,OEINVH.CUSTOMER
from ARCUS
left join OEINVH on ARCUS.IDCUST=OEINVH.CUSTOMER
where OEINVH.CUSTOMER is null




93 total views, 1 views today