×
☰ See All Chapters

Oracle JOINS

  • Combing two or more tables to a single table is called JOIN. Using joins you can retrieve data from multiple tables from single query. 

  • We can join both related (related with primary key and foreign key) as well as unrelated tables. 

  • WHERE clause is a required element of an SQL statement when joining tables. The join is performed in the WHERE clause. Several operators can be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT. 

  • For joining tables, criteria are the data type of columns. The data type of the columns which are used (used in where clause) for joining should be of the same type. 

  • The tables being joined are listed in the FROM clause. 

  • Tables can have same column names, if same column name exists in multiple table being joined, we must specify column names with table name as below: 

SELECT EMP.ENAME, DEPT.DNAME

FROM DEPT  , EMP

WHERE DEPT.DEPTNO = EMP.DEPTNO;

  • Notice that each column in the SELECT clause is preceded by the associated table name to identify each column. This is called qualifying columns in a query. Qualifying columns is only necessary for columns that exist in more than one table referenced by a query. You usually qualify all columns for consistency and to avoid questions when debugging or modifying SQL code. 

  • In the below example we joined EMP table with DEPT table. 

CREATE TABLE DEPT

       (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,

        DNAME VARCHAR2(14) ,

        LOC VARCHAR2(13) ) ;

 

CREATE TABLE EMP

       (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,

        ENAME VARCHAR2(10),

        JOB VARCHAR2(9),

        MGR NUMBER(4),

        HIREDATE DATE,

        SAL NUMBER(7,2),

        COMM NUMBER(7,2),

        DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');

INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');

INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');

INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);

INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);

INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);

INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);

INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);

INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);

INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);

INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);

INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);

INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);

INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);

INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);

INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);

INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

COMMIT;

oracle-joins-0
 

SELECT E.*, D.*

FROM DEPT D JOIN EMP E

ON D.DEPTNO = E.DEPTNO;

oracle-joins-1
 

Different type of Joins

oracle-joins-2

Using Table Aliases

  • You use table aliases to rename a table temporarily. By this actual table name does not change in the database. 

  • Giving the tables aliases is a necessity for the self-join. 

  • You can use any alias name of your choice. 

  • The selected columns were justified with the corresponding table alias.  

SELECT E.*, D.*

FROM DEPT D , EMP E

WHERE D.DEPTNO = E.DEPTNO;

 

Replacing WHERE clause by JOIN Syntax

SELECT E.*, D.*

FROM DEPT D JOIN EMP E

ON D.DEPTNO = E.DEPTNO;

The table being joined is added after the JOIN syntax, and then the JOIN operators are placed after the ON qualifier. The data type of the columns which are used (used in ON qualifier) for joining should be of the same type.

 

 

All Chapters
Author