[comp.databases] Need help in optimizing query using "not exists" in SQL

beverly@ai.cs.wisc.edu (Beverly Seavey (-Kung)) (10/02/90)

	WE are compliling a database of boring NMR data. THe way we planned to
screen for errors in the data is to have every piece of data entered
twice, then take the difference. So, for example, I have two tables,
mpshifts and lshifts that the 2 student hourlies have entered data into,
and I want to find if either contains a row that the other does not.
The following SQL query:

  select * from mpshifts where mpshifts.sh_event = 191
	and not exists
	(select *
	 from lshifts
	  where lshifts.sh_event = 191
		and mpshifts.sh_res = lshifts.sh_res
		  and mpshifts.sh_atom = lshifts.sh_atom
		and mpshifts.sh_shift = lshifts.sh_shift);


works ok when there is not much in mpshifts with sh_event = 191,
but dies pretty early otherwise.  Is there another way to do this
query that would be more efficient? I essentially just want to
take the set difference between the two tables, but I don't see
set "minus" or "difference" in the Ingres SQL manual.
Does anyone know how Oracle or Ingres front ends parse this query,
and therefore how to optimize it?
.