Oracle – Using SQL MINUS operator

[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