SQL Query to compare two columns with one column equal to a column in another table and second colum-CodePudding

Table structure is as below

t1 PK:(id,item)

id	item
1	1
1	2
1	3
2	1
2	2
2	3

t2 PK:(id,item)

id	item
1	1
1	2
2	1
2	3

output expected: t1

id	item
1	3
2	2

I tried the below query but it didn't work, I am getting all records of t1 whereas the expectation is only those are not matching in t2, Note: I am trying it without using sub query

select 
    a.id, a.item 
from 
    t1 a, t2 b, 
where 
    a.id = b.id and b.item <> b.item;

CodePudding user response：

The logic in your WHERE clause can be made to work if we rephrase your query using exists:

SELECT id, item
FROM yourTable t1
WHERE NOT EXISTS (
    SELECT 1
    FROM yourTable t2
    WHERE t2.id = t1.id AND t2.item = t1.item
);