# 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)

## 0 Comments:

## Post a Comment

Note: Only a member of this blog may post a comment.