Joins in Oracle SQL

 Joins

==========

Primary Forign Key Relation Ship

DEPT ((DEPTNO PK-Primary Key) 

EMP (DEPTNO FK-Foreign Key)

Cross Join

----------

select empno, ename, job, dname , loc from emp, dept;

select empno, ename, job, dname , loc from emp, dept where ename='SCOTT';

Result:

EMPNO ENAME JOB DNAME LOC

------------------------------------------

7788 SCOTT ANALYST ACCOUNTING NEW YORK

7788 SCOTT ANALYST RESEARCH DALLAS

7788 SCOTT ANALYST SALES CHICAGO

7788 SCOTT ANALYST OPERATIONS BOSTON

------------------------------------------

Equi-Join

---------

select empno, ename, job, dname , loc from emp, dept where 1=1

and emp.deptno=dept.deptno

and ename='SCOTT';

Result:

EMPNO, ENAME, JOB, DNAME, LOC

---------------------------------------

7788 SCOTT ANALYST RESEARCH DALLAS

select empno, ename, job, dname , loc from emp, dept where 1=1

and emp.deptno=dept.deptno

--and ename='SCOTT';

Result:

EMPNO, ENAME, JOB, DNAME, LOC

----------------------------------------------

7782 CLARK MANAGER ACCOUNTING NEW YORK

7934 MILLER CLERK ACCOUNTING NEW YORK

7839 KING PRESIDENT ACCOUNTING NEW YORK

7902 FORD ANALYST RESEARCH DALLAS

7788 SCOTT ANALYST RESEARCH DALLAS

7566 JONES MANAGER RESEARCH DALLAS

7369 SMITH CLERK RESEARCH DALLAS

7876 ADAMS CLERK RESEARCH DALLAS

7521 WARD SALESMAN SALES CHICAGO

7654 MARTIN SALESMAN SALES CHICAGO

7844 TURNER SALESMAN SALES CHICAGO

7900 JAMES CLERK SALES CHICAGO

7499 ALLEN SALESMAN SALES CHICAGO

7698 BLAKE MANAGER SALES CHICAGO

----------------------------------------------

Non-Equi Join 

-------------

select empno, ename, job, dname , loc from emp, dept where 1=1

and emp.deptno<dept.deptno;

Result: 25 rows 

EMPNO, ENAME, JOB, DNAME, LOC

----------------------------------------------

7521 WARD SALESMAN OPERATIONS BOSTON

7499 ALLEN SALESMAN OPERATIONS BOSTON

7654 MARTIN SALESMAN OPERATIONS BOSTON

7900 JAMES CLERK OPERATIONS BOSTON

7698 BLAKE MANAGER OPERATIONS BOSTON

7844 TURNER SALESMAN OPERATIONS BOSTON

7902 FORD ANALYST OPERATIONS BOSTON

7788 SCOTT ANALYST OPERATIONS BOSTON

7566 JONES MANAGER OPERATIONS BOSTON

7369 SMITH CLERK OPERATIONS BOSTON

7876 ADAMS CLERK OPERATIONS BOSTON

7839 KING PRESIDENT OPERATIONS BOSTON

7782 CLARK MANAGER OPERATIONS BOSTON

7934 MILLER CLERK OPERATIONS BOSTON

7902 FORD ANALYST SALES CHICAGO

7788 SCOTT ANALYST SALES CHICAGO

7566 JONES MANAGER SALES CHICAGO

7369 SMITH CLERK SALES CHICAGO

7876 ADAMS CLERK SALES CHICAGO

7839 KING PRESIDENT SALES CHICAGO

7782 CLARK MANAGER SALES CHICAGO

7934 MILLER CLERK SALES CHICAGO

7839 KING PRESIDENT RESEARCH DALLAS

7782 CLARK MANAGER RESEARCH DALLAS

7934 MILLER CLERK RESEARCH DALLAS

----------------------------------------------

select empno, ename, job, dname , loc from emp, dept

where 1=1

and ename='SMITH'

and emp.deptno<dept.deptno;

20<10

20<20

20<30

20<40

20<33

Result: 

EMPNO, ENAME, JOB, DNAME, LOC

-----------------------------------------

7369 SMITH CLERK SALES CHICAGO

7369 SMITH CLERK OPERATIONS BOSTON

select empno, ename, job, dname , loc from emp, dept

where 1=1

and ename='SMITH'

and emp.deptno>dept.deptno;

EMPNO, ENAME, JOB, DNAME, LOC

-----------------------------------------

7369 SMITH CLERK ACCOUNTING NEW YORK

select empno, ename, job, dname , loc from emp, dept where 1=1

and emp.deptno>dept.deptno;

17 rows 

EMPNO, ENAME, JOB, DNAME, LOC

-----------------------------------------

7902 FORD ANALYST ACCOUNTING NEW YORK

7788 SCOTT ANALYST ACCOUNTING NEW YORK

7566 JONES MANAGER ACCOUNTING NEW YORK

7369 SMITH CLERK ACCOUNTING NEW YORK

7876 ADAMS CLERK ACCOUNTING NEW YORK

7521 WARD SALESMAN ACCOUNTING NEW YORK

7654 MARTIN SALESMAN ACCOUNTING NEW YORK

7844 TURNER SALESMAN ACCOUNTING NEW YORK

7900 JAMES CLERK ACCOUNTING NEW YORK

7499 ALLEN SALESMAN ACCOUNTING NEW YORK

7698 BLAKE MANAGER ACCOUNTING NEW YORK

7521 WARD SALESMAN RESEARCH DALLAS

7654 MARTIN SALESMAN RESEARCH DALLAS

7844 TURNER SALESMAN RESEARCH DALLAS

7900 JAMES CLERK RESEARCH DALLAS

7499 ALLEN SALESMAN RESEARCH DALLAS

7698 BLAKE MANAGER RESEARCH DALLAS

select empno, ename, job, dname , loc from emp, dept where 1=1

and emp.deptno!=dept.deptno;

42 rows 

EMPNO, ENAME, JOB, DNAME, LOC

------------------------------------------------

7698 BLAKE MANAGER ACCOUNTING NEW YORK

7566 JONES MANAGER ACCOUNTING NEW YORK

7788 SCOTT ANALYST ACCOUNTING NEW YORK

7902 FORD ANALYST ACCOUNTING NEW YORK

7369 SMITH CLERK ACCOUNTING NEW YORK

7499 ALLEN SALESMAN ACCOUNTING NEW YORK

7521 WARD SALESMAN ACCOUNTING NEW YORK

7654 MARTIN SALESMAN ACCOUNTING NEW YORK

7844 TURNER SALESMAN ACCOUNTING NEW YORK

7876 ADAMS CLERK ACCOUNTING NEW YORK

7900 JAMES CLERK ACCOUNTING NEW YORK

7839 KING PRESIDENT RESEARCH DALLAS

7698 BLAKE MANAGER RESEARCH DALLAS

7782 CLARK MANAGER RESEARCH DALLAS

7499 ALLEN SALESMAN RESEARCH DALLAS

7521 WARD SALESMAN RESEARCH DALLAS

7654 MARTIN SALESMAN RESEARCH DALLAS

7844 TURNER SALESMAN RESEARCH DALLAS

7900 JAMES CLERK RESEARCH DALLAS

7934 MILLER CLERK RESEARCH DALLAS

7839 KING PRESIDENT SALES CHICAGO

7782 CLARK MANAGER SALES CHICAGO

7566 JONES MANAGER SALES CHICAGO

7788 SCOTT ANALYST SALES CHICAGO

7902 FORD ANALYST SALES CHICAGO

7369 SMITH CLERK SALES CHICAGO

7876 ADAMS CLERK SALES CHICAGO

7934 MILLER CLERK SALES CHICAGO

7839 KING PRESIDENT OPERATIONS BOSTON

7698 BLAKE MANAGER OPERATIONS BOSTON

7782 CLARK MANAGER OPERATIONS BOSTON

7566 JONES MANAGER OPERATIONS BOSTON

7788 SCOTT ANALYST OPERATIONS BOSTON

7902 FORD ANALYST OPERATIONS BOSTON

7369 SMITH CLERK OPERATIONS BOSTON

7499 ALLEN SALESMAN OPERATIONS BOSTON

7521 WARD SALESMAN OPERATIONS BOSTON

7654 MARTIN SALESMAN OPERATIONS BOSTON

7844 TURNER SALESMAN OPERATIONS BOSTON

7876 ADAMS CLERK OPERATIONS BOSTON

7900 JAMES CLERK OPERATIONS BOSTON

7934 MILLER CLERK OPERATIONS BOSTON

------------------------------------------------

Outer Joins 

----------

Outer Joins

Left

----

select empno,ename, job,dname ,loc,emp.deptno from emp, dept

where 1=1

and emp.deptno=dept.deptno (+);

-- 14 records

EMPNO, ENAME, JOB, DNAME, LOC,  DEPTNO

------------------------------------------------

7934 MILLER CLERK ACCOUNTING NEW YORK 10

7782 CLARK MANAGER ACCOUNTING NEW YORK 10

7839 KING PRESIDENT ACCOUNTING NEW YORK 10

7876 ADAMS CLERK RESEARCH DALLAS 20

7369 SMITH CLERK RESEARCH DALLAS 20

7902 FORD ANALYST RESEARCH DALLAS 20

7788 SCOTT ANALYST RESEARCH DALLAS 20

7566 JONES MANAGER RESEARCH DALLAS 20

7900 JAMES CLERK SALES CHICAGO 30

7844 TURNER SALESMAN SALES CHICAGO 30

7654 MARTIN SALESMAN SALES CHICAGO 30

7521 WARD SALESMAN SALES CHICAGO 30

7499 ALLEN SALESMAN SALES CHICAGO 30

7698 BLAKE MANAGER SALES CHICAGO 30

------------------------------------------------

Right

----

select empno, ename, job, dname ,loc, emp.deptno from emp, dept

where 1=1

and emp.deptno (+)=dept.deptno;

-- 15 records 

EMPNO, ENAME, JOB, DNAME, LOC,  DEPTNO

------------------------------------------------

7782 CLARK MANAGER ACCOUNTING NEW YORK 10

7934 MILLER CLERK ACCOUNTING NEW YORK 10

7839 KING PRESIDENTACCOUNTING NEW YORK 10

7902 FORD ANALYST RESEARCH DALLAS 20

7788 SCOTT ANALYST RESEARCH DALLAS 20

7566 JONES MANAGER RESEARCH DALLAS 20

7369 SMITH CLERK RESEARCH DALLAS 20

7876 ADAMS CLERK RESEARCH DALLAS 20

7521 WARD SALESMAN SALES CHICAGO 30

7654 MARTIN SALESMAN SALES CHICAGO 30

7844 TURNER SALESMAN SALES CHICAGO 30

7900 JAMES CLERK SALES CHICAGO 30

7499 ALLEN SALESMAN SALES CHICAGO 30

7698 BLAKE MANAGER SALES CHICAGO 30

OPERATIONS BOSTON

------------------------------------------------

Full

----

select empno, ename, job, dname, loc from emp e full outer join dept d

on(e.deptno=d.deptno);

--15 records 

EMPNO, ENAME, JOB, DNAME, LOC

------------------------------------------------

7839 KING PRESIDENT ACCOUNTING NEW YORK

7698 BLAKE MANAGER SALES CHICAGO

7782 CLARK MANAGER ACCOUNTING NEW YORK

7566 JONES MANAGER RESEARCH DALLAS

7788 SCOTT ANALYST RESEARCH DALLAS

7902 FORD ANALYST RESEARCH DALLAS

7369 SMITH CLERK RESEARCH DALLAS

7499 ALLEN SALESMAN SALES CHICAGO

7521 WARD SALESMAN SALES CHICAGO

7654 MARTIN SALESMAN SALES CHICAGO

7844 TURNER SALESMAN SALES CHICAGO

7876 ADAMS CLERK RESEARCH DALLAS

7900 JAMES CLERK SALES CHICAGO

7934 MILLER CLERK ACCOUNTING NEW YORK

OPERATIONS BOSTON

------------------------------------------------

Self Joins 

----------

Joing a table with itself.

select 

e1.empno,

e1.ename,

e2.ename,

e2.mgr

from 

emp e1,

emp e2

where 1=1

and e2.empno=e1.mgr 

EMPNO, ENAME, ENAME_1, MGR

----------------------------

7902 FORD JONES 7839

7788 SCOTT JONES 7839

7499 ALLEN BLAKE 7839

7900 JAMES BLAKE 7839

7844 TURNER BLAKE 7839

7654 MARTIN BLAKE 7839

7521 WARD BLAKE 7839

7934 MILLER CLARK 7839

7876 ADAMS SCOTT 7566

7698 BLAKE KING

7782 CLARK KING

7566 JONES KING

7369 SMITH FORD 7566

----------------------------

===============

Quiz 

Oracle SQL Joins Quiz

Section 1: Fundamentals and Prerequisites

1. Importance of Joins

What is the significance of joins in Oracle development?

A) They are only used for reporting.

