outer join?

[edrugtrader]

[FONT=courier new]my_table

col1 | col2
1....| 2
2....| 3
3....| 8
6....| 9
7....| 10
12...| 16

how can i query to get all of the values that are in col2, but not in col1? i think it is outer join but i don't know the syntax

something like

SELECT B.col2 FROM my_table A OUTER JOIN my_table B WHERE A.col1 = B.col2

or something like that... thanks for the help.[/FONT]

[chriskl]

In a database that has subselects:

SELECT col2 FROM my_table WHERE col2 NOT IN (SELECT col1 FROM my_table);

Or you could use UNIONs:

SELECT col2 FROM my_table EXCEPT SELECT col1 FROM my_table;

Both of these work in PostgreSQL, while the second will only work in MySQL 4, and the first won't work at all.

Chris