Oracle Tips and Tricks — David Fitzjarrell

March 31, 2017

You’re A Natural

Filed under: General — dfitzjarrell @ 08:44

"'Why is it,' he said quietly, 'that quite often even the things which are correct just don't seem to be right?'"
-- Norton Juster, The Phantom Tollbooth

A YouTube video is currently being promoted in the database community regarding joins, including the variety of joins available in most database engines (the engine referred to in the video is Oracle). A very good discussion ensues covering inner, left outer, right outer, full outer and cross joins. Notably absent (and, conceivably, of limited use) is the natural join, used when the join columns have the same name (and, hopefully, the same definition). Let’s look at the natural join and what it can, and cannot, do.

The following example sets up the conditions for a successful natural join: two tables with a common column that will facilitate the use of the natural join. Notice that in a natural join columns cannot have a prefix; the natural join returns the selected columns from all tables in the join which can make things a bit confusing when writing such a select list. We begin with a simple ‘select *’ query:


SQL> create table yooper(
  2  snorm   number,
  3  wabba   varchar2(20),
  4  oplunt  date);

Table created.

SQL> 
SQL> create table amplo(
  2  snorm   number,
  3  fleezor date,
  4  smang   varchar2(20),
  5  imjyt   varchar2(17));

Table created.

SQL> 
SQL> begin
  2  	     for i in 1..10 loop
  3  		     insert into yooper
  4  		     values(i, 'Quarzenfleep '||i, sysdate +i);
  5  		     insert into amplo
  6  		     values(i, sysdate -i, 'Erblo'||i, 'Zaxegoomp'||i);
  7  	     end loop;
  8  
  9  	     commit;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select *
  2  from yooper natural join amplo;

     SNORM WABBA                OPLUNT    FLEEZOR   SMANG                IMJYT
---------- -------------------- --------- --------- -------------------- -----------------
         1 Quarzenfleep 1       31-MAR-17 29-MAR-17 Erblo1               Zaxegoomp1
         2 Quarzenfleep 2       01-APR-17 28-MAR-17 Erblo2               Zaxegoomp2
         3 Quarzenfleep 3       02-APR-17 27-MAR-17 Erblo3               Zaxegoomp3
         4 Quarzenfleep 4       03-APR-17 26-MAR-17 Erblo4               Zaxegoomp4
         5 Quarzenfleep 5       04-APR-17 25-MAR-17 Erblo5               Zaxegoomp5
         6 Quarzenfleep 6       05-APR-17 24-MAR-17 Erblo6               Zaxegoomp6
         7 Quarzenfleep 7       06-APR-17 23-MAR-17 Erblo7               Zaxegoomp7
         8 Quarzenfleep 8       07-APR-17 22-MAR-17 Erblo8               Zaxegoomp8
         9 Quarzenfleep 9       08-APR-17 21-MAR-17 Erblo9               Zaxegoomp9
        10 Quarzenfleep 10      09-APR-17 20-MAR-17 Erblo10              Zaxegoomp10

10 rows selected.

SQL> 
>

In this case the common column not only has the same name but also the same data type. When processing such queries Oracle returns the join column data from the first table listed in the join. Since the column has the same name and definition in this example it really doesn’t matter which table the join column data comes from, but in the next example there will be a difference. Let’s drop the existing tables and recreate them, this time with one table having a number data type and the other having a VARCHAR2 data type. The data in both tables will be the same (although the numbers will be stored as characters in the VARCHAR2 column). Since there are no alpha characters in the VARCHAR2 column the implicit TO_NUMBER() conversion succeeds; the difference in the output is the SNORM column (the commonly-named join column) printed is the character data, not the numeric, but, as mentioned previously, that is dependent on the table order in the join:


SQL> drop table yooper purge;

Table dropped.

SQL> drop table amplo purge;

Table dropped.

SQL> 
SQL> create table yooper(
  2  snorm   number,
  3  wabba   varchar2(20),
  4  oplunt  date);

Table created.

SQL> 
SQL> create table amplo(
  2  snorm   varchar2(10),
  3  fleezor date,
  4  smang   varchar2(20),
  5  imjyt   varchar2(17));

