| Not Exists vs Left Outer Join with NULL [message #647878] |
Wed, 10 February 2016 09:52  |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Hi,
Is there any result difference between not exists vs left outer join with is null condition.
WITH tablea
AS (SELECT 1 id FROM DUAL
UNION ALL
SELECT 2 id FROM DUAL
UNION ALL
SELECT 3 id FROM DUAL
UNION ALL
SELECT 4 id FROM DUAL),
tableb
AS (SELECT 1 id FROM DUAL
UNION ALL
SELECT 4 id FROM DUAL)
SELECT *
FROM tablea a
WHERE NOT EXISTS
(SELECT 1
FROM tableb b
WHERE a.id = b.id);
WITH tablea
AS (SELECT 1 id FROM DUAL
UNION ALL
SELECT 2 id FROM DUAL
UNION ALL
SELECT 3 id FROM DUAL
UNION ALL
SELECT 4 id FROM DUAL),
tableb
AS (SELECT 1 id FROM DUAL
UNION ALL
SELECT 4 id FROM DUAL)
SELECT a.*
FROM tablea a LEFT OUTER JOIN tableb b ON (a.id = b.id)
WHERE b.id IS NULL;
Is there any case when they can produce different results. I remember I once saw the difference in results but not able to reconstruct that use case.
Thanks,
Manu
|
|
|
|
| Re: Not Exists vs Left Outer Join with NULL [message #647884 is a reply to message #647878] |
Wed, 10 February 2016 15:33  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
SQL> @$HOME/a.sql
SQL> WITH tablea
2 AS (SELECT 1 id FROM DUAL
3 UNION ALL
4 SELECT 2 id FROM DUAL
5 UNION ALL
6 SELECT 3 id FROM DUAL
7 UNION ALL
8 SELECT 4 id FROM DUAL),
9 tableb
10 AS (SELECT 1 id FROM DUAL
11 UNION ALL
12 SELECT 4 id FROM DUAL)
13 SELECT *
14 FROM tablea a
15 WHERE NOT EXISTS
16 (SELECT 1
17 FROM tableb b
18 WHERE a.id = b.id);
Execution Plan
----------------------------------------------------------
Plan hash value: 3857091967
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 24 | 13 (8)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 4 | 24 | 13 (8)| 00:00:01 |
| 2 | VIEW | | 4 | 12 | 8 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
| 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 8 | VIEW | | 2 | 6 | 4 (0)| 00:00:01 |
| 9 | UNION-ALL | | | | | |
| 10 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 11 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")
SQL>
SQL> WITH tablea
2 AS (SELECT 1 id FROM DUAL
3 UNION ALL
4 SELECT 2 id FROM DUAL
5 UNION ALL
6 SELECT 3 id FROM DUAL
7 UNION ALL
8 SELECT 4 id FROM DUAL),
9 tableb
10 AS (SELECT 1 id FROM DUAL
11 UNION ALL
12 SELECT 4 id FROM DUAL)
13 SELECT a.*
14 FROM tablea a LEFT OUTER JOIN tableb b ON (a.id = b.id)
15 WHERE b.id IS NULL;
Execution Plan
----------------------------------------------------------
Plan hash value: 4127306397
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 24 | 13 (8)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN OUTER| | 4 | 24 | 13 (8)| 00:00:01 |
| 3 | VIEW | | 4 | 12 | 8 (0)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 8 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 9 | VIEW | | 2 | 6 | 4 (0)| 00:00:01 |
| 10 | UNION-ALL | | | | | |
| 11 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 12 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"."ID" IS NULL)
2 - access("A"."ID"="B"."ID"(+))
SQL>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
