1

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

  • 2
    Did you try using not in instead of <> ? – Colin 't Hart Aug 11 '14 at 13:16
  • But you want retrun values only from col1 of table1? – miracle173 Aug 11 '14 at 14:18
  • 2
    MINUS is designed for this kind of problems. right? – Raj Aug 11 '14 at 16:55
  • @miracl173 :yes – user41960 Aug 12 '14 at 15:14
  • 1
    Do you want to return values form both tables that have no match in the other table? Or just the values from table1.col1 that are not in table2.colA? – RBarryYoung Aug 15 '18 at 17:15
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
)
1

You just need to use NOT IN

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

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.

  • 1
    Not bad but you could use JOIN instead of the EXISTS subquery or even better INTERSECT, to match the set operators in your formula. – ypercubeᵀᴹ Nov 24 '18 at 20:46
  • Thanks @ypercubeᵀᴹ . :-) I modified the answer. – Sameer Pradhan Nov 24 '18 at 22:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.