Select rows which are there in one table and not there in another table by matching two columns valu-CodePudding

I have two tables as shown below

t1:

cust_id   product       score
1         bat           0.8
2         ball          0.3
2         phone         0.6
3         tv            1.0
2         bat           1.0
4         phone         0.2
1         ball          0.6

t2:

cust_id          product
1                bat
2                phone
3                tv

From the above table I would like to filter rows which are there in t1 and not in t2 by matching the columns cust_id and product

Expected output:

cust_id   product       score
2         ball          0.3
2         bat           1.0
4         phone         0.2
1         ball          0.6

I tried below query but it does not works.

   SELECT t1.* 
     FROM t1
LEFT JOIN t2
       ON t1.cust_id = t1.cust_id
     WHERE t2.product IS NUL

CodePudding user response：

...by matching two columns values

means that you should use both columns in the ON clause:

SELECT t1.* 
FROM t1 LEFT JOIN t2
ON t2.cust_id = t1.cust_id AND t2.product = t1.product
WHERE t2.cust_id IS NULL;

In the condition in the WHERE clause you may use any of the 2 columns.

CodePudding user response：

Since you only require columns from t1 here you should use not exists which allows the optimiser to avoid fully joining the tables before filtering, you specifiy the columns that should be correlated as part of the exists criteria:

select cust_id, product, score
from t1
where not exists (
  select * from t2
  where t2.cust_id = t1.cust_id and t2.product = t1.product
);