Left join in SQL to identify certain rows

Question

Given below is table A:

Cust_id	Code
1	101
1	102
1	103
2	201

Table B:

Cust_id	Code
1	101
1	102

Table B has only customer 1 and contains only two product codes of customer 1. I want to identify the code of customers in Table B that is not present in Table B.

Cust_id	Code
1	103

To get this, I did Table A left join Table B on cust_id and code and thought those with null values of code in Table B would give the desired result, but it did not seem to work. It would be really helpful if someone could tell what should the correct steps be. Thanks

My query so far:

select a.cust_id, a.code, b.cust_id as customer, b.code as product 
from a 
left join b on a.cust_id = b.cust_id and a.code = b.code

Answer 1

Two conditions:

• cust_id must be in table B
• (cust_id, code) must not be in table B

The query with the conditions almost literally translated from human language to SQL:

select *
from a
where cust_id in (select cust_id from b)
and (cust_id, code) not in (select cust_id, code from b);