B) There won't be any developments without joins in Oracle.

C) They are necessary for creating indexes.

D) They replace the need for constraints.

2. Prerequisite for Joins

What must a developer be aware of as a prerequisite for using joins?

A) The use of table aliases.

B) The existence of Primary Key and Foreign Key relationship constraints.

C) The data type must always be VARCHAR2.

D) That both tables have the exact same column names.2

3. Cross Product Definition

What is the result called when a query is run against multiple tables but is missing the join condition?

A) Equijoin

B) Non-Equijoin

C) Cross Product or Cross Join

D) Full Outer Join3

4. Cross Product Calculation

If the EMP table has 14 records and the DEPT table has 5 records, how many total records will a Cross Product (a wrong join) produce?

A) 19

B) 70

C) 14

D) 54

5. Common Join Column

In the Primary Key example, what is the name of the common column that relates the EMP and DEPT tables?

A) EMPNO

B) MGR

C) DP number (or DEPTNO)

D) LOCATION5

Section 2: Equijoin and Non-Equijoin

6. Equijoin Operator

Which operator defines an Equijoin?

A) Less than (<)

B) Not equals (!=)

C) Equal (=)

D) Greater than (>)

7. Correct Equijoin Result

When the 14-record EMP table is correctly joined to the DEPT table using an Equijoin, how many records should the output display?

