Hi all,
I would like to ask if anyone can suggest how to filter out rows of one
table by using negation over several columns of rows of other table.
For example:
create temp table A(col1 varchar, col2 varchar, col3 varchar, col4
varchar);
insert into A values('a', 'A', '1', 'n');
insert into A values('a', 'a', '2', 'e');
insert into A values('b', 'B', '3', 'n');
insert into A values('a', 'A', '4', 'n');
insert into A values('b', 'b', '5', 'e');
insert into A values('c', 'c', '6', 'n');
create temp table B(col1 varchar, col2 varchar, col3 varchar);
insert into B values('a', '1', 'a');
insert into B values('b', '2', 'b');
insert into B values('d', '3', 'd');
insert into B values('e', '3', 'e');
So, in order to get all the rows of table A where col1 and col2 are equal to
any of the col1 and col3 of the rows of table B, one can write:
select A.col1, A.col2, A.col3, A.col4 from A, B where A.col1 = B.col1 and
A.col2 = B.col3;
Which returns:
a|a|2|e
b|b|5|e
However, how could I get the other rows - so those rows in table A which do
NOT match to the rows of table B (using the same columns)?
Just to change the equal signs to non-equal won't work because the tables
are joined - so many rows that are not wanted are returned.
Of course I could write:
select A.col1, A.col2, A.col3, A.col4 from A
except
select A.col1, A.col2, A.col3, A.col4 from A, B where A.col1 = B.col1 and
A.col2 = B.col3;
Which gives correctly:
a|A|1|n
a|A|4|n
b|B|3|n
c|c|6|n
But I wonder if there is a better way (so which perform better) to get those
rows. Any suggestions?
Thanks a lot,
Ran
