CODEWITHSUNDEEP
sqloracle-database
user6440081
user6440081

Reputation:

Oracle SQL: Exclude IDs from another table without subquery join

I would like to know if the following is possible without joining the same table twice:

Table A:
+----+------+
| ID | ColA |
+----+------+
|  1 | A1   |
|  2 | A2   |
|  3 | A3   |
|  4 | A4   |
+----+------+

Table B:
+----+------+
| ID | ColB |
+----+------+
|  1 | B1   |
|  2 | B2   |
|  3 | B3   |
|  4 | B4   |
|  5 | B5   |
|  6 | B6   |
+----+------+

Table C:
+----+
| ID | 
+----+
|  1 | 
|  2 | 
+----+

Desired result: (A LEFT JOIN B WITHOUT C)
+----+------+------+
| ID | ColA | ColB |
+----+------+------+
|  3 | A3   | B3   |
|  4 | A4   | B4   |
+----+------+------+

So basically I need to add Column B to Table A, hence left join, and exclude all IDs which occur in Table C.

Current solution:

SELECT a.id, a.ColA, b.ColB
  FROM tableA a
  LEFT JOIN tableB b ON a.id = b.id
  WHERE a.id NOT IN(
    SELECT a2.id FROM tableA a2
    LEFT JOIN tableC c on a2.id = c.id)

What's irritating me is, that the exclusion of table C requires an additional left join of table A with table C. Isn't there a more straight-forward approach, without having to join table A again as part of the subquery, if all I want to do is to exclude IDs which occur in table C from the resultset?.

Thanks

Upvotes: 1

Views: 685

Answers (2)

JohnHC
JohnHC

Reputation: 11205

Use a not exists:

SELECT a.id, a.ColA, b.ColB
FROM tableA a
LEFT JOIN tableB b ON a.id = b.id
where not exists(select 1 from tablec c where a.id = c.id)

The issue with using a not in with a select in Oracle is that:

a) it has to return the whole subquery dataset

b) if there are nulls, it breaks

TOM link regarding these 2 issues

Upvotes: 2

Hogan
Hogan

Reputation: 70538

won't this work?

SELECT a.id, a.ColA, b.ColB
FROM tableA a
JOIN tableB b ON a.id = b.id
WHERE a.id NOT IN (SELECT c.Id FROM tableC c)

this can also be done in a join

SELECT a.id, a.ColA, b.ColB
FROM tableA a
JOIN tableB b ON a.id = b.id
LEFT JOIN tableC C ON a.id = c.id 
WHERE c.Id is null

Upvotes: 1

Related Questions