A) 70 records

B) 14 records

C) 19 records

D) 37 records7

8. Non-Equijoin Definition

What distinguishes a Non-Equijoin from an Equijoin?

A) It uses a logical operator other than the equal symbol.

B) It requires table aliases.

C) It is defined only by the NOT IN clause.

D) It must use a subquery.

9. Result Count in Non-Equijoin

How does the number of records returned by a Non-Equijoin compare to an Equijoin?

A) It is always 70, like a Cross Product.

B) It is always a fixed 14 records.

C) It is variable (e.g., 19 or 37 records) depending on the condition used.

D) It is always 0 records.

10. Real-Time Usage

In real-time development, which type of join does the speaker note they rarely get a chance to use?

A) Equijoin

B) Outer Join

C) Non-Equijoin

D) Self Join10

Section 3: Outer Joins

11. Purpose of Outer Joins

What is the main purpose of an Outer Join?

A) To filter out all non-matching records.

B) To fetch all matching records and any non-matching records from one or both tables.

C) To join a table to itself.

D) To use only the ANSI SQL standard syntax.

12. Fetching from One Side

Which types of Outer Joins are designed to fetch non-matching records from only one side?

A) Equijoin and Non-Equijoin

B) Full Outer Join

C) Left Outer Join and Right Outer Join

