1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Find missing records.. minus giving unexpected results.

Discussion in 'SQL PL/SQL' started by Sparamanga, Oct 2, 2016.

  1. Sparamanga

    Sparamanga Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    UK
    Hi,

    I have a very large query that extracts data from multiple databases and provides a data set of assets.

    I need to check these assets against another table to see if they are present there.

    Unfortunately each row has no unique identifier in the query. The table I'm checking against also has no unique identifier.

    I have tried to use the set operator MINUS..

    Select 6 columns from the first query
    Minus
    The same 6 columns from the checking table.

    This does provide a result set, but to check if it was correct I limited the first query and the checking table via 'WHERE' criteria to display the same two rows and then used MINUS but it did not leave me with 0 rows as expected but two rows.

    I wondered why this could be?

    Also is MINUS the best way to do this.. I've been told to perform a left outer join on multiple columns between the two tables??

    Thanks,

    R.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    If you have no common unique identifier between the tables then differences in the actual data columns will produce the results you are seeing. Look at the following example:

    Code (SQL):
    SQL> @minus_ex
    Connected.
    SQL>
    SQL> CREATE TABLE x(
      2  a         NUMBER,
      3  b         NUMBER,
      4  c         varchar2(20));

    TABLE created.

    SQL>
    SQL> CREATE TABLE y(
      2  d         NUMBER,
      3  e         NUMBER,
      4  f         varchar2(20));

    TABLE created.

    SQL>
    SQL> BEGIN
      2           FOR i IN 1..200 loop
      3               INSERT INTO x
      4               VALUES(i,i,'Record '||i);
      5               INSERT INTO y
      6               VALUES(i,i,'Records '||i);
      7           END loop;
      8
      9           commit;
    10  END;
    11  /

    PL/SQL PROCEDURE successfully completed.

    SQL>
    SQL> DELETE FROM x WHERE MOD(a,2) = 0;

    100 ROWS deleted.

    SQL>
    SQL> commit;

    Commit complete.

    SQL>
    SQL> SELECT d, e, f
      2  FROM y
      3  minus
      4  SELECT a, b, c
      5  FROM x;

             D          E F                                                        
    ---------- ---------- --------------------                                    
             1          1 Records 1                                                
             2          2 Records 2                                                
             3          3 Records 3                                                
             4          4 Records 4                                                
             5          5 Records 5                                                
             6          6 Records 6                                                
             7          7 Records 7                                                
             8          8 Records 8                                                
             9          9 Records 9                                                
            10         10 Records 10                                              
            11         11 Records 11                                              

             D          E F                                                        
    ---------- ---------- --------------------                                    
            12         12 Records 12                                              
            13         13 Records 13                                              
            14         14 Records 14                                              
            15         15 Records 15                                              
            16         16 Records 16                                              
            17         17 Records 17                                              
            18         18 Records 18                                              
            19         19 Records 19                                              
            20         20 Records 20                                              
            21         21 Records 21                                              
            22         22 Records 22                                              

             D          E F                                                        
    ---------- ---------- --------------------                                    
            23         23 Records 23                                              
            24         24 Records 24                                              
            25         25 Records 25                                              
            26         26 Records 26                                              
            27         27 Records 27                                              
            28         28 Records 28                                              
            29         29 Records 29                                              
            30         30 Records 30                                              
            31         31 Records 31                                              
            32         32 Records 32                                              
            33         33 Records 33                                              

             D          E F                                                        
    ---------- ---------- --------------------                                    
            34         34 Records 34                                              
            35         35 Records 35                                              
            36         36 Records 36                                              
            37         37 Records 37                                              
            38         38 Records 38                                              
            39         39 Records 39                                              
            40         40 Records 40                                              
            41         41 Records 41                                              
            42         42 Records 42                                              
            43         43 Records 43                                              
            44         44 Records 44                                              

             D          E F                                                        
    ---------- ---------- --------------------                                    
            45         45 Records 45                                              
            46         46 Records 46                                              
            47         47 Records 47                                              
            48         48 Records 48                                              
            49         49 Records 49                                              
            50         50 Records 50                                              
            51         51 Records 51                                              
            52         52 Records 52                                              
            53         53 Records 53                                              
            54         54 Records 54                                              
            55         55 Records 55                                              

             D          E F                                                        
    ---------- ---------- --------------------                                    
            56         56 Records 56                                              
            57         57 Records 57                                              
            58         58 Records 58                                              
            59         59 Records 59                                              
            60         60 Records 60                                              
            61         61 Records 61                                              
            62         62 Records 62                                              
            63         63 Records 63                                              
            64         64 Records 64                                              
            65         65 Records 65                                              
            66         66 Records 66                                              

             D          E F                                                        
    ---------- ---------- --------------------                                    
            67         67 Records 67                                              
            68         68 Records 68                                              
            69         69 Records 69                                              
            70         70 Records 70                                              
            71         71 Records 71                                              
            72         72 Records 72                                              
            73         73 Records 73                                              
            74         74 Records 74                                              
            75         75 Records 75                                              
            76         76 Records 76                                              
            77         77 Records 77                                              

             D          E F                                                        
    ---------- ---------- --------------------                                    
            78         78 Records 78                                              
            79         79 Records 79                                              
            80         80 Records 80                                              
            81         81 Records 81                                              
            82         82 Records 82                                              
            83         83 Records 83                                              
            84         84 Records 84                                              
            85         85 Records 85                                              
            86         86 Records 86                                              
            87         87 Records 87                                              
            88         88 Records 88                                              

             D          E F                                                        
    ---------- ---------- --------------------                                    
            89         89 Records 89                                              
            90         90 Records 90                                              
            91         91 Records 91                                              
            92         92 Records 92                                              
            93         93 Records 93                                              
            94         94 Records 94                                              
            95         95 Records 95                                              
            96         96 Records 96                                              
            97         97 Records 97                                              
            98         98 Records 98                                              
            99         99 Records 99                                              

             D          E F                                                        
    ---------- ---------- --------------------                                    
           100        100 Records 100                                              
           101        101 Records 101                                              
           102        102 Records 102                                              
           103        103 Records 103                                              
           104        104 Records 104                                              
           105        105 Records 105                                              
           106        106 Records 106                                              
           107        107 Records 107                                              
           108        108 Records 108                                              
           109        109 Records 109                                              
           110        110 Records 110                                              

             D          E F                                                        
    ---------- ---------- --------------------                                    
           111        111 Records 111                                              
           112        112 Records 112                                              
           113        113 Records 113                                              
           114        114 Records 114                                              
           115        115 Records 115                                              
           116        116 Records 116                                              
           117        117 Records 117                                              
           118        118 Records 118                                              
           119        119 Records 119                                              
           120        120 Records 120                                              
           121        121 Records 121                                              

             D          E F                                                        
    ---------- ---------- --------------------                                    
           122        122 Records 122                                              
           123        123 Records 123                                              
           124        124 Records 124                                              
           125        125 Records 125                                              
           126        126 Records 126                                              
           127        127 Records 127                                              
           128        128 Records 128                                              
           129        129 Records 129                                              
           130        130 Records 130                                              
           131        131 Records 131                                              
           132        132 Records 132                                              

             D          E F                                                        
    ---------- ---------- --------------------                                    
           133        133 Records 133                                              
           134        134 Records 134                                              
           135        135 Records 135                                              
           136        136 Records 136                                              
           137        137 Records 137                                              
           138        138 Records 138                                              
           139        139 Records 139                                              
           140        140 Records 140                                              
           141        141 Records 141                                              
           142        142 Records 142                                              
           143        143 Records 143                                              

             D          E F                                                        
    ---------- ---------- --------------------                                    
           144        144 Records 144                                              
           145        145 Records 145                                              
           146        146 Records 146                                              
           147        147 Records 147                                              
           148        148 Records 148                                              
           149        149 Records 149                                              
           150        150 Records 150                                              
           151        151 Records 151                                              
           152        152 Records 152                                              
           153        153 Records 153                                              
           154        154 Records 154                                              

             D          E F                                                        
    ---------- ---------- --------------------                                    
           155        155 Records 155                                              
           156        156 Records 156                                              
           157        157 Records 157                                              
           158        158 Records 158                                              
           159        159 Records 159                                              
           160        160 Records 160                                              
           161        161 Records 161                                              
           162        162 Records 162                                              
           163        163 Records 163                                              
           164        164 Records 164                                              
           165        165 Records 165                                              

             D          E F                                                        
    ---------- ---------- --------------------                                    
           166        166 Records 166                                              
           167        167 Records 167                                              
           168        168 Records 168                                              
           169        169 Records 169                                              
           170        170 Records 170                                              
           171        171 Records 171                                              
           172        172 Records 172                                              
           173        173 Records 173                                              
           174        174 Records 174                                              
           175        175 Records 175                                              
           176        176 Records 176                                              

             D          E F                                                        
    ---------- ---------- --------------------                                    
           177        177 Records 177                                              
           178        178 Records 178                                              
           179        179 Records 179                                              
           180        180 Records 180                                              
           181        181 Records 181                                              
           182        182 Records 182                                              
           183        183 Records 183                                              
           184        184 Records 184                                              
           185        185 Records 185                                              
           186        186 Records 186                                              
           187        187 Records 187                                              

             D          E F                                                        
    ---------- ---------- --------------------                                    
           188        188 Records 188                                              
           189        189 Records 189                                              
           190        190 Records 190                                              
           191        191 Records 191                                              
           192        192 Records 192                                              
           193        193 Records 193                                              
           194        194 Records 194                                              
           195        195 Records 195                                              
           196        196 Records 196                                              
           197        197 Records 197                                              
           198        198 Records 198                                              

             D          E F                                                        
    ---------- ---------- --------------------                                    
           199        199 Records 199                                              
           200        200 Records 200                                              

    200 ROWS selected.

    SQL>
     
    The data is different between column c and f , so all records are returned. Changing that changes the output:

    Code (SQL):
    SQL>
    SQL> TRUNCATE TABLE x;

    TABLE truncated.

    SQL> TRUNCATE TABLE y;

    TABLE truncated.

    SQL>
    SQL> BEGIN
      2           FOR i IN 1..200 loop
      3               INSERT INTO x
      4               VALUES(i,i,'Record '||i);
      5               INSERT INTO y
      6               VALUES(i,i,'Record '||i);
      7           END loop;
      8
      9           commit;
    10  END;
    11  /

    PL/SQL PROCEDURE successfully completed.

    SQL>
    SQL> DELETE FROM x WHERE MOD(a,2) = 0;

    100 ROWS deleted.

    SQL>
    SQL> commit;

    Commit complete.

    SQL>
    SQL> SELECT d, e, f
      2  FROM y
      3  minus
      4  SELECT a, b, c
      5  FROM x;

             D          E F                                                        
    ---------- ---------- --------------------                                    
             2          2 Record 2                                                
             4          4 Record 4                                                
             6          6 Record 6                                                
             8          8 Record 8                                                
            10         10 Record 10                                                
            12         12 Record 12                                                
            14         14 Record 14                                                
            16         16 Record 16                                                
            18         18 Record 18                                                
            20         20 Record 20                                                
            22         22 Record 22                                                

             D          E F                                                        
    ---------- ---------- --------------------                                    
            24         24 Record 24                                                
            26         26 Record 26                                                
            28         28 Record 28                                                
            30         30 Record 30                                                
            32         32 Record 32                                                
            34         34 Record 34                                                
            36         36 Record 36                                                
            38         38 Record 38                                                
            40         40 Record 40                                                
            42         42 Record 42                                                
            44         44 Record 44                                                

             D          E F                                                        
    ---------- ---------- --------------------                                    
            46         46 Record 46                                                
            48         48 Record 48                                                
            50         50 Record 50                                                
            52         52 Record 52                                                
            54         54 Record 54                                                
            56         56 Record 56                                                
            58         58 Record 58                                                
            60         60 Record 60                                                
            62         62 Record 62                                                
            64         64 Record 64                                                
            66         66 Record 66                                                

             D          E F                                                        
    ---------- ---------- --------------------                                    
            68         68 Record 68                                                
            70         70 Record 70                                                
            72         72 Record 72                                                
            74         74 Record 74                                                
            76         76 Record 76                                                
            78         78 Record 78                                                
            80         80 Record 80                                                
            82         82 Record 82                                                
            84         84 Record 84                                                
            86         86 Record 86                                                
            88         88 Record 88                                                

             D          E F                                                        
    ---------- ---------- --------------------                                    
            90         90 Record 90                                                
            92         92 Record 92                                                
            94         94 Record 94                                                
            96         96 Record 96                                                
            98         98 Record 98                                                
           100        100 Record 100                                              
           102        102 Record 102                                              
           104        104 Record 104                                              
           106        106 Record 106                                              
           108        108 Record 108                                              
           110        110 Record 110                                              

             D          E F                                                        
    ---------- ---------- --------------------                                    
           112        112 Record 112                                              
           114        114 Record 114                                              
           116        116 Record 116                                              
           118        118 Record 118                                              
           120        120 Record 120                                              
           122        122 Record 122                                              
           124        124 Record 124                                              
           126        126 Record 126                                              
           128        128 Record 128                                              
           130        130 Record 130                                              
           132        132 Record 132                                              

             D          E F                                                        
    ---------- ---------- --------------------                                    
           134        134 Record 134                                              
           136        136 Record 136                                              
           138        138 Record 138                                              
           140        140 Record 140                                              
           142        142 Record 142                                              
           144        144 Record 144                                              
           146        146 Record 146                                              
           148        148 Record 148                                              
           150        150 Record 150                                              
           152        152 Record 152                                              
           154        154 Record 154                                              

             D          E F                                                        
    ---------- ---------- --------------------                                    
           156        156 Record 156                                              
           158        158 Record 158                                              
           160        160 Record 160                                              
           162        162 Record 162                                              
           164        164 Record 164                                              
           166        166 Record 166                                              
           168        168 Record 168                                              
           170        170 Record 170                                              
           172        172 Record 172                                              
           174        174 Record 174                                              
           176        176 Record 176                                              

             D          E F                                                        
    ---------- ---------- --------------------                                    
           178        178 Record 178                                              
           180        180 Record 180                                              
           182        182 Record 182                                              
           184        184 Record 184                                              
           186        186 Record 186                                              
           188        188 Record 188                                              
           190        190 Record 190                                              
           192        192 Record 192                                              
           194        194 Record 194                                              
           196        196 Record 196                                              
           198        198 Record 198                                              

             D          E F                                                        
    ---------- ---------- --------------------                                    
           200        200 Record 200                                              

    100 ROWS selected.

    SQL>
     
    Notice the 'correct' rows are now returned. You really need a common unique identifier between the two tables to ensure you find the actual 'missing' records.
     
  3. Sparamanga

    Sparamanga Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    UK
    Hi thanks for the reply.. but I got a little lost with code you were using. I'm new to SQL.

    I've been told to left join between the two tables on multiple columns that are the same between the two tables, then WHERE an additional column from the right table is null to get the records not in the right table.

    The issue is some of the columns I'm joining on have null values so this is skewing the results I think.

    How would you deal with this aside from omitting bulls from the join. I need to keep them as the table I'm checking against has nulls in certain columns too.

    Thanks

    Rob.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    MINUS will process things just fine even with NULL values in one of the columns:


    Code (SQL):
    SQL> UPDATE x SET c = NULL
      2  WHERE MOD(a,2) = 0;

    100 ROWS updated.

    SQL>
    SQL> commit;

    Commit complete.

    SQL>
    SQL> SELECT d, e, f
      2  FROM y
      3  minus
      4  SELECT a, b, c
      5  FROM x;

             D          E F
    ---------- ---------- --------------------
             2          2 Record 2
             4          4 Record 4
             6          6 Record 6
             8          8 Record 8
            10         10 Record 10
            12         12 Record 12
            14         14 Record 14
            16         16 Record 16
            18         18 Record 18
            20         20 Record 20
            22         22 Record 22

             D          E F
    ---------- ---------- --------------------
            24         24 Record 24
            26         26 Record 26
            28         28 Record 28
            30         30 Record 30
            32         32 Record 32
            34         34 Record 34
            36         36 Record 36
            38         38 Record 38
            40         40 Record 40
            42         42 Record 42
            44         44 Record 44

             D          E F
    ---------- ---------- --------------------
            46         46 Record 46
            48         48 Record 48
            50         50 Record 50
            52         52 Record 52
            54         54 Record 54
            56         56 Record 56
            58         58 Record 58
            60         60 Record 60
            62         62 Record 62
            64         64 Record 64
            66         66 Record 66

             D          E F
    ---------- ---------- --------------------
            68         68 Record 68
            70         70 Record 70
            72         72 Record 72
            74         74 Record 74
            76         76 Record 76
            78         78 Record 78
            80         80 Record 80
            82         82 Record 82
            84         84 Record 84
            86         86 Record 86
            88         88 Record 88

             D          E F
    ---------- ---------- --------------------
            90         90 Record 90
            92         92 Record 92
            94         94 Record 94
            96         96 Record 96
            98         98 Record 98
           100        100 Record 100
           102        102 Record 102
           104        104 Record 104
           106        106 Record 106
           108        108 Record 108
           110        110 Record 110

             D          E F
    ---------- ---------- --------------------
           112        112 Record 112
           114        114 Record 114
           116        116 Record 116
           118        118 Record 118
           120        120 Record 120
           122        122 Record 122
           124        124 Record 124
           126        126 Record 126
           128        128 Record 128
           130        130 Record 130
           132        132 Record 132

             D          E F
    ---------- ---------- --------------------
           134        134 Record 134
           136        136 Record 136
           138        138 Record 138
           140        140 Record 140
           142        142 Record 142
           144        144 Record 144
           146        146 Record 146
           148        148 Record 148
           150        150 Record 150
           152        152 Record 152
           154        154 Record 154

             D          E F
    ---------- ---------- --------------------
           156        156 Record 156
           158        158 Record 158
           160        160 Record 160
           162        162 Record 162
           164        164 Record 164
           166        166 Record 166
           168        168 Record 168
           170        170 Record 170
           172        172 Record 172
           174        174 Record 174
           176        176 Record 176

             D          E F
    ---------- ---------- --------------------
           178        178 Record 178
           180        180 Record 180
           182        182 Record 182
           184        184 Record 184
           186        186 Record 186
           188        188 Record 188
           190        190 Record 190
           192        192 Record 192
           194        194 Record 194
           196        196 Record 196
           198        198 Record 198

             D          E F
    ---------- ---------- --------------------
           200        200 Record 200

    100 ROWS selected.

    SQL>
    You need to supply table create statements and sample data so we can replicate what you are seeing. With what little you have provided I can't reproduce what you report.