0

I'm developing on the OCEP platform which uses CQL.

I'm looking for a way to select values from one table which do not exist in another. CQL does not allow the use of sub queries so I can't simply use "not in (query)".

Looking for a replacement for this:

select * 
from tb1 
where tb1.id not in (select id from tb2)

Table1: 1,3,4,5,6. Table2: 1,4,7,9.

I'm looking for a way to get a table with (3,5,6) as values.

Any ideas would be welcome :)

Alexei Babin
  • 1
  • 1

1 Answers1

1

This can also be done using an outer join:

select one_table.*
from one_table
  left join another on one_table.pk_column = another.fk_column
where another.pk_column is null;

Your example then maps to this query:

select tb1.*
from tb1 
  left join tb2 on tb1.id = tb2.id
where tb2.id is null;

SQLFiddle example: http://sqlfiddle.com/#!4/4594f/2