# Sub query with not equal

Posted on

### 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

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
)
``````

You just need to use `NOT IN`

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

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.