SQL Query to check not repeated values

I’m new to SQL. I wanted to know, How to check the entries which are not repeated for the other entries.

Better I give one example.

column1	column2
a	1
a	2
a	3
a	4
b	1
b	2
b	3
b	4
c	1
c	2
c	3

I want output as

column1	column2
c	4

because c does not have 4, but the other values a and b do.

Answer

You are looking for missing entries. For these to find, you must know which entries you expect. You expect to find one entry for all combinations of column1 values and column2 values. You get these with a cross join.

Now you want to select all these pairs except for those already in the table. You can use EXCEPT for this or NOT EXISTS or NOT IN. You haven’t told us your DBMS, so I don’t know what it features.

EXCEPT

select c1.column1, c2.column2
from (select distinct column1 from mytable) c1
cross join (select distinct column2 from mytable) c2
except
select column1, column2
from mytable
order by column1, column2;

NOT IN

select c1.column1, c2.column2
from (select distinct column1 from mytable) c1
cross join (select distinct column2 from mytable) c2
where (c1.column1, c2.column2) not in
(
    select column1, column2 from mytable
)
order by c1.column1, c2.column2;

NOT EXISTS

select c1.column1, c2.column2
from (select distinct column1 from mytable) c1
cross join (select distinct column2 from mytable) c2
where not exists
(
    select null
    from mytable
    where mytable.column1 = c1.column1
    and mytable.column2 = c2.column2
)
order by c1.column1, c2.column2;