CHECK FOR DATA USING NOT EXISTS IN B CHILD TABLE COMPARED TO A SOURCE TABLE

CHECK FOR DATA USING NOT EXISTS IN B TABLE  COMPARED TO A SOURCE TABLE

SELECT ID

FROM  ITEMSA(NOLOCK) A

WHERE NOT EXISTS

(

    SELECT INVOICEITEM_ID

    FROM  ITEMSB (NOLOCK) B

    WHERE A.Id = B.ID

)

GO

SELECT DISTINCT A.ID

FROM ITEMS(NOLOCK) A

LEFT JOIN JLRMCTPRD.[PRDDOL].[INVOICEITEMS] (NOLOCK) B

    ON A.Id = B.INVOICEITEM_ID

WHERE B.INVOICEITEM_ID IS NULL

-- using EXISTS to check for existence

SELECT P.ProductID

FROM Production.Product p

WHERE EXISTS

(

    SELECT 1

    FROM Sales.SalesOrderDetail sod

    WHERE sod.ProductID = p.ProductID

)

-- Using LEFT JOIN to check for existence

GO

SELECT DISTINCT P.ProductID

FROM Production.Product p

LEFT JOIN Sales.SalesOrderDetail sod

    ON sod.ProductID = p.ProductID

WHERE sod.SalesOrderDetailID IS NOT NULL

REFERENCE: http://sqlserverplanet.com/tsql/comparing-exists-vs-left-join-where-not-null