Could you try something more like:

SELECT *
FROM Deceased
WHERE EXISTS (SELECT Zip FROM ZipCross WHERE Zip = Deceased.Zip AND EXISTS (SELECT AreaId FROM AreaOrder WHERE AreaId = ZipCross.AreaOrder AND CampaignId = 300))

or I have had some luck with breaking those into multiple smaller queries and indexing the temp tables like this:

SELECT AreaId 
INTO #Campaign
FROM AreaOrder 
WHERE CampaignId = 300

CREATE INDEX idxC on #Campaign (AreaId)

SELECT Zip 
INTO #ZipCross
FROM ZipCross 
WHERE Zip = Deceased.Zip AND EXISTS (SELECT AreaId FROM #Campaign WHERE AreaId = ZipCross.AreaId)

CREATE INDEX idxZ on #ZipCross (Zip)

SELECT *
FROM Deceased
WHERE EXISTS (SELECT Zip FROM #ZipCross WHERE Zip = Deceased.Zip)

DROP TABLE #ZipCross
DROP TABLE #Campaign

This way you can time each part of the whole query to see where you should be concentrating.

>>Also, look to the drives the data is stored on. Is the data on a seperate RAID array from the logs? Is this 5 minutes running from the server, or across the network?
>>
>>>First, I have a table called "Deceased" that holds over 6 million names and addresses of deceased persons. I need to do a JOIN on "ZipCross" table that holds all the zipcodes that were mailed for a particular direct mail campaign. Here is my SQL statement:
>>>
>>>SELECT Deceased.* FROM Deceased JOIN ZipCross ON Deceased.Zip=Zipcross.Zip
>>>JOIN AreaOrder C ON B.AreaID=C.AreaID AND C.CampaignID=300
>>>
>>>The problem is that this query takes almost 5 minutes to complete. Of course it doesn't help that the resulting recordset is over one million records. Anyone with ideas to make this query faster or am I doomed 'cause of amount of records returned? TIA.