[FIXED] How to join two tables based on two columns and verifying that one column is existing in both

Issue

I have a table A and a table B. The tables can be joined based on the columns (say x and y in both).

I want to join both tables based on x and y to find out all rows in table A where the couple (x,y) doesn't exist in the table B.

What I do now is:

SELECT * FROM A a
LEFT JOIN B b
ON a.x = b.x AND a.y = B.y
WHERE b.x IS NULL AND b.y IS NULL;

And the result is ok...

But me I want to have couples (x,y) in A that don't exist in B and be sure only for couple where x exist in B...

Any idea?

I have the idea to do the following:

SELECT * FROM A a
LEFT JOIN B b
ON a.x = b.x AND a.y = B.y
WHERE b.x IS NULL AND b.y IS NULL
AND x in (SELECT x FROM B);

And that works but it seems for me not a good way...

Sample data would be:

in A, we have (x,y): (1,2) (1,5) (2,3) (3,7)

in B, we have (x,y): (1,4) (1,5) (3,9)

expected result is:

(1,2) (3,7)

Solution

In my experience, subqueries outside of the FROM clause have generally poor performance, this would probably be faster:

SELECT * 
FROM (SELECT DISTINCT x FROM B) AS bsAs
INNER JOIN A AS a ON bsAs.x = A.x
LEFT JOIN B AS b ON a.x = b.x AND a.y = B.y
WHERE b.x IS NULL AND b.y IS NULL
;

Edit: Don't forget the DISTINCT in the subquery, otherwise you'll get your results for each x value duplicated for every instance of that x value in B.

Answered By - Uueerdo

Answer Checked By - Clifford M. (PHPFixing Volunteer)