Optimizing SQL query using JOIN instead of NOT IN

Question

I have a sql query that I'd like to optimize. I'm not the designer of the database, so I have no way of altering structure, indexes or stored procedures.

I have a table that consists of invoices (called faktura) and each invoice has a unique invoice id. If we have to cancel the invoice a secondary invoice is created in the same table but with a field ("modpartfakturaid") referring to the original invoice id.

Example of faktura table:

invoice 1: Id=152549, modpartfakturaid=null

invoice 2: Id=152592, modpartfakturaid=152549

We also have a table called "BHLFORLINIE" which consists of services rendered to the customer. Some of the services have already been invoiced and match a record in the invoice (FAKTURA) table.

What I'd like to do is get a list of all services that either does not have an invoice yet or does not have an invoice that's been cancelled.

What I'm doing now is this:

`SELECT
    dbo.BHLFORLINIE.LeveringsDato AS treatmentDate,
    dbo.PatientView.Navn AS patientName,
    dbo.PatientView.CPRNR AS patientCPR
FROM
    dbo.BHLFORLINIE
INNER JOIN dbo.BHLFORLOEB 
    ON dbo.BHLFORLOEB.BhlForloebID = dbo.BHLFORLINIE.BhlForloebID
INNER JOIN dbo.PatientView 
    ON dbo.PatientView.PersonID = dbo.BHLFORLOEB.PersonID
INNER JOIN dbo.HENVISNING 
    ON dbo.HENVISNING.BhlForloebID = dbo.BHLFORLOEB.BhlForloebID
LEFT JOIN dbo.FAKTURA 
    ON dbo.BHLFORLINIE.FakturaId = FAKTURA.FakturaId
WHERE
    (dbo.BHLFORLINIE.LeveringsDato >= '2017-01-01' OR dbo.BHLFORLINIE.FakturaId IS NULL) AND
    dbo.BHLFORLINIE.ProduktNr IN (110,111,112,113,8050,4001,4002,4003,4004,4005,4006,4007,4008,4009,6001,6002,6003,6004,6005,6006,6007,6008,7001,7002,7003,7004,7005,7006,7007,7008) AND
    ((dbo.FAKTURA.FakturaType = 0 AND 
      dbo.FAKTURA.FakturaID NOT IN (
      SELECT FAKTURA.ModpartFakturaID FROM FAKTURA WHERE FAKTURA.ModpartFakturaID IS NOT NULL
      )) OR 
    dbo.FAKTURA.FakturaType IS NULL) 
 GROUP BY
    dbo.PatientView.CPRNR,
    dbo.PatientView.Navn,
    dbo.BHLFORLINIE.LeveringsDato`

Is there a smarter way of doing this? Right now the added the query performs three times slower because of the "not in" subquery.

Any help is much appreciated!

Peter

score 0 · Answer 1 · answered May 31 '17 at 16:14

0

You can use an outer join and check for null values to find non matches

SELECT customer.name, invoice.id 
FROM invoices i
INNER JOIN customer ON i.customerId = customer.customerId 
LEFT OUTER JOIN invoices i2 ON i.invoiceId = i2.cancelInvoiceId
WHERE i2.invoiceId IS NULL

answered May 31 '17 at 16:14

FJT

1,923
1
15
14

Thank you for the speedy reply! I think I might have given a really bad example - sorry! I've modified my original question to show the query I'm doing. The invoice table is not the one I'm joining from. – Peter Andreas Rothe May 31 '17 at 17:29

Optimizing SQL query using JOIN instead of NOT IN

1 Answers1