select distinct top 100

i.invoice_id, i.buyer_code, i.submit_time, i.request_id

from

dbo.invoice i inner join dbo.invoice_adj ia on i.invoice_id = ia.invoice_id and ia.amount !=0

where

(i.detail_amount != 0 or i.tax_amount!=0)

and not exists (select 1

from dbo.invoice_detail iad

where i.invoice_id = iad.invoice_id)

and not exists (select 1

from dbo.unprocess_invoice_detail uiad

where i.invoice_id = uiad.invoice_id)

order by i.submit_time desc

I would also like to see the total row counts for each table as well as the following counts:

1) invoice rows where either detailamount or taxamount are non-zero

2) invoicedetails that do not have any unprocessinvoicedetail rows, but only for the invoicedetails rows where invoices match your non-zero clause

Given certain data volumes and data value distributions it is quite possible that the most (and possibly ONLY) efficient way to answer this query is to have a trigger that maintains a table with row keys that match the conditionals involved. With proper indexes in place that trigger should be extremely efficient.

Of course there are likely other data volumes and data value distributions where (filtered?) indexing could make performance acceptable.