Table created.

SQL> 
SQL> begin
  2  	     for i in 1..10 loop
  3  		     insert into yooper
  4  		     values(i, 'Quarzenfleep '||i, sysdate +i);
  5  		     insert into amplo
  6  		     values(i, sysdate -i, 'Erblo'||i, 'Zaxegoomp'||i);
  7  	     end loop;
  8  
  9  	     commit;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select *
  2  from yooper natural join amplo;

SNORM      WABBA                OPLUNT    FLEEZOR   SMANG                IMJYT
---------- -------------------- --------- --------- -------------------- -----------------
1          Quarzenfleep 1       31-MAR-17 29-MAR-17 Erblo1               Zaxegoomp1
2          Quarzenfleep 2       01-APR-17 28-MAR-17 Erblo2               Zaxegoomp2
3          Quarzenfleep 3       02-APR-17 27-MAR-17 Erblo3               Zaxegoomp3
4          Quarzenfleep 4       03-APR-17 26-MAR-17 Erblo4               Zaxegoomp4
5          Quarzenfleep 5       04-APR-17 25-MAR-17 Erblo5               Zaxegoomp5
6          Quarzenfleep 6       05-APR-17 24-MAR-17 Erblo6               Zaxegoomp6
7          Quarzenfleep 7       06-APR-17 23-MAR-17 Erblo7               Zaxegoomp7
8          Quarzenfleep 8       07-APR-17 22-MAR-17 Erblo8               Zaxegoomp8
9          Quarzenfleep 9       08-APR-17 21-MAR-17 Erblo9               Zaxegoomp9
10         Quarzenfleep 10      09-APR-17 20-MAR-17 Erblo10              Zaxegoomp10

10 rows selected.

SQL> 

Notice when the tables are reversed the SNORM data is again numeric:


SQL> select *
  2  from amplo natural join yooper;

     SNORM FLEEZOR   SMANG                IMJYT             WABBA                OPLUNT
---------- --------- -------------------- ----------------- -------------------- ---------
         1 29-MAR-17 Erblo1               Zaxegoomp1        Quarzenfleep 1       31-MAR-17
         2 28-MAR-17 Erblo2               Zaxegoomp2        Quarzenfleep 2       01-APR-17
         3 27-MAR-17 Erblo3               Zaxegoomp3        Quarzenfleep 3       02-APR-17
         4 26-MAR-17 Erblo4               Zaxegoomp4        Quarzenfleep 4       03-APR-17
         5 25-MAR-17 Erblo5               Zaxegoomp5        Quarzenfleep 5       04-APR-17
         6 24-MAR-17 Erblo6               Zaxegoomp6        Quarzenfleep 6       05-APR-17
         7 23-MAR-17 Erblo7               Zaxegoomp7        Quarzenfleep 7       06-APR-17
         8 22-MAR-17 Erblo8               Zaxegoomp8        Quarzenfleep 8       07-APR-17
         9 21-MAR-17 Erblo9               Zaxegoomp9        Quarzenfleep 9       08-APR-17
        10 20-MAR-17 Erblo10              Zaxegoomp10       Quarzenfleep 10      09-APR-17

10 rows selected.

SQL>

Adjusting the select list by using specific columns produces a smaller data set; notice that no table aliases or prefixes are used, which can make it difficult to keep track of what columns are coming from which table:


SQL> select smang, snorm, fleezor
  2  from yooper natural join amplo;

SMANG                SNORM      FLEEZOR
-------------------- ---------- ---------
Erblo1               1          29-MAR-17
Erblo2               2          28-MAR-17
Erblo3               3          27-MAR-17
Erblo4               4          26-MAR-17
Erblo5               5          25-MAR-17
Erblo6               6          24-MAR-17
Erblo7               7          23-MAR-17
Erblo8               8          22-MAR-17
Erblo9               9          21-MAR-17
Erblo10              10         20-MAR-17

10 rows selected.

SQL>

The natural join only requires the column names to be the same; the definitions can be different and as long as the data can be converted so a comparison can be made the query succeeds. Now let’s change the picture a bit more and store character strings in the one table while the other remains with numeric data:


SQL> drop table yooper purge;

Table dropped.

SQL> drop table amplo purge;

Table dropped.

SQL> 
SQL> create table yooper(
  2  snorm   number,
  3  wabba   varchar2(20),
  4  oplunt  date);

Table created.

SQL> 
SQL> create table amplo(
  2  snorm   varchar2(10),
  3  fleezor date,
  4  smang   varchar2(20),
  5  imjyt   varchar2(17));

Table created.

SQL> 
SQL> begin
  2  	     for i in 1..10 loop
  3  		     insert into yooper
  4  		     values(i, 'Quarzenfleep '||i, sysdate +i);
  5  		     insert into amplo
  6  		     values(i||'Bubba', sysdate -i, 'Erblo'||i, 'Zaxegoomp'||i);
  7  	     end loop;
  8  
  9  	     commit;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select *
  2  from yooper natural join amplo;
select *
*
ERROR at line 1:
ORA-01722: invalid number


SQL> 
SQL> select *
  2  from amplo natural join yooper;
select *
*
ERROR at line 1:
ORA-01722: invalid number


SQL> 
SQL> drop table yooper purge;

Table dropped.

SQL> drop table amplo purge;

Table dropped.

SQL> 

Now the natural join fails to return data since the TO_NUMBER() conversion fails; it doesn’t matter which table is listed first in the natural join from this example as the conversion will be from the character string to a number.

The natural join will use all commonly named columns in the join condition, so let’s add another matching column to this example and see what happens:


SQL> create table yooper(
  2  snorm      number,
  3  fleezor date,
  4  wabba      varchar2(20),
  5  oplunt     date);

Table created.

SQL>
SQL> create table amplo(
  2  snorm      number(10),
  3  fleezor date,
  4  smang      varchar2(20),
  5  imjyt      varchar2(17));

Table created.

SQL>
SQL> begin
  2          for i in 1..10 loop
  3                  insert into yooper
  4                  values(i, sysdate-i, 'Quarzenfleep '||i, sysdate +i);
  5                  insert into amplo
  6                  values(i, sysdate -i, 'Erblo'||i, 'Zaxegoomp'||i);
  7          end loop;
  8
  9          commit;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL>
SQL> select *
  2  from yooper natural join amplo;

     SNORM FLEEZOR   WABBA                OPLUNT    SMANG                IMJYT
---------- --------- -------------------- --------- -------------------- -----------------
         1 30-MAR-17 Quarzenfleep 1       01-APR-17 Erblo1               Zaxegoomp1
         2 29-MAR-17 Quarzenfleep 2       02-APR-17 Erblo2               Zaxegoomp2
         3 28-MAR-17 Quarzenfleep 3       03-APR-17 Erblo3               Zaxegoomp3
         4 27-MAR-17 Quarzenfleep 4       04-APR-17 Erblo4               Zaxegoomp4
         5 26-MAR-17 Quarzenfleep 5       05-APR-17 Erblo5               Zaxegoomp5
         6 25-MAR-17 Quarzenfleep 6       06-APR-17 Erblo6               Zaxegoomp6
         7 24-MAR-17 Quarzenfleep 7       07-APR-17 Erblo7               Zaxegoomp7
         8 23-MAR-17 Quarzenfleep 8       08-APR-17 Erblo8               Zaxegoomp8
         9 22-MAR-17 Quarzenfleep 9       09-APR-17 Erblo9               Zaxegoomp9
        10 21-MAR-17 Quarzenfleep 10      10-APR-17 Erblo10              Zaxegoomp10

10 rows selected.

SQL>
SQL> select *
  2  from amplo natural join yooper;

     SNORM FLEEZOR   SMANG                IMJYT             WABBA                OPLUNT
---------- --------- -------------------- ----------------- -------------------- ---------
         1 30-MAR-17 Erblo1               Zaxegoomp1        Quarzenfleep 1       01-APR-17
         2 29-MAR-17 Erblo2               Zaxegoomp2        Quarzenfleep 2       02-APR-17
         3 28-MAR-17 Erblo3               Zaxegoomp3        Quarzenfleep 3       03-APR-17
         4 27-MAR-17 Erblo4               Zaxegoomp4        Quarzenfleep 4       04-APR-17
         5 26-MAR-17 Erblo5               Zaxegoomp5        Quarzenfleep 5       05-APR-17
         6 25-MAR-17 Erblo6               Zaxegoomp6        Quarzenfleep 6       06-APR-17
         7 24-MAR-17 Erblo7               Zaxegoomp7        Quarzenfleep 7       07-APR-17
         8 23-MAR-17 Erblo8               Zaxegoomp8        Quarzenfleep 8       08-APR-17
         9 22-MAR-17 Erblo9               Zaxegoomp9        Quarzenfleep 9       09-APR-17
        10 21-MAR-17 Erblo10              Zaxegoomp10       Quarzenfleep 10      10-APR-17

10 rows selected.

SQL>

As with all inner joins only the matching data is returned so if YOOPER is reloaded with only the even-numbered records that will be seen in the output from the join:


SQL> truncate table yooper;

Table truncated.

SQL>
SQL> begin
  2          for i in 1..10 loop
  3                  if mod(i,2) = 0 then
  4                          insert into yooper
  5                          values(i, sysdate-i, 'Quarzenfleep '||i, sysdate +i);
  6                  end if;
  7          end loop;
  8
  9          commit;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL>
SQL> select *
  2  from yooper natural join amplo;

     SNORM FLEEZOR   WABBA                OPLUNT    SMANG                IMJYT
---------- --------- -------------------- --------- -------------------- -----------------
         2 29-MAR-17 Quarzenfleep 2       02-APR-17 Erblo2               Zaxegoomp2
         4 27-MAR-17 Quarzenfleep 4       04-APR-17 Erblo4               Zaxegoomp4
         6 25-MAR-17 Quarzenfleep 6       06-APR-17 Erblo6               Zaxegoomp6
         8 23-MAR-17 Quarzenfleep 8       08-APR-17 Erblo8               Zaxegoomp8
        10 21-MAR-17 Quarzenfleep 10      10-APR-17 Erblo10              Zaxegoomp10

SQL>
SQL> select *
  2  from amplo natural join yooper;

     SNORM FLEEZOR   SMANG                IMJYT             WABBA                OPLUNT
---------- --------- -------------------- ----------------- -------------------- ---------
         2 29-MAR-17 Erblo2               Zaxegoomp2        Quarzenfleep 2       02-APR-17
         4 27-MAR-17 Erblo4               Zaxegoomp4        Quarzenfleep 4       04-APR-17
         6 25-MAR-17 Erblo6               Zaxegoomp6        Quarzenfleep 6       06-APR-17
         8 23-MAR-17 Erblo8               Zaxegoomp8        Quarzenfleep 8       08-APR-17
        10 21-MAR-17 Erblo10              Zaxegoomp10       Quarzenfleep 10      10-APR-17

SQL>

All data in the common columns must match to return data; if ids and dates in in YOOPER don’t match up with the ids and dates in AMPLO no data is returned:


SQL> truncate table yooper;

Table truncated.

SQL>
SQL> begin
  2          for i in 1..10 loop
  3                  if mod(i,2) = 0 then
  4                          insert into yooper
  5                          values(i-1, sysdate-i, 'Quarzenfleep '||i, sysdate +i);
  6                  end if;
  7          end loop;
  8
  9          commit;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL>
SQL> select *
  2  from yooper natural join amplo;

No rows selected.

SQL>
SQL> select *
  2  from amplo natural join yooper;

No rows selected.

SQL>

There are matching id values, and there are matching date values between the tables but the combination of id and date produces no matching records. With a traditional inner join data could be returned based on either id values or date values (although how good those results would be is questionable, given the matching key structure).

A natural join isn’t a commonly used join type, mainly because the joined tables are not likely to contain join columns with the same name (the demonstration schema provided with Oracle is another good set of tables to use for a natural join). When such a condition exists a natural join is an option but testing is necessary to ensure that the results returned are both desirable and usable.

Just because it’s ‘correct’ doesn’t make it ‘right’. Right?

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: