[VPM] SQL statement

[Malcolm Dew-Jones]

On Tue, 13 May 2003, Nathanael Kuipers wrote:

> Hello hello,
> 
> Hope everyone is well.  I have a quick question about what I think would be 
> called a negated join but I'm not sure.  I have 2 tables:
> 
> 1)blastnhsp
> 2)blastxhsp
> 
> Each of these tables has all the same fields, and no field in either table 
> contains a null value.
> 
> What I want to do is something like the following incorrect statement:
> 
> SELECT query, qfrom, qto FROM blastnhsp WHERE NOT blastxhsp.query;

as an outer join, oracle syntax

	SELECT query, qfrom, qto 
	FROM blastnhsp , blastxhsp
	WHERE blastnhsp.primary_key = blastxhsp.primary_key(+)
	and blastxhsp.primary_key IS NULL;

This joins the two tables on what ever columns are the ones that would
match, but uses an outer join on the second table so that if they don't
exist in the second table then the second set of columns are just null. 

If you then take just the set of records in which the second table columns
are null then you have the records that where unique in the first table.


You can also do this with a not exists, and search in table 2 for the
record of table 1

	SELECT something 
	FROM table1
	WHERE not exists (select * 
			  from table2
			  where table2.columns = table1.columns 
                         );
$0.02