D) Self Join12

13. Full Outer Join Capability

What unique capability does the Full Outer Join offer?

A) It ensures only 14 records are returned.

B) It fetches non-matching records from both sets (tables).

C) It is only used for hierarchical data.

D) It must use the Oracle-specific (+) operator.

14. Oracle-Specific Outer Join Syntax

In Oracle-specific syntax, which symbol is used to perform a Left or Right Outer Join?

A) The asterisk (*)

B) The percent sign (%)

C) The plus operator ((+))

D) The double ampersand (&&)

15. Right Outer Join Example

When the plus operator ((+)) was placed on the left side of the condition (the EMP table side), what was the result?

A) It brought back Department 40, which has no employees, increasing the count to 15 records.

B) It brought back only the 14 matching records.

C) It resulted in a Cross Product.

D) It produced a syntax error.14

Section 4: Self Join

16. Self Join Definition

What is a Self Join?

A) Joining two different tables using a non-equal condition.

B) Joining a table with itself.

C) A synonym for a Full Outer Join.

D) The standard term for an Equijoin.15

17. Use Case for Self Join

What is the primary scenario where a Self Join is used?

A) To determine which departments are empty.

B) To bring data in a hierarchical manner (e.g., finding out who is the manager for whom).

C) To join the employee table with the department table.

D) To use a logical operator like less than.15

18. Self Join Condition

When joining the EMP table to itself (using aliases E1 and E2), which column from E1 is typically joined to the EMPNO column from E2 to map the hierarchy?

A) DEPTNO

B) JOB

C) MGR (Manager Number)

D) NAME16

19. Necessity of Aliases

Why are aliases (like E1 and E2) required when performing a Self Join?

A) To make the query shorter.

B) To allow the single table to be treated as two separate tables in the query.

C) To enable the use of the (+) operator.

D) To define the Primary Key and Foreign Key.17

20. Self Join Cross Product

What was the Cross Product result when the 14-record EMP table was joined to itself without a join condition?

A) 14

B) 70

C) 196

D) 35017

Answers:

1 B

2 B

3 C

4 B

5 C

6 C

7 B

8 A

9 C

10 C

11 B

12 C

13 B

14 C

15 A

16 B

17 B

18 C

19 B

20 C