SQL Except Select with different number of columns in two tables

Question

I have two SQL Tables, which both have three columns

Normal

val_one, val_two, bool_val
1, 2, False
3, 4, False

Temp

val_one, val_two, bool_val
1, 2, True

I have a SQL statement that can select all of the rows from one that don't exist in the other table.

"SELECT val_one, val_two, bool_val FROM temp EXCEPT SELECT val_one, val_two, bool_val FROM normal;"

However, this has the problem that it also returns rows with different bool values, which is not what I want. So if I query, I only want to get back rows with different (val_one, val_two) values

In this example, my query would ideally return only

3, 4, False

How can I modify the current statement to do this?

Answer 1

Use not exists as below

SELECT val_one, val_two, bool_val FROM normal n
where not exists 
     (
          SELECT 1 FROM temp t  where t.val_one=n.val_one and t.val_two=n.val_two
      )

DB-Fiddle

 create table normal(val_one int, val_two int, bool_val varchar(10));
 insert into normal values(1, 2, False);
 insert into normal values(3, 4, False);

 create table temp(val_one int, val_two int, bool_val varchar(10));
 insert into temp values(1, 2, True);

Query:

 SELECT val_one, val_two, bool_val FROM normal n
 where not exists 
      (
           SELECT 1 FROM temp t  where t.val_one=n.val_one and t.val_two=n.val_two
       )

Output:

val_one	val_two	bool_val
3	4	false

db<fiddle here