Document Number: <QMS Ref. No.

>

Copy Number: <copy number>

TATA CONSULTANCY SERVICES

Hyderabad

VILP Unix/C++/Oracle
Content Manual

April 2014

A Division of Tata Sons Limited

Version 1.0

Content Manual Ver. 1.0

TCS VILP Unix/C++/Oracle

DOCUMENT RELEASE NOTICE

Notice No.
Client
Project
Document details
Name

Version No.

Author

Description

04_VILP_Oracle_Joins

Neelima/4
89810

Initial draft version of Joins

Revision details:
Action taken Preceding
(add/del/chg page No.
)

New
page No.

Revision
description

Change Register serial numbers covered:

The documents or revised pages are subject to document control.
Please keep them up-to-date using the release notices from the distributor of the document.
These are confidential documents. Unauthorised access or copying is prohibited.

Approved by : ________________________________

Date: _________

Authorised by: ________________________________

Date: _________

Page i

Content Manual Ver. 1.0

TCS VILP Unix/C++/Oracle

DOCUMENT REVISION LIST

Client
Project
Document Name
Release Notice Reference (for release)
Rev.
No.

Revision
date

Revision
description

Page
No.

Prev
page
No.

Action
taken

Addenda
/New
page

Release
Notice
reference

Page ii

Content Manual Ver. 1.0

TCS VILP Unix/C++/Oracle

CONTENTS
COURSE 1 - JOINS....................................................................................................................... 1
1.1 Objective................................................................................................................................... 1
1.2 Course Content......................................................................................................................... 1
1.2.1 Introduction....................................................................................................................... 1
1.2.2 Different SQL joins............................................................................................................ 1
1.2.3 Guidelines ........................................................................................................................ 1
1.2.4 Cross Join ........................................................................................................................ 2
1.2.5 Inner Join ......................................................................................................................... 3
1.2.5.1 Equi Join..................................................................................................................... 3
1.2.5.2 Non Equi Join............................................................................................................. 3
1.2.6 Outer Join ........................................................................................................................ 4
1.2.6.1 Left Outer Join............................................................................................................ 4
1.2.6.2 Right Outer Join.......................................................................................................... 5
1.2.6.3 Full Outer Join............................................................................................................ 5
1.2.7 Self Join ........................................................................................................................... 6
1.2.8 Video1: JAVA IM Introduction to JOINS......................................................................7
1.3 Quiz Time.................................................................................................................................. 7
APPENDICES
A. xxxxxxxxxxxx
B. xxxxxxxxxxxx

Page iii

TCS VILP Unix/C++/Oracle

COURSE 1 - JOINS
1.1 Objective

Introduction
Different SQL Joins
Guidelines
Cross Join
Inner Join
Outer Join
Self Join

1.2 Course Content

1.2.1 Introduction
Join achieves the goal of creating a single SQL sentence that can manipulate
data from two or more tables.
A join is a query that combines rows from two or more tables.
A join is performed whenever multiple tables appear in the queries FROM clause.
The queries SELECT statement can select any of the columns from any of these
tables.

1.2.2 Different SQL joins

There are four different types of joins:
CROSS JOIN
INNER JOIN
EQUI JOIN
NON EQUI JOIN
OUTER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
SELF JOIN

1.2.3 Guidelines
When writing a SELECT statement that joins tables , precede the column name
with the table name.
If the same column name appears in more than one table, the column name
must be prefixed with the table name.
To join 'N' tables , a minimum of 'N-1' join conditions are required.
Consider the below tables:
Page 1

TCS VILP Unix/C++/Oracle

Employee:
ENO NAME
DID
------ -------------------26 SRIDHAR
102
11 NEELIMA
101
12 SAILAJA
101
16 SANTOSH
102
17 HEMANTH
103
18 DEEPTHI
102
19 PRASHANTH 103
21 SWATHI
104
22 SUMANTH
104
24 RAJU

DESIGNATION
SALARY
------------------------------ ---------EXECUTIVE
25000
I.T.ANALYST
15000
I.T.ANALYST
16000
I.T.ANALYST
17000
SYSTEMS ANALYST 23000
SYSTEMS ENGINEER 10000
SYSTEMS ENGINEER 9000
EXECUTIVE
21000
EXECUTIVE
23000
EXECUTIVE
22000

Department:
DID
DEPTNAME
LOC_ID
---------- ------------------------------ ---------101
PRODUCTION
101
102
SALES
102
103
PURCHASE
103
104
COMMERCIAL
104
105
IT
105

Grade:
GRA
------A
B
C
D

LOSAL
---------30001
20001
10001
1

HISAL
---------40000
30000
20000
10000

1.2.4 Cross Join

The Cross join / Cartesian product is a join query , that does not contain a join
condition. Oracle combines each row of one table with each row of the other.
Syntax: SELECT * FROM tableA , tableB;
Ex: SELECT * FROM department,grade;
DID DEPTNAME
LOC_ID
----- ---------------------- ---------101 PRODUCTION 101
101 PRODUCTION 101
101 PRODUCTION 101
101 PRODUCTION 101
102 SALES
102
102 SALES
102
102 SALES
102
102 SALES
102
103 PURCHASE
103
103 PURCHASE
103
103 PURCHASE
103
103 PURCHASE
103
104 COMMERCIAL 104

