I'm facing a problem using INNER JOIN and would like some help from the community.

I have this 2 tables:

Table 1

Table 2:

My query:

SELECT t1.member_id, t2.cod_id, t2.compl_id
FROM Table_1 t1
INNER JOIN Table_2 t2
    ON t2.member_id = t1.member_id
      AND t2.cod_id <> t1.cod_id
      AND t2.compl_id <> t1.compl_id

Result obtained:

Expected result:

I already understand what is happening here for these values to be returned, but I'm not sure how to get around the situation.

CodePudding user response：

You just can use EXCEPT set operator such as

SELECT * FROM table2 EXCEPT SELECT * FROM table1

but if you need full-difference between two tables, then combine the results such as

(SELECT * FROM table2 EXCEPT SELECT * FROM table1)
UNION ALL
(SELECT * FROM table1 EXCEPT SELECT * FROM table2)

CodePudding user response：

Try the below Query using Where after the join and the "NOT IN" Subquery

SELECT t1.member_id, t2.cod_id, t2.compl_id
FROM Table_1 t1
INNER JOIN Table_2 t2
ON t2.member_id = t1.member_id
  Where t2.cod_id NOT IN (select cod_id from Table_1)
  AND t2.compl_id NOT IN (select compl_id from Table_1)