Join columns with full table name reference : Table Join Column « Table Joins « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Table Joins
  3. Table Join Column

Join columns with full table name reference

  

SQL> CREATE TABLE emps (
  2    emp varchar(30)
  3   ,title    varchar(30)
  4  );

Table created.

SQL>
SQL> INSERT INTO emps VALUES ('Tom','Programmer');

1 row created.

SQL> INSERT INTO emps VALUES ('Jack','Tester');

1 row created.

SQL> INSERT INTO emps VALUES ('Mary','Technician');

1 row created.

SQL>
SQL> CREATE TABLE JobLevel (
  2    title     varchar(30)
  3   ,rank    varchar(30)
  4  );

Table created.

SQL>
SQL> INSERT INTO JobLevel VALUES ('Programmer','Level1');

1 row created.

SQL> INSERT INTO JobLevel VALUES ('Tester','Level2');

1 row created.

SQL> INSERT INTO JobLevel VALUES ('Technician','Level3');

1 row created.

SQL>
SQL> CREATE TABLE salary (
  2    rank     varchar(30)
  3   ,payment  DECIMAL(10,2)
  4  );

Table created.

SQL>
SQL> INSERT INTO salary VALUES ('Level1',2000.00);

1 row created.

SQL> INSERT INTO salary VALUES ('Level2',3000.00);

1 row created.

SQL> INSERT INTO salary VALUES ('Level3',5000.00);

1 row created.

SQL> INSERT INTO salary VALUES ('Level4',6000.00);

1 row created.

SQL>
SQL> select * from emps;



EMP
------------------------------
TITLE
------------------------------
Tom
Programmer

Jack
Tester

Mary
Technician

3 rows selected.

SQL> select * from JobLevel;



TITLE
------------------------------
RANK
------------------------------
Programmer
Level1

Tester
Level2

Technician
Level3

3 rows selected.

SQL> select * from salary;



RANK                               PAYMENT
------------------------------  ----------
Level1                                2000
Level2                                3000
Level3                                5000
Level4                                6000

4 rows selected.

SQL>
SQL> SELECT payment FROM salary,JobLevel,emps
  2  WHERE  salary.rank = JobLevel.rank
  3  AND    JobLevel.title = emps.title
  4  AND    emps.emp = 'Jack';



   PAYMENT
----------
      3000

1 row selected.

SQL>
SQL> DROP TABLE emps;

Table dropped.

SQL> DROP TABLE JobLevel;

Table dropped.

SQL> DROP TABLE salary;

Table dropped.

SQL>
SQL>
SQL>








Related examples in the same category

1.Join two tables: column is not mentioned in the SELECT list
2.Join with (+)
3.Use in operator with table join
4.Use column name alias during table join
5.3-Way join
6.Join and GROUP BY
7.Join three table to find out which employee sold that gift
8.Join using and join on
9.Join using clause