How do I select all id's that are present in one table but not in another

Question

I am trying to get a list of department Ids are present in one table, (PS_Y_FORM_HIRE), but which don't exist in another table (PS_DEPARTMENT_VW).

Here is the basics of what I have which isn't working:

SELECT h.DEPTID FROM PS_Y_FORM_HIRE h, PS_DEPARTMENT_VW d WHERE NOT EXISTS (
    SELECT d1.DEPTID FROM PS_DEPARTMENT_VW d1 WHERE d1.DEPTID = h.DEPTID 
        and d1.SETID_GL_DEPT = 'IDBYU'
);

I'm trying to form this query in SQL Developer, but it just returns a long list of blanks (after spinning/running the query for a very long time).

In addition, I need this to be effective dated, so that it only grabs the correct effective-dated row, but I was unsure how and where to incorporate this into the query.

EDIT I neglected to mention that only the department table is effective dated. The form hire table is not. I need to get the current effectively dated row from that in this query (to make sure the data is accurate).

Also note that DEPTID isn't a key on PS_Y_FORM_HIRE, but is on PS_DEPARTMENT_VW. (Along with SETID_GL_DEPT and EFFDT).

So again, ideally, I will have a list of all the department ids that appear in PS_Y_FORM_HIRE, but which are not in PS_DEPARTMENT_VW.

Answer 1

SELECT DEPTID
FROM   PS_Y_FORM_HIRE
MINUS
SELECT DEPTID
FROM   PS_DEPARTMENT_VW
WHERE  SETID_GL_DEPT = 'IDBYU';

or

SELECT DEPTID
FROM   PS_Y_FORM_HIRE
WHERE  DEPTID NOT IN (
  SELECT DEPTID
  FROM   PS_DEPARTMENT_VW
  WHERE  SETID_GL_DEPT = 'IDBYU'
)

or

SELECT DEPTID
FROM   PS_Y_FORM_HIRE h
WHERE  NOT EXISTS (
  SELECT 1
  FROM   PS_DEPARTMENT_VW d
  WHERE  SETID_GL_DEPT = 'IDBYU'
  AND    d.DEPTID = h.DEPTID
)