Left join with null instead of where

[jimmasami]

I'm working on a very large database, I have this piece of SQL in my script, but I've been informed that there is a more efficient way of doing this by using LEFT JOIN and checking for Null, but I'm not sure how to do this, any suggestions would be greatly appreciated.
...AND NOT EXISTS
(
SELECT 1
FROM MonthlyAssignment RMP
WHERE RMP.ProjectId = RMA.ProjectId
AND RMP.AssignmentUniqueId = RMA.AssignmentUniqueId
AND RMP.MonthStartDate = RMA.MonthStartDate
)

 

[twifosp]

More efficient way of doing what exactly?

I think we need more info...

 

[jimmasami]

There's about 2 pages of SQL that goes with this, but it is restricting the records returned in the recordset by using the NOT EXISTS, but maybe a LEFT JOIN on the main table (sorry not shown in the script) utilising a NULL value could be a more efficient way of restricting the recordset.

 

[twifosp]

I'm sorry, but that still means nothing in terms of how your data is structured, how you are pulling it, and how you want it pulled.

Perhaps you could give an example of the results your getting vs the results you want and the relevant code?

 

[jimmasami]

Thanks for the help twifosp.

SELECT * FROM MonthlyActuals RMA
WHERE CAST(STR(RMA.Year ,4,0) + '-' +
STR(RMA.Month,2,0) + '-01'
AS DATETIME) >= '01 Jun 2000'
AND CAST(STR(RMA.Year ,4,0) + '-' +
STR(RMA.Month,2,0) + '-01'
AS DATETIME) <= GetDate()
AND NOT EXISTS
(
SELECT 1
FROM MonthlyAssignment RMP
WHERE RMP.ProjectId = RMA.ProjectId
AND RMP.AssignmentUniqueId = RMA.AssignmentUniqueId
AND RMP.MonthStartDate = RMA.MonthStartDate
)

 

[Pattycake245]

Basic syntax for something like that I believe would be:

SELECT RMA.*, RMP.ProjectId, RMP.AssignmentUniqueId,
RMP.MonthStartDate FROM MonthlyActuals RMA
LEFT JOIN MonthlyAssignment RMP
ON
RMP.ProjectId = RMA.ProjectId
AND RMP.AssignmentUniqueId = RMA.AssignmentUniqueId
AND RMP.MonthStartDate = RMA.MonthStartDate
WHERE
RMP.ProjectId is null
AND RMP.AssignmentUniqueId is null
AND RMP.MonthStartDate is null

Although, you may not need all three fields to check for null. I think if the Project_Id is null you wouldn't need to check for the other two. Anyway, I use this syntax for many update and select queries.

And, unless you need to, only select the columns you need instead of select *. I've even seen better performance writing out all of the columns even though you could use select *.

Hope this is what you were looking for.

Tim

 

[jimmasami]

Thanks for the response Pattycake245, I'll try this today, it certainly looks like it might be exactly what I need.
Cheers