[adsense id=”0514458240″ width=”468″ height=”60″]
Oracle provides a nice syntax for determining all entries in one dataset that do not exist in another. If we look at SET operations we can see to datasets A and B. 
In the example below we want to show all the entries that exist in dataset A but which do not exist in dataset B. The example we will show below consists of two tables, one an employee table and another a table of employees assigned to projects. Assuming one employee can only work on a single project. We will use the MINUS operator to find employees that are available for projects.
In our example we are going to have a table which contains all employees called “emp”. We will have 6 employees: Tom, Dick, Harry, Jade, Jasmin and Rose.
SQL> create table emp (id int, name varchar2(100)); Table created. SQL> insert into emp(id, name) values (1, 'Tom'); 1 row created. SQL> insert into emp(id, name) values (2, 'Dick'); 1 row created. SQL> insert into emp(id, name) values (3, 'Harry'); 1 row created. SQL> insert into emp(id, name) values (4, 'Jade'); 1 row created. SQL> insert into emp(id, name) values (5, 'Jasmin'); 1 row created. SQL> insert into emp(id, name) values (6, 'Rose'); 1 row created.
Now we will create a table where employees are assigned to a project. The table is very simple and consists of just the employee id and a project id. In the new assigned project table we will assign Dick and Jasmin to a project.
SQL> create table emp_assigned (id int, projectid int); Table created. SQL> insert into emp_assigned(id, projectid) values (2, 1); 1 row created. SQL> insert into emp_assigned(id, projectid) values (5, 2); 1 row created.
Now that everything is setup we will first list the id’s of the employees that have not yet been assigned to a project.
SQL> select id from emp 2 minus 3 select id from emp_assigned; ID ---------- 1 3 4 6
We can expand this to return the names of the employees not yet listed.
SQL> col name format a15 SQL> select emp.id, name 2 from emp, (select id from emp minus select id from emp_assigned) ass 3 where emp.id = ass.id 4 order by id; ID NAME ---------- --------------- 1 Tom 3 Harry 4 Jade 6 Rose
The MINUS operation can also be performed using alternative SQL syntax. I have shown an example of how this can be done in MySQL