A

E BR
L G
A
A L
N
O
I R T
2

T
LA –
A
P

E
R I T
3

N
U
RELATIONAL ALGEBRA
Relational algebra is a procedural query language. It
gives a step by step process to obtain the result of
the query. It uses operators to perform queries.
Types of Relational operation
SELECT OPERATION
The select operation selects tuples that satisfy a given
predicate.
It is denoted by sigma (σ).
Notation: σ p(r)
Where:
σ is used for selection prediction
r is used for relation
p is used as a propositional logic formula which may use
connectors like: AND OR and NOT. These relational can
use as relational operators like =, ≠, ≥, <, >, ≤.
For example: LOAN Relation

BRANCH_NAME LOAN_NO AMOUNT

Downtown L-17 1000

Redwood L-23 2000

Perryride L-15 1500

Downtown L-14 1500

Mianus L-13 500

Roundhill L-11 900

Perryride L-16 1300

Input:
σ BRANCH_NAME="perryride" (LOAN)

BRANCH_NAME LOAN_NO AMOUNT

Perryride L-15 1500

Perryride L-16 1300
PROJECT OPERATION:

This operation shows the list of those attributes that we wish

to appear in the result. Rest of the attributes are eliminated
from the table.
It is denoted by ∏.
Notation: ∏ A1, A2, An (r)
Where
A1, A2, A3 is used as an attribute name of relation r.
Example: CUSTOMER RELATION

NAME STREET CITY

Jones Main Harrison OUTPUT:

Smith North Rye
Hays Main Harrison NAME CITY

Curry North Rye Jones Harrison

Johnson Alma Brooklyn

Smith Rye
Brooks Senator Brooklyn
Hays Harrison
Input:
∏ NAME, CITY (CUSTOMER)
Curry Rye
Johnson Brooklyn

Brooks Brooklyn
UNION OPERATION
Suppose there are two tables R and S. The union
operation contains all the tuples that are either in R or
S or both in R & S.
It eliminates the duplicate tuples. It is denoted by ∪.
Notation: R ∪ S
A union operation must hold the following condition:
R and S must have the attribute of the same number.
Duplicate tuples are eliminated automatically.
Example:

DEPOSITOR RELATION
CUSTOMER_NAME ACCOUNT_NO

Johnson A-101
Smith A-121
BORROW RELATION
Mayes A-321
Turner A-176
Johnson A-273
Jones A-472
Lindsay A-284
CUSTOMER_NAME LOAN_NO
Jones L-17
Smith L-23
Hayes L-15
CUSTOMER_NAME
Jackson L-14
Curry L-93 Johnson

Smith L-11 Smith

Hayes
Williams L-17
Turner

Input: Jones
∏ CUSTOMER_NAME (BORROW) ∪ ∏ CUST Lindsay
OMER_NAME (DEPOSITOR)
Jackson
Curry
Williams
Mayes
SET INTERSECTION
Suppose there are two tuples R and S. The set intersection
operation contains all tuples that are in both R & S(common
tuples).
It is denoted by intersection ∩.
Notation: R ∩ S
Example: Using the above DEPOSITOR table and BORROW table
Input:
∏ CUSTOMER_NAME (BORROW) ∩ ∏ CUSTOMER_NAME (DEPOSIT
OR) CUSTOMER_NAME
Output:
Smith
Jones
SET DIFFERENCE
Suppose there are two tuples R and S. The set intersection
operation contains all tuples that are in R but not in S.
It is denoted by intersection minus (-).
Notation: R - S
Example: Using the above DEPOSITOR table and BORROW table
Input:
∏ CUSTOMER_NAME (BORROW) - ∏ CUSTOMER_NAME (DEPOSIT
OR)
Output: CUSTOMER_NAME
Jackson
Hayes
Willians
Curry
CARTESIAN PRODUCT

The Cartesian product is used to combine each

row in one table with each row in the other
table. It is also known as a cross product.
It is denoted by X.
Notation: E X D
Example:
EMP_ID EMP_NAME EMP_DEPT
EMPLOYEE
1 Smith A
2 Harry C
3 John B
DEPARTMENT

DEPT_NO DEPT_NAME

A Marketing
B Sales
C Legal

Input:
EMPLOYEE X DEPARTMENT
Output:
EMP_ID EMP_NAME EMP_DEPT DEPT_NO DEPT_NAME

1 Smith A A Marketing
1 Smith A B Sales
1 Smith A C Legal
2 Harry C A Marketing
2 Harry C B Sales
2 Harry C C Legal
3 John B A Marketing
3 John B B Sales
3 John B C Legal
RENAME OPERATION
The rename operation is used to rename the output relation. It
is denoted by rho (ρ).
Example: We can use the rename operator to rename STUDENT
relation to STUDENT1.
ρ(STUDENT1, STUDENT)
SET THEORETICAL OPERATIONS ON
RELATIONS
The SQL Set operation is used to combine the two or more SQL SELECT
statements.
Types of Set Operation
1. Union
2. UnionAll
3. Intersect
4. Minus
UNION
The SQL Union operation is used to combine the result of two or more
SQL SELECT queries.
In the union operation, all the number of datatype and columns must
be same in both the tables on which UNION operation is being
applied.
The union operation eliminates the duplicate rows from its resultset.
Syntax
SELECT column_name FROM table1
UNION
SELECT column_name FROM table2;
EXAMPLE
The First table The Second table

ID NAME ID NAME
1 Jack 3 Jackson
2 Harry 4 Stephan
3 Jackson 5 David

Union SQL query will be:

SELECT * FROM First
UNION
SELECT * FROM Second;
The resultset table will look like:

ID NAME
1 Jack
2 Harry
3 Jackson
4 Stephan
5 David
UNION ALL
Union All operation is equal to the Union operation. It returns the set
without removing duplication and sorting the data.
Syntax:
SELECT column_name FROM table1
UNION ALL
SELECT column_name FROM table2;
Example: Using the above First and Second table.
Union All query will be like:
SELECT * FROM First
UNION ALL
SELECT * FROM Second;
The resultset table will look like:

ID NAME
1 Jack
2 Harry
3 Jackson
3 Jackson
4 Stephan
5 David
 INTERSECT
It is used to combine two SELECT statements. The Intersect operation returns
the common rows from both the SELECT statements.
In the Intersect operation, the number of datatype and columns must be the
same.
It has no duplicates and it arranges the data in ascending order by default.
Syntax
SELECT column_name FROM table1
INTERSECT
SELECT column_name FROM table2;
Example:
Using the above First and Second table.
Intersect query will be:

SELECT * FROM First

INTERSECT
SELECT * FROM Second;
The result set table will look like:

ID NAME
3 Jackson
MINUS
It combines the result of two SELECT statements. Minus operator is used to
display the rows which are present in the first query but absent in the second
query.
It has no duplicates and data arranged in ascending order by default.
Syntax:
SELECT column_name FROM table1
MINUS
SELECT column_name FROM table2;
Example
Using the above First and Second table.
Minus query will be:
SELECT * FROM First
MINUS
SELECT * FROM Second;
The resultset table will look like:

ID NAME

1 Jack

2 Harry
JOIN OPERATIONS
A Join operation combines related tuples from different relations, if and
only if a given join condition is satisfied. It is denoted by ⋈.
Example:
EMPLOYEE

EMP_CODE EMP_NAME
101 Stephan
102 Jack
103 Harry
 SALARY

EMP_CODE SALARY

101 50000
102 30000
103 25000

Operation: (EMPLOYEE ⋈ SALARY)

Result

EMP_COD EMP_NA SALARY

E ME

101 Stephan 50000

102 Jack 30000
103 Harry 25000
TYPES OF JOIN OPERATIONS:
NATURAL JOIN
A natural join is the set of tuples of all combinations in R and S that are
equal on their common attribute names.
It is denoted by ⋈.
Example: Let's use the above EMPLOYEE table and SALARY table:
Input:
∏EMP_NAME, SALARY (EMPLOYEE ⋈ SALARY)
Output:

EMP_NAME SALARY
Stephan 50000
Jack 30000
Harry 25000
OUTER JOIN
The outer join operation is an extension of the join operation. It is used
to deal with missing information.
Example:
EMPLOYEE

EMP_NAME STREET CITY

Ram Civil line Mumbai

Shyam Park street Kolkata
Ravi M.G. Street Delhi
Hari Nehru nagar Hyderabad
FACT_WORKERS

EMP_NA BRANCH SALARY

ME

Ram Infosys 10000

Shyam Wipro 20000
Kuber HCL 30000
Hari TCS 50000

Input: EMP_ STREE CITY BRAN SALA

(EMPLOYEE ⋈ FACT_WORKERS)
NAME T CH RY

Output: Ram Civil Mumb Infosy 10000

line ai s
Shyam Park Kolkat Wipro 20000
street a
Hari Nehru Hyder TCS 50000
nagar abad
 An outer join is basically of three types:
1. Left outer join
2. Right outer join
3. Full outer join
a. Left outer join:
• Left outer join contains the set of tuples of all combinations in R and
S that are equal on their common attribute names.
• In the left outer join, tuples in R have no matching tuples in S.
• It is denoted by ⟕.
Example: Using the above EMPLOYEE table and FACT_WORKERS table
Input:
EMPLOYEE ⟕ FACT_WORKERS

EMP_NAME STREET CITY BRANCH SALARY

Ram Civil line Mumbai Infosys 10000

Shyam Park street Kolkata Wipro 20000
Hari Nehru street Hyderabad TCS 50000

Ravi M.G. Street Delhi NULL NULL

Right outer join:
• Right outer join contains the set of tuples of all combinations in R
and S that are equal on their common attribute names.
• In right outer join, tuples in S have no matching tuples in R.
• It is denoted by ⟖.
Example: Using the above EMPLOYEE table and FACT_WORKERS
Relation
Input:
EMPLOYEE ⟖ FACT_WORKERS
Output:
EMP_NAME BRANCH SALARY STREET CITY

Ram Infosys 10000 Civil line Mumbai

Shyam Wipro 20000 Park street Kolkata

Hari TCS 50000 Nehru Hyderabad

street
Kuber HCL 30000 NULL NULL
Full outer join:
Full outer join is like a left or right join except that it contains all rows
from both tables.
In full outer join, tuples in R that have no matching tuples in S and
tuples in S that have no matching tuples in R in their common
attribute name.
It is denoted by ⟗.
Example: Using the above EMPLOYEE table and FACT_WORKERS
table
Input: EMPLOYEE ⟗ FACT_WORKERS
EMP_NAME STREET CITY BRANCH SALARY
Output:

Ram Civil line Mumbai Infosys 10000

Shyam Park street Kolkata Wipro 20000

Hari Nehru street Hyderabad TCS 50000

Ravi M.G. Street Delhi NULL NULL

Kuber NULL NULL HCL 30000

Equi join:
It is also known as an inner join. It is the most common join. It is
based on matched data as per the equality condition. The equi join
uses the comparison operator(=).
Example: PRODUCT
CUSTOMER RELATION
CLASS_ID NAME PRODUCT_ID CITY

1 John 1 Delhi
2 Harry 2 Mumbai
3 Jackson 3 Noida

Input:
CUSTOMER ⋈ PRODUCT
Output:

CLASS_ID NAME PRODUCT_ID CITY

1 John 1 Delhi
2 Harry 2 Mumbai
3 Harry 3 Noida