10 Types of Programming Languages Every Coder should Know
Learn about different types of programming languages and how they are implemented in the real world.
8 mins read
Wonder how it can extract data from several tables within a database? How can we effectively combine data from these tables during such an extract? These are common questions when working with databases.
Joins in DBMS are a solution since they allow for combining data from many tables in related columns. This is important to acquire relevant results and insight that will, in turn, help in making relevant decisions.
Joins are very similar to adhesives that hold your data together. It will be extremely difficult to retrieve data from more than one table if no joins exist.
Let’s dive into the world of joins in DBMS and see how they work.
What is join in DBMS
A DBMS JOIN operates through SQL to link different tables using matching field contents. The DBMS JOIN functionality allows users to seek related data by creating linkages between different tables which helps produce efficient data retrieval and organization processes. Hard and soft JOIN operations including INNER JOIN and LEFT JOIN and FULL JOIN enable diverse ways to conduct data unions and analysis.
POSTGRADUATE PROGRAM IN
Multi Cloud Architecture & DevOps
Master cloud architecture, DevOps practices, and automation to build scalable, resilient systems.
Types of Join in DBMS
In a database management system (DBMS), a join operation is used to combine rows from two or more tables in a related column. Joins help retrieve meaningful data by establishing a connection between tables. There are several types of DBMS joins.
- INNER JOIN: This is used to perform the returns only for the matching rows from both tables.
- Outer JOIN: These outer joins include unmatched rows as well. There are three types.
- LEFT JOIN (LEFT OUTER JOIN): This returns all rows from the left table and the matched rows from the right table. If no match is found, NULL values are returned for columns from the right table.
- Right Join(Right Outer Join): This returns all rows from the right table and the matched rows from the left table. If no match is found, NULL values are returned for columns from the left table.
- FULL JOIN(FULL OUTER JOIN): This function returns all rows from both tables. If there is not match found, NULL values appear for missing columns from either
- CROSS JOIN: This produces the cartesian prodcut of both tables. Each row from the first table is combined with every row from the second table.
- SELF JOIN: This table join with itself. It is very useful when comparing rows within the same table.
- Natural JOIN: It is automatically joins table based on columns with the same name and data type. No need to specify the join condition explicitly.
Detailed Explanation of Inner Join in DBMS
Inner join is one of the most commonly used DBMS join. 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 enrolled in courses. Students without courses are not included.
Comprehensive Overview of Theta Join in DBMS
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 !=.
Here are the dbms joins with examples
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.
82.9%
of professionals don't believe their degree can help them get ahead at work.
Equi Join in DBMS: Concepts and Examples
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.
Here are the joins in dbms with examples
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 in DBMS Explained
Have you ever wondered how to merge tables without specifying the join condition? Natural join does just that.
Natural join combines tables based on columns with the same name and domain. It’s like matching puzzle pieces that fit perfectly.
Here is the joins in dbms with examples
Also Read: DBMS Interview Questions With Answers
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.
Understanding Outer Joins in DBMS
Outer joins help us retrieve matching records from related tables and include non-matching rows.
Left Outer Join Explained with Examples
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 join with dbms example, Alex has no matching order, so the product name is NULL.
Right Outer Join Detailed with Examples
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.
Also Read: Relational Algebra in DBMS
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, Supplier C has no matching product, so the product name is NULL.
Conclusion
Data management operations through DBMS become efficient because the technology allows users to store and retrieve data while managing its manipulation. Database management systems play multiple essential functions to ensure data consistency and protection while providing convenient accessibility, making it practical for organisations of different scales. Background joins and other types available through DBMS enable users to discover significant relationships between their data across different tables, which leads to improved decision processes.
For those eager to build dynamic web and mobile applications while mastering database management, explore the Full Stack Development with Specialization for Web and Mobile powered by Hero Vired and take your tech skills to the next level!
FAQs
What is the primary purpose of a join operation in DBMS?
The primary purpose is to combine data from two or more tables based on related columns, enabling efficient data retrieval.
What is the difference between inner join and outer join?
Inner join returns only the rows that have matching values in both tables. Outer join returns all the rows from one or both joined tables, filling in NULL for non-matching rows.
When should I use a theta join instead of an inner join?
Use a theta join when you need to join tables based on conditions other than equality, using comparison operators like >, <, >=, etc.
How does a natural join differ from an equi join?
A natural join automatically matches columns with the same name and domain, while an equi join explicitly uses equality conditions to match specified columns.
Can I use multiple joins in a single query?
Yes, we can use multiple joins in a single query to combine data from more than two tables, specifying the conditions for each join operation.
What is theta join in dbms?
A Theta Join in DBMS combines rows from two tables based on a condition using comparison operators like <,>,<=,>=, !=, or =.
Updated on February 20, 2025
Link
