Sub query with not equal

Question

Sub query with not equal

table_1


col1
SV0362
RU0177CSPG
NO0124CSPG
VN3582
AT9923CSPG
BZ0324
PE0309
AS4293EXPT
ML0331OMKT

table 2

colA
OO56128665
OO56128665
OO56128665
OO56128665
US0170
US0170
US0170
US0170
US0170
US0170
US0170
US0170
US0170
US0170
OO56128665
OO56128665

I want to compare col1 of table1 with colA of table 2 and return the values which are not qual

something like this

select * from table1 where col1 <> (select colA from table_2 a,table1 b where b.col1=a.colA)

but this would give single row returns more than one subquery

Answer 1

I assume you want rows from table 1 that does not exist in table_2 and vice versa:

SELECT col1, 'table_1' as origin
FROM table_1 x
WHERE NOT EXISTS (
    SELECT 1 FROM table_2 y
    WHERE x.col1 = y.cola
)
UNION
SELECT cola, 'table_2' as origin
FROM table_2 x
WHERE NOT EXISTS (
    SELECT 1 FROM table_1 y
    WHERE y.col1 = x.cola
)

Answer 2

You just need to use NOT IN

SELECT * FROM table1 WHERE col1 NOT IN (SELECT colA FROM table_2)

Answer 3

You can use Oracle MINUS, UNION and INTERSECT also to achieve the same. It is an another way to solve.

i.e. ( A - B ) ∪ ( B - A ) = ( A ∪ B ) - ( A ∩ B )

( select col1  from table_1
   union
  select cola  from table_2
)
minus
( select col1 from table_1
   intersect
  select cola from table_2
);

You can very well use IN or EXISTS in the subqueries also.

( select col1
from table_1
union
select cola
  from table_2
)
minus
( select col1
    from table_1
   where exists ( select 1
                    from table_2
                   where table_1.col1 = table_2.cola
)
);

This SQL Tutorial explains the EXISTS, NOT EXISTS usage.