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 ⋈.
In a relational database management system (RDBMS), the join operation combines rows from two or more
tables based on a related column between them
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_CODE EMP_NAME SALARY

101 Stephan 50000

102 Jack 30000

103 Harry 25000

Types of Join operations:

1) Inner join
• Inner join is one of the most commonly used joins in DBMS. It joins rows from two or more tables
based on some common fields or columns.
• In an inner join, we end up with only those rows that find a match with other rows in the table. This
means we are not going to have any rows that don’t match anything in the second table.
Example:
Consider two tables, Students and Courses:
Students Table:

student_id name age

 1 Neha 20

2 Murali 22

3 Divyansh 19

Courses Table:

course_id student_id course_name

101 1 Math

102 2 Science

103 4 History

SQL Query:
SELECT Students.name, Courses.course_name FROM Students INNER JOIN Courses ON Students.student_id =
Courses.student_id;
Output:

name course_name

Neha Math

Murali Science

In this example, we get only the students who have enrolled in courses. Students without courses are not
included.
Theta Join
• Theta join is more flexible than the inner join. It allows us to join tables based on any condition, not
just equality.
• We can use any comparison operator such as >, <, >=, <=, or !=.
Example:
Consider two tables, Employees and Departments:
Employees Table:

emp_id name salary dept_id

1 Divyansh 50000 10

2 Krish 60000 20
 3 Neha 55000 30

Departments Table:

dept_id dept_name min_salary

10 HR 45000

20 IT 55000

30 Sales 52000

SQL Query:
SELECT Employees.name, Departments.dept_name FROM Employees JOIN Departments
ON Employees.salary >= Departments.min_salary;
Output:

name dept_name

Divyansh HR

Krish IT

Neha Sales

Here, we get employees who meet or exceed the minimum salary requirement for their department.
Equi Join
• Equi join is a type of theta join where the condition is always based on equality. It’s similar to an inner
join but explicitly uses the equality operator.
Example:
Consider two tables, Authors and Books:
Authors Table:

author_id author_name

1 Mark Twain

2 Jane Austen

3 J.K. Rowling

Books Table:

book_id title author_id

 101 Tom Sawyer 1

102 Pride and Prejudice 2

103 Harry Potter 3

SQL Query:
SELECT Authors.author_name, Books.title FROM Authors INNER JOIN Books ON Authors.author_id =
Books.author_id;
Output:

author_name title

Mark Twain Tom Sawyer

Jane Austen Pride and Prejudice

J.K. Rowling Harry Potter

In this example, we get a list of authors and their books. Only matching rows based on author_id are included.
Natural Join
• Natural join combines tables based on columns with the same name and domain. It’s like matching
puzzle pieces that fit perfectly.
Example:
Consider two tables, Employees and Departments:
Employees Table:

emp_id name dept_id

1 Suman 10

2 Sonali 20

3 Aditi 30

Departments Table:

dept_id dept_name

10 HR

20 IT

30 Sales
SQL Query:
SELECT Employees.name, Departments.dept_name FROM Employees NATURAL JOIN Departments;
Output:

name dept_name

Suman HR

Sonali IT

Aditi Sales

In this example, we get a list of employees with their department names. Natural join uses the dept_id
column to match rows from both tables.
2.Outer Joins
• Outer joins help us retrieve matching records from related tables and include non-matching rows.
• 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_NAME BRANCH SALARY

Ram Infosys 10000

Shyam Wipro 20000

Kuber HCL 30000

Hari TCS 50000

Input:
(EMPLOYEE ⋈ FACT_WORKERS)
Output:
 EMP_NAME STREET CITY BRANCH SALARY

Ram Civil line Mumbai Infosys 10000

Shyam Park street Kolkata Wipro 20000

Hari Nehru nagar Hyderabad TCS 50000

An outer join is basically of three types:

• Left outer join
• Right outer join
• Full outer join
Left Outer Join
• The left outer join returns all rows from the left table and matching rows from the right table. Non-
matching rows in the right table are filled with NULL.
Example:
Consider two tables, Customers and Orders:
Customers Table:

customer_id customer_name

1 Murali

2 Divyansh

3 Anupriya

Orders Table:

order_id customer_id product_name

101 1 Laptop

102 2 Phone

103 4 Tablet

SQL Query:
SELECT Customers.customer_name, Orders.product_name FROM Customers LEFT JOIN Orders
ON Customers.customer_id = Orders.customer_id;
Output:

customer_name product_name
 Murali Laptop

Divyansh Phone

Anupriya NULL

In this example, we see that Alex has no matching order, so the product name is NULL.
Right Outer Join
• The right outer join returns all rows from the right table and matching rows from the left table. Non-
matching rows in the left table are filled with NULL.
Example:
Consider two tables, Products and Suppliers:
Products Table:

product_id product_name

1 Laptop

2 Phone

3 Tablet

Suppliers Table:

supplier_id product_id supplier_name

101 1 Supplier A

102 2 Supplier B

103 4 Supplier C

SQL Query:
SELECT Products.product_name, Suppliers.supplier_name FROM Products RIGHT JOIN Suppliers
ON Products.product_id = Suppliers.product_id;
Output:

product_name supplier_name

Laptop Supplier A

Phone Supplier B

NULL Supplier C
Here, we see that Supplier C has no matching product, so the product name is 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

EMP_NAME STREET CITY

Ram Civil line Mumbai

Shyam Park street Kolkata

Ravi M.G. Street Delhi

Hari Nehru nagar Hyderabad

FACT_WORKERS

EMP_NAME BRANCH SALARY

Ram Infosys 10000

Shyam Wipro 20000

Kuber HCL 30000

Hari TCS 50000

Input:
EMPLOYEE ⟗ FACT_WORKERS
Output:

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

Kuber NULL NULL HCL 30000