GRA
-----D
B
C
A
C
B
A
D
D
B
C
A
C

LOSAL
---------1
20001
10001
30001
10001
20001
30001
1
1
20001
10001
30001
10001

HISAL
---------10000
30000
20000
40000
20000
30000
40000
10000
10000
30000
20000
40000
20000

Page 2

TCS VILP Unix/C++/Oracle

104 COMMERCIAL
104 COMMERCIAL
104 COMMERCIAL
105 IT
105 IT
105 IT
105 IT

104
104
104
105
105
105
105

B
A
D
B
A
D
C

20001
30001
1
20001
30001
1
10001

30000
40000
10000
30000
40000
10000
20000

The above query returns 20 records. As per the department table DID 101 must
appear only once , but each row of department table is linked with each and
every row of grade table , 101 is appearing 4 times in the resultant set.

1.2.5 Inner Join

An inner is used when the join fields are guaranteed not to be NULL.
1.2.5.1 Equi Join
It is a join condition containing an equality operator ( = ). This join condition
combines the rows that have the same values for the specified columns.
Syntax : SELECT tableA.col1,tableA.col2,tableB.col1,..... FROM
tableA,tableB WHERE tableA.col1 = tableB.col1;
Ex: SELECT employee.eno ENO , employee.name NAME, employee.salary
SALARY, department.did DID, department.deptname DEPTNAME FROM
employee,department WHERE employee.did = department.did;
The above query returns all the rows that are matching with the join condition ,
i.e., the rows from both the tables where DID is matching.
ENO NAME
---------- -------------------11 NEELIMA
12 SAILAJA
16 SANTOSH
17 HEMANTH
18 DEEPTHI
19 PRASHANTH
21 SWATHI
22 SUMANTH
26 SRIDHAR

SALARY DID
------------ -----------15000
101
16000 101
17000 102
23000 103
10000 102
9000
103
21000 104
23000 104
25000 102

DEPTNAME
------------------------------PRODUCTION
PRODUCTION
SALES
PURCHASE
SALES
PURCHASE
COMMERCIAL
COMMERCIAL
SALES

The above query can also be given with the alias names provided to the table
names. Alias names can be used wherever the table names are required.
Ex: SELECT e.eno, e.name, e.salary, d.did, d.deptname FROM employee e ,
department d WHERE e.did = d.did;
1.2.5.2 Non Equi Join
It is a join condition that is executed when no column in one table is directly
Page 3

TCS VILP Unix/C++/Oracle

linked with a column in another table. The data in the tables are logically related
through appropriate values.
Syntax : SELECT tableA.col1,tableA.col2,tableB.col1,..... FROM
tableA,tableB WHERE tableA.col1 BETWEEN tableB.col2 AND tableB.col3;
Ex: SELECT eno,name,salary,grade FROM employee e,grade g WHERE
e.salary BETWEEN g.losal AND g.hisal;
ENO
---------11
12
16
17
18
19
21
22
24
26

NAME
SALARY GRA
------------------- ---------- -----NEELIMA
15000
C
SAILAJA
16000
C
SANTOSH
17000
C
HEMANTH
23000
B
DEEPTHI
10000
D
PRASHANTH 9000
D
SWATHI
21000
B
SUMANTH
23000
B
RAJU
22000
B
SRIDHAR
25000
B

The above query retrieves the employee details along with his salary grade
based on the join condition.

1.2.6 Outer Join

Outer join extends the result of Equi join. Outer join returns all the rows from both
the tables that satisfy the join condition and also the rows from one table which
do not satisfy the join condition. The Outer join operator is ( + ) , which is used on
one side of the join condition.
1.2.6.1 Left Outer Join
It is a join which returns all the rows from the left hand side of the table specified
at the join condition and only those rows from the other table which are matching
with the join condition.
Syntax : SELECT tableA.col1,tableA.col2,tableB.col1,..... FROM
tableA,tableB WHERE tableA.col1 = tableB.col1(+);
Ex: SELECT e.eno ENO, e.name NAME, e.salary SALARY, e.did
DEPTNO,d.did DEPTID , d.deptname DEPTNAME FROM employee e
,department d WHERE e.did = d.did(+);
ENO
---------12
11
18

NAME
SALARY
----------------- -----------SAILAJA
16000
NEELIMA
15000
DEEPTHI
10000

DEPTNO DEPTID
------------- ----------101
101
101
101
102
102
Page 4

DEPTNAME
-----------------------------PRODUCTION
PRODUCTION
SALES

TCS VILP Unix/C++/Oracle

16
26
19
17
22
21
24

SANTOSH
SRIDHAR
PRASHANTH
HEMANTH
SUMANTH
SWATHI
RAJU

17000
25000
9000
23000
23000
21000
22000

102
102
103
103
104
104

102
102
103
103
104
104

SALES
SALES
PURCHASE
PURCHASE
COMMERCIAL
COMMERCIAL

The above query displays all the records which are matching with the join
condition from both the tables along with the rows from employee table which are
not matched with department table.
1.2.6.2 Right Outer Join
It is a join which returns all the rows from the right hand side of the table
specified at the join condition and only those rows from the other table which are
matching with the join condition.
Syntax : SELECT tableA.col1,tableA.col2,tableB.col1,..... FROM
tableA,tableB WHERE tableA.col1(+) = tableB.col1;
Ex: SELECT e.eno ENO, e.name NAME, e.salary SALARY, e.did
DEPTNO,d.did DEPTID , d.deptname DEPTNAME FROM employee e
,department d WHERE e.did(+) = d.did;
ENO NAME
---------- ----------------------26
SRIDHAR
11
NEELIMA
12
SAILAJA
16
SANTOSH
17
HEMANTH
18
DEEPTHI
19
PRASHANTH
21
SWATHI
22
SUMANTH

SALARY DEPTNO
------------- ---------25000
102
15000
101
16000
101
17000
102
23000
103
10000
102
9000
103
21000
104
23000
104

DEPTID
-----------102
101
101
102
103
102
103
104
104
105

DEPTNAME
-----------------------------SALES
PRODUCTION
PRODUCTION
SALES
PURCHASE
SALES
PURCHASE
COMMERCIAL
COMMERCIAL
IT

The above query displays all the records which are matching with the join
condition from both the tables along with the rows from department table which
are not matched with employee table.
1.2.6.3 Full Outer Join
It is a join which returns all the rows from both the tables which are placed on
either side of the join condition.
Syntax : SELECT tableA.col1,tableA.col2,tableB.col1,..... FROM
tableA,tableB WHERE tableA.col1 = tableB.col1(+)
UNION
Syntax : SELECT tableA.col1,tableA.col2,tableB.col1,..... FROM
Page 5

TCS VILP Unix/C++/Oracle

tableA,tableB WHERE tableA.col1(+) = tableB.col1;

Ex: SELECT e.eno ENO, e.name NAME, e.salary SALARY, e.did
DEPTNO,d.did DEPTID , d.deptname DEPTNAME FROM employee e
,department d WHERE e.did = d.did(+)
UNION
SELECT e.eno ENO, e.name NAME, e.salary SALARY, e.did
DEPTNO,d.did DEPTID , d.deptname DEPTNAME FROM employee e
,department d WHERE e.did(+) = d.did;
ENO NAME
---------- ----------------------11
NEELIMA
12
SAILAJA
16
SANTOSH
17
HEMANTH
18
DEEPTHI
19
PRASHANTH
21
SWATHI
22
SUMANTH
24
RAJU
26
SRIDHAR

SALARY DEPTNO DEPTID

------------ ------------- ----------15000
101
101
16000
101
101
17000
102
102
23000
103
103
10000
102
102
9000
103
103
21000
104
104
23000
104
104
22000
25000
102
102
105

DEPTNAME
-----------------------------PRODUCTION
PRODUCTION
SALES
PURCHASE
SALES
PURCHASE
COMMERCIAL
COMMERCIAL
SALES
IT

The above query displays all the records which are matching with the join
condition from both the tables along with the rows from both employee and
department table which are not matched..

1.2.7 Self Join

It is a join used to join a table to itself. The table is logically considered as two
tables and joined to itself.
Syntax : SELECT A.* FROM tableA A,tableB B WHERE A.col1 = B.col1 AND
condition;
Ex: SELECT e1.* FROM employee e1,employee e2 WHERE
e2.name='SRIDHAR' AND e1.did=e2.did;
ENO
---------26
16
18

NAME
DID
DESIGNATION
SALARY
---------------------- ---------- ------------------------------ ---------SRIDHAR
102
EXECUTIVE
25000
SANTOSH
102
I.T.ANALYST
17000
DEEPTHI
102
SYSTEMS ENGINEER 10000

The above query is going to display the details of all those employees who are
working in the same department of "SRIDHAR".

Page 6

TCS VILP Unix/C++/Oracle

1.2.8 Video1: JAVA IM Introduction to JOINS

TCS Video Channel >> Home >> Category>> Learning Videos >> 541) JAVA -IM
- SQL Joins

1.3 Quiz Time

1. Which of the following is not a type of join?
a) Equi
b) Non equi
c) Self
d) All of the above
Ans: d
2. Self join is joining a table to itself
a) TRUE
b) FALSE
Ans: a
3. What is the join need to be used to retrieve the rows from both the
tables when the values are not matching in the columns specified in the
join condition.
a) OUTER
b) INNER
c) SELF
d) CROSS
Ans: a
4. What is the join that does a Cartesian product.
a) OUTER
b) INNER
c) SELF
d) CROSS
Ans: d
5. Non equi join is established considering ( = ) operator for the join
condition.
a) TRUE
b) FALSE
Ans: b

Page 7