| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle EXISTS
Chuck wrote:
> cochrane68_at_hotmail.com wrote in
> news:1118065874.206271.123930_at_g49g2000cwa.googlegroups.com:
>
> > I'm having a little trouble understanding the EXISTS operator. Why
> > do the following SQL statements return a different number of records:
> >
> > SELECT t.id, lt.id
> > FROM table t, link_table lt
> > WHERE lt.id(+) = t.id;
> >
> >
> > SELECT t.id
> > FROM table1 t
> > WHERE
> > EXISTS (
> > SELECT 0
> > FROM link_table1 lt
> > WHERE lt.id(+) = t.id
> > );
> >
> > I thought EXISTS basically allows you to join tables in subselect
> > statements, but there appears to be some subtle differences I'm
> > missing.
> >
> >
>
> The (+) operator is an outter join operator. Your first query is
> returning everything in table that is NOT IN link_table.
>
> In this case I think you are looking for a NOT EXISTS query.
>
> select t.id
> from table t
> where NOT EXISTS (
> select 0
> from link_table lt
> where lt.id = t.id
> );
Incorrect. The outer join returns all matched and unmatched records between the two tables, NOT just those which are unmatched.
As shown below the first query returnes, as expected, all matched and unmatched records between the two tables (I've loaded tables with data, t with ids from 1 through 1000 inclusive, and link_table with values 2 through 1000 where values are even, and I've analyzed the data in both tables; no indexes are present):
SQL> SELECT t.id, lt.id
2 from t, link_table lt
3 WHERE lt.id(+) = t.id;
ID ID
---------- ----------
2 2
4 4
6 6
8 8
10 10
12 12
14 14
16 16
18 18
20 20
22 22
ID ID
---------- ----------
24 24
26 26
28 28
30 30
32 32
34 34
36 36
38 38
40 40
42 42
44 44
ID ID
---------- ----------
46 46
48 48
50 50
52 52
54 54
56 56
58 58
60 60
62 62
64 64
66 66
ID ID
---------- ----------
68 68
70 70
72 72
74 74
76 76
78 78
80 80
82 82
84 84
86 86
88 88
ID ID
---------- ----------
90 90
92 92
94 94
96 96
98 98
100 100
102 102
104 104
106 106
108 108
110 110
ID ID
---------- ----------
112 112
114 114
116 116
118 118
120 120
122 122
124 124
126 126
128 128
130 130
132 132
ID ID
---------- ----------
134 134
136 136
138 138
140 140
142 142
144 144
146 146
148 148
150 150
152 152
154 154
ID ID
---------- ----------
156 156
158 158
160 160
162 162
164 164
166 166
168 168
170 170
172 172
174 174
176 176
ID ID
---------- ----------
178 178
180 180
182 182
184 184
186 186
188 188
190 190
192 192
194 194
196 196
198 198
ID ID
---------- ----------
200 200
202 202
204 204
206 206
208 208
210 210
212 212
214 214
216 216
218 218
220 220
ID ID
---------- ----------
222 222
224 224
226 226
228 228
230 230
232 232
234 234
236 236
238 238
240 240
242 242
ID ID
---------- ----------
244 244
246 246
248 248
250 250
252 252
254 254
256 256
258 258
260 260
262 262
264 264
ID ID
---------- ----------
266 266
268 268
270 270
272 272
274 274
276 276
278 278
280 280
282 282
284 284
286 286
ID ID
---------- ----------
288 288
290 290
292 292
294 294
296 296
298 298
300 300
302 302
304 304
306 306
308 308
ID ID
---------- ----------
310 310
312 312
314 314
316 316
318 318
320 320
322 322
324 324
326 326
328 328
330 330
ID ID
---------- ----------
332 332
334 334
336 336
338 338
340 340
342 342
344 344
346 346
348 348
350 350
352 352
ID ID
---------- ----------
354 354
356 356
358 358
360 360
362 362
364 364
366 366
368 368
370 370
372 372
374 374
ID ID
---------- ----------
376 376
378 378
380 380
382 382
384 384
386 386
388 388
390 390
392 392
394 394
396 396
ID ID
---------- ----------
398 398
400 400
402 402
404 404
406 406
408 408
410 410
412 412
414 414
416 416
418 418
ID ID
---------- ----------
420 420
422 422
424 424
426 426
428 428
430 430
432 432
434 434
436 436
438 438
440 440
ID ID
---------- ----------
442 442
444 444
446 446
448 448
450 450
452 452
454 454
456 456
458 458
460 460
462 462
ID ID
---------- ----------
464 464
466 466
468 468
470 470
472 472
474 474
476 476
478 478
480 480
482 482
484 484
ID ID
---------- ----------
486 486
488 488
490 490
492 492
494 494
496 496
498 498
500 500
502 502
504 504
506 506
ID ID
---------- ----------
508 508
510 510
512 512
514 514
516 516
518 518
520 520
522 522
524 524
526 526
528 528
ID ID
---------- ----------
530 530
532 532
534 534
536 536
538 538
540 540
542 542
544 544
546 546
548 548
550 550
ID ID
---------- ----------
552 552
554 554
556 556
558 558
560 560
562 562
564 564
566 566
568 568
570 570
572 572
ID ID
---------- ----------
574 574
576 576
578 578
580 580
582 582
584 584
586 586
588 588
590 590
592 592
594 594
ID ID
---------- ----------
596 596
598 598
600 600
602 602
604 604
606 606
608 608
610 610
612 612
614 614
616 616
ID ID
---------- ----------
618 618
620 620
622 622
624 624
626 626
628 628
630 630
632 632
634 634
636 636
638 638
ID ID
---------- ----------
640 640
642 642
644 644
646 646
648 648
650 650
652 652
654 654
656 656
658 658
660 660
ID ID
---------- ----------
662 662
664 664
666 666
668 668
670 670
672 672
674 674
676 676
678 678
680 680
682 682
ID ID
---------- ----------
684 684
686 686
688 688
690 690
692 692
694 694
696 696
698 698
700 700
702 702
704 704
ID ID
---------- ----------
706 706
708 708
710 710
712 712
714 714
716 716
718 718
720 720
722 722
724 724
726 726
ID ID
---------- ----------
728 728
730 730
732 732
734 734
736 736
738 738
740 740
742 742
744 744
746 746
748 748
ID ID
---------- ----------
750 750
752 752
754 754
756 756
758 758
760 760
762 762
764 764
766 766
768 768
770 770
ID ID
---------- ----------
772 772
774 774
776 776
778 778
780 780
782 782
784 784
786 786
788 788
790 790
792 792
ID ID
---------- ----------
794 794
796 796
798 798
800 800
802 802
804 804
806 806
808 808
810 810
812 812
814 814
ID ID
---------- ----------
816 816
818 818
820 820
822 822
824 824
826 826
828 828
830 830
832 832
834 834
836 836
ID ID
---------- ----------
838 838
840 840
842 842
844 844
846 846
848 848
850 850
852 852
854 854
856 856
858 858
ID ID
---------- ----------
860 860
862 862
864 864
866 866
868 868
870 870
872 872
874 874
876 876
878 878
880 880
ID ID
---------- ----------
882 882
884 884
886 886
888 888
890 890
892 892
894 894
896 896
898 898
900 900
902 902
ID ID
---------- ----------
904 904
906 906
908 908
910 910
912 912
914 914
916 916
918 918
920 920
922 922
924 924
ID ID
---------- ----------
926 926
928 928
930 930
932 932
934 934
936 936
938 938
940 940
942 942
944 944
946 946
ID ID
---------- ----------
948 948
950 950
952 952
954 954
956 956
958 958
960 960
962 962
964 964
966 966
968 968
ID ID
---------- ----------
970 970
972 972
974 974
976 976
978 978
980 980
982 982
984 984
986 986
988 988
990 990
ID ID
---------- ----------
992 992
994 994
996 996
998 998
1000 1000
693
597
957
185
727
101
ID ID
---------- ----------
867
567
603
885
715
729
461
627
975
99
805
ID ID
---------- ----------
403
263
379
545
339
329
13
377
933
127
913
ID ID
---------- ----------
39
915
459
953
909
335
297
213
951
207
229
ID ID
---------- ----------
281
249
363
41
591
931
433
469
839
679
109
ID ID
---------- ----------
113
721
617
381
809
937
509
143
51
527
151
ID ID
---------- ----------
137
733
587
849
639
455
791
169
749
253
973
ID ID
---------- ----------
985
847
511
783
495
245
211
803
557
197
79
ID ID
---------- ----------
575
769
49
725
645
413
147
779
201
175
405
ID ID
---------- ----------
647
615
373
65
661
133
427
935
999
921
963
ID ID
---------- ----------
747
123
471
483
649
279
503
351
625
631
11
ID ID
---------- ----------
33
225
359
91
881
893
15
793
135
869
37
ID ID
---------- ----------
829
707
997
237
589
939
943
435
565
853
571
ID ID
---------- ----------
843
689
629
477
431
719
361
665
289
561
5
ID ID
---------- ----------
255
929
437
529
475
691
81
467
861
987
341
ID ID
---------- ----------
507
139
559
947
695
601
621
191
321
7
161
ID ID
---------- ----------
419
239
271
465
285
319
55
827
63
723
877
ID ID
---------- ----------
17
357
543
563
945
883
307
293
521
155
703
ID ID
---------- ----------
533
605
187
93
735
423
609
531
583
371
421
ID ID
---------- ----------
365
777
537
203
857
737
145
231
813
901
43
ID ID
---------- ----------
811
607
781
657
261
971
311
59
355
163
773
ID ID
---------- ----------
675
757
183
83
697
391
241
717
473
807
1
ID ID
---------- ----------
497
541
223
275
895
651
535
349
837
845
447
ID ID
---------- ----------
709
539
35
117
959
387
305
283
635
751
57
ID ID
---------- ----------
759
25
167
815
593
917
891
97
995
499
753
ID ID
---------- ----------
445
965
9
745
859
443
45
801
825
205
75
ID ID
---------- ----------
741
331
873
103
613
251
667
29
227
189
855
ID ID
---------- ----------
173
287
755
771
87
407
21
863
979
789
787
ID ID
---------- ----------
23
927
165
637
677
181
823
301
785
85
487
ID ID
---------- ----------
449
129
215
983
941
711
547
119
149
89
401
ID ID
---------- ----------
517
77
549
765
385
199
383
159
681
493
115
ID ID
---------- ----------
569
291
327
177
153
831
337
27
835
451
833
ID ID
---------- ----------
47
333
955
767
993
367
221
897
309
555
415
ID ID
---------- ----------
393
595
705
233
303
265
259
257
795
299
209
ID ID
---------- ----------
343
875
903
643
71
345
95
907
659
315
369
ID ID
---------- ----------
219
871
525
655
157
553
247
441
389
923
961
ID ID
---------- ----------
31
73
323
551
641
633
699
701
817
743
713
ID ID
---------- ----------
479
581
375
347
967
417
295
887
763
481
125
ID ID
---------- ----------
313
193
67
585
267
879
653
797
599
991
911
ID ID
---------- ----------
269
683
579
669
731
685
505
399
411
491
457
ID ID
---------- ----------
217
573
687
501
179
397
623
61
673
277
739
ID ID
---------- ----------
819
409
513
611
969
107
69
131
273
671
799
ID ID
---------- ----------
865
243
489
841
981
949
141
195
453
235
317
ID ID
---------- ----------
429
121
171
851
3
19
899
775
919
515
519
ID ID
---------- ----------
905
821
439
463
105
761
577
111
485
663
425
ID ID
---------- ----------
619
325
925
353
989
889
977
523
53
395
1000 rows selected.
The second query returns the following results:
SQL> SELECT t.id
2 FROM t
3 WHERE
4 EXISTS (
5 SELECT 0
6 FROM link_table lt
7 WHERE lt.id(+) = t.id
8 );
ID
----------
652
654
656
658
660
662
664
666
668
670
672
ID
----------
674
676
678
680
682
684
686
688
690
692
694
ID
----------
696
698
700
702
704
706
708
710
712
714
716
ID
----------
718
720
722
724
726
728
730
732
734
736
738
ID
----------
740
742
744
746
748
750
752
754
756
758
760
ID
----------
762
764
766
768
770
772
774
776
778
780
782
ID
----------
784
786
788
790
792
794
796
798
800
802
804
ID
----------
806
808
810
812
814
816
818
820
822
824
826
ID
----------
828
830
832
834
836
838
840
842
844
846
848
ID
----------
850
852
854
856
858
860
862
864
866
868
870
ID
----------
872
874
876
878
880
882
884
886
888
890
892
ID
----------
894
896
898
900
902
904
906
908
910
912
914
ID
----------
916
918
920
922
924
926
928
930
932
934
936
ID
----------
938
940
942
944
946
948
950
952
954
956
958
ID
----------
960
962
964
966
968
970
972
974
976
978
980
ID
----------
982
984
986
988
990
992
994
996
998
1000
2
ID
----------
4
6
8
10
12
14
16
18
20
22
24
ID
----------
26
28
30
32
34
36
38
40
42
44
46
ID
----------
48
50
52
54
56
58
60
62
64
66
68
ID
----------
70
72
74
76
78
80
82
84
86
88
90
ID
----------
92
94
96
98
100
102
104
106
108
110
112
ID
----------
114
116
118
120
122
124
126
128
130
132
134
ID
----------
136
138
140
142
144
146
148
150
152
154
156
ID
----------
158
160
162
164
166
168
170
172
174
176
178
ID
----------
180
182
184
186
188
190
192
194
196
198
200
ID
----------
202
204
206
208
210
212
214
216
218
220
222
ID
----------
224
226
228
230
232
234
236
238
240
242
244
ID
----------
246
248
250
252
254
256
258
260
262
264
266
ID
----------
268
270
272
274
276
278
280
282
284
286
288
ID
----------
290
292
294
296
298
300
302
304
306
308
310
ID
----------
312
314
316
318
320
322
324
326
328
330
332
ID
----------
334
336
338
340
342
344
346
348
350
352
354
ID
----------
356
358
360
362
364
366
368
370
372
374
376
ID
----------
378
380
382
384
386
388
390
392
394
396
398
ID
----------
400
402
404
406
408
410
412
414
416
418
420
ID
----------
422
424
426
428
430
432
434
436
438
440
442
ID
----------
444
446
448
450
452
454
456
458
460
462
464
ID
----------
466
468
470
472
474
476
478
480
482
484
486
ID
----------
488
490
492
494
496
498
500
502
504
506
508
ID
----------
510
512
514
516
518
520
522
524
526
528
530
ID
----------
532
534
536
538
540
542
544
546
548
550
552
ID
----------
554
556
558
560
562
564
566
568
570
572
574
ID
----------
576
578
580
582
584
586
588
590
592
594
596
ID
----------
598
600
602
604
606
608
610
612
614
616
618
ID
----------
620
622
624
626
628
630
632
634
636
638
640
ID
----------
642
644
646
648
650
500 rows selected.
The same result set is returned using an IN condition:
SQL> SELECT t.id
2 FROM t
3 WHERE
4 t.id in (
5 SELECT lt.id
6 FROM link_table lt
7 WHERE lt.id(+) = t.id
8 );
ID
----------
652
654
656
658
660
662
664
666
668
670
672
ID
----------
674
676
678
680
682
684
686
688
690
692
694
ID
----------
696
698
700
702
704
706
708
710
712
714
716
ID
----------
718
720
722
724
726
728
730
732
734
736
738
ID
----------
740
742
744
746
748
750
752
754
756
758
760
ID
----------
762
764
766
768
770
772
774
776
778
780
782
ID
----------
784
786
788
790
792
794
796
798
800
802
804
ID
----------
806
808
810
812
814
816
818
820
822
824
826
ID
----------
828
830
832
834
836
838
840
842
844
846
848
ID
----------
850
852
854
856
858
860
862
864
866
868
870
ID
----------
872
874
876
878
880
882
884
886
888
890
892
ID
----------
894
896
898
900
902
904
906
908
910
912
914
ID
----------
916
918
920
922
924
926
928
930
932
934
936
ID
----------
938
940
942
944
946
948
950
952
954
956
958
ID
----------
960
962
964
966
968
970
972
974
976
978
980
ID
----------
982
984
986
988
990
992
994
996
998
1000
2
ID
----------
4
6
8
10
12
14
16
18
20
22
24
ID
----------
26
28
30
32
34
36
38
40
42
44
46
ID
----------
48
50
52
54
56
58
60
62
64
66
68
ID
----------
70
72
74
76
78
80
82
84
86
88
90
ID
----------
92
94
96
98
100
102
104
106
108
110
112
ID
----------
114
116
118
120
122
124
126
128
130
132
134
ID
----------
136
138
140
142
144
146
148
150
152
154
156
ID
----------
158
160
162
164
166
168
170
172
174
176
178
ID
----------
180
182
184
186
188
190
192
194
196
198
200
ID
----------
202
204
206
208
210
212
214
216
218
220
222
ID
----------
224
226
228
230
232
234
236
238
240
242
244
ID
----------
246
248
250
252
254
256
258
260
262
264
266
ID
----------
268
270
272
274
276
278
280
282
284
286
288
ID
----------
290
292
294
296
298
300
302
304
306
308
310
ID
----------
312
314
316
318
320
322
324
326
328
330
332
ID
----------
334
336
338
340
342
344
346
348
350
352
354
ID
----------
356
358
360
362
364
366
368
370
372
374
376
ID
----------
378
380
382
384
386
388
390
392
394
396
398
ID
----------
400
402
404
406
408
410
412
414
416
418
420
ID
----------
422
424
426
428
430
432
434
436
438
440
442
ID
----------
444
446
448
450
452
454
456
458
460
462
464
ID
----------
466
468
470
472
474
476
478
480
482
484
486
ID
----------
488
490
492
494
496
498
500
502
504
506
508
ID
----------
510
512
514
516
518
520
522
524
526
528
530
ID
----------
532
534
536
538
540
542
544
546
548
550
552
ID
----------
554
556
558
560
562
564
566
568
570
572
574
ID
----------
576
578
580
582
584
586
588
590
592
594
596
ID
----------
598
600
602
604
606
608
610
612
614
616
618
ID
----------
620
622
624
626
628
630
632
634
636
638
640
ID
----------
642
644
646
648
650
500 rows selected.
SQL> spool off
This indicates, to me, that the EXISTS query cannot return values from one table where NULLs exist in the subquery. Notice all of the odd valued results are missing, the results which return NULL from link_table with an outer join. And this is where your confusion lies, I believe.
Given the queries you've supplied you'll never get the first result set (that from the straight outer join) from an EXISTS query, even with the outer join in the subquery.
David Fitzjarrell Received on Mon Jun 06 2005 - 10:27:33 CDT
 |
 |