Advertisement
ovalerio

Natural Join with Implicit Type Conversion in Oracle.

Oct 7th, 2019
1,105
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Performing natural join between tables with different datatypes in Oracle DB.
  2. -- NOTE: Oracle requires for the natural join that the tables we want to join
  3. -- have at least one column with a common name and implicitly convertible datatypes.
  4.  
  5. -- TABLE_ONE is composed of a DATE field and a NUMBER field
  6. CREATE TABLE TABLE_ONE  (
  7.     COMMON_NAME DATE,
  8.     NUM_FIELD   NUMBER);
  9.    
  10. INSERT INTO TABLE_ONE VALUES ('22-01-1995', 10);
  11. INSERT INTO TABLE_ONE VALUES ('13-02-1997', 20);
  12. INSERT INTO TABLE_ONE VALUES ('04-05-2008', 30);
  13. INSERT INTO TABLE_ONE VALUES ('15-06-2019', 40);
  14.  
  15. SELECT * FROM TABLE_ONE; -- Show TABLE_ONE contents.
  16.  
  17. -- TABLE_TWO is composed of two VARCHAR2 fields
  18. CREATE TABLE TABLE_TWO (
  19.     COMMON_NAME VARCHAR2(10),
  20.     CHAR_FIELD  CHAR(10));
  21.  
  22. INSERT INTO TABLE_TWO VALUES ('22.01.1995', 'JANUAR');
  23. INSERT INTO TABLE_TWO VALUES ('13.02.1997', 'FEBRUAR');
  24. INSERT INTO TABLE_TWO VALUES ('04.05.2008', 'MAI');
  25. INSERT INTO TABLE_TWO VALUES ('17.09.1999', 'SEPTEMBER');
  26.  
  27. SELECT * FROM TABLE_TWO; -- Show TABLE_TWO contents.
  28.  
  29. -- Performing a NATURAL JOIN between TABLE_ONE and TABLE_TWO with implicit type conversion.
  30. -- The natural join is based on the field COMMON_NAME
  31. SELECT * FROM TABLE_ONE NATURAL JOIN TABLE_TWO;
Advertisement
RAW Paste Data Copied
Advertisement