---
title: "Oracle: Is OUTER JOIN Better Than NOT EXISTS?"
description: I was told using NOT EXISTS in (Oracle) SQL is a bad idea, to overcome this you should collect the non-matching rows with an OUTER JOIN. I test it here.
---

[Blog | Pythian ](https://www.pythian.com/blog)

# [Oracle: Is OUTER JOIN Better Than NOT EXISTS?](https://www.pythian.com/blog/oracle-is-outer-join-better-than-not-exists)

 Written by [Pythian Marketing](https://www.pythian.com/blog/author/pythian-marketing) | Jan 7, 2008 5:00:00 AM

I’ve been told that using `NOT EXISTS` in (Oracle) SQL is a bad idea, and that a way to overcome this problem is to collect the non-matching rows with an `OUTER JOIN`. So I decided to check if it is true.

## Establishing the Test Case

In order to start, here is my test case:

```
create table t1(id number,    constraint t1_pk primary key(id));    create table t2(id number);  begin   for i in 1..100 loop     insert into t1 values(i);   end loop;   commit; end;  begin   for i in 1..100000 loop     insert into t2       values(mod(i,97));   end loop;   commit; end; /  create index t2_idx on t2(id);  exec dbms_stats.gather_table_stats(USER,'T1');  exec dbms_stats.gather_table_stats(USER,'T2'); 
```

## Analyzing Execution Plans: NOT EXISTS vs. OUTER JOIN

First, I checked what I’d been told, i.e. that the `OUTER JOIN` is more efficient than the `NOT EXISTS`. In order to do that, I wrote a simple `SELECT` and displayed the plan for both syntaxes (my database is 11.1.0.6 on Linux 32-bits). As I assumed, it’s not the case. In fact, both orders took the same plan.

### Execution Plan with NOT EXISTS

```
explain plan for   select id from t1 a    where not exists     (select 1 from t2 b where b.id=a.id);  select * from table(dbms_xplan.display);  PLAN_TABLE_OUTPUT ----------------------------------------------------------------- Plan hash value: 1906534000  ----------------------------------------------------------------- | Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| ----------------------------------------------------------------- |   0 | SELECT STATEMENT   |       |    96 |   480 |    46   (5)| |* 1 |  HASH JOIN ANTI    |       |    96 |   480 |    46   (5)| |   2 |   INDEX FULL SCAN  | T1_PK |   100 |   300 |     1   (0)| |   3 |   TABLE ACCESS FULL| T2    |   100K|   195K|    44   (3)| -----------------------------------------------------------------  Predicate Information (identified by operation id): ---------------------------------------------------    1 - access("B"."ID"="A"."ID") 
```

### Execution Plan with OUTER JOIN

```
explain plan for   select a.id from t1 a, t2 b    where a.id=b.id(+)      and b.id is null;  select * from table(dbms_xplan.display);  PLAN_TABLE_OUTPUT ----------------------------------------------------------------- Plan hash value: 1906534000  ----------------------------------------------------------------- | Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| ----------------------------------------------------------------- |   0 | SELECT STATEMENT   |       |     3 |    15 |    46   (5)| |* 1 |  HASH JOIN ANTI    |       |     3 |    15 |    46   (5)| |   2 |   INDEX FULL SCAN  | T1_PK |   100 |   300 |     1   (0)| |   3 |   TABLE ACCESS FULL| T2    |   100K|   195K|    44   (3)| ----------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - access("A"."ID"="B"."ID") 
```

## The Verdict: Identical Optimization

I know — the real way to check that both queries are equivalent is to trace the plan generation with a **10053 event**. (I cannot explain this, so I’ll leave that to you.)

However, the original query I’ve been told to rewrite was not a `SELECT`, but the **DELETE** below:

```
delete from t1 a    where not exists     (select 1 from t2 b where b.id=a.id); 
```

I haven’t yet found how to rewrite it in a way that makes it more efficient with an `OUTER JOIN`. I’ll be happy if someone can help me, at least to find the syntax, if not to enhance response time.

## Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?

 

[View full post](https://www.pythian.com/blog/oracle-is-outer-join-better-than-not-exists)

```json
{
  "@context" : "http://schema.org",
  "@type" : "BlogPosting",
  "author" : {
    "@type" : "Person",
    "name" : "Pythian Marketing"
  },
  "dateModified" : "2026-04-15T01:38:40.239Z",
  "datePublished" : "2008-01-07T05:00:00Z",
  "headline" : "Oracle: Is OUTER JOIN Better Than NOT EXISTS?",
  "image" : {
    "@type" : "ImageObject",
    "height" : 60,
    "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png",
    "width" : 60
  },
  "mainEntityOfPage" : "https://www.pythian.com/blog/oracle-is-outer-join-better-than-not-exists",
  "publisher" : {
    "@type" : "Organization",
    "logo" : {
      "@type" : "ImageObject",
      "height" : 60,
      "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png",
      "width" : 60
    },
    "name" : "Pythian Blog"
  }
}
```