Home » DBMS

Join Operation Vs Nested Query in DBMS

Join Operation Vs. Nested Query: In this tutorial, we will learn about the join operations and nested queries in detail, and the differences between join operations and nested queries in the database management system (DBMS). By Anushree Goswami Last updated : May 31, 2023

Join Operations

Join operations and nested queries both works to combine the data, which is allocated in different tables to make a single result. Both the join and nested query operations are similar but there are some differences which help a user to choose between them on specific situations.

Let's look at how join works and what are its types?

A join operation is used to combine related tuples or rows from two or more different tables or relations, if and only if, the condition which is given is satisfied.

Join operations are of two types,

1. Inner join
   1. Theta join
   2. Equi join
   3. Natural join
2. Outer join
   1. Left outer join
   2. Right outer join
   3. Full outer join

1) Inner join

An Inner Join is used when a user has to combine related tuples with similar attributes and the tuples which are left are discarded in resulting table or relation.

a) Theta join: Theta join is used when a user has to combine tuples from different tables or relations, If and only if they satisfy the theta condition. It is denoted by θ. Theta Join can use all types of comparative operators.

Example: Suppose T1(A1, A2, ..., AN) and T2(B1, B2, ..., BN) are two tables given whose not a single value of attributes is same then the combined result will be T1 union T2= θ.

b) Equi Join: Equi Join is used when a user has to use equality operator in Theta join.

Example: Two tables are given Employee and Project.

Employee:

Emp.I.d.	Name	Section
202	Anubhav verma	2B
203	Smita singh	3B

Project:

Standard	Projects
2B	C++
2B	Python
3B	Java
3B	Adobe

After combining them through Equi Join, we get

Employee Details:

Emp.I.d.	Name	Section	Standard	Projects
202	Anubhav verma	2B	2B	C++
202	Anubhav verma	2B	2B	Python
203	Smita singh	3B	3B	Java
203	Smita singh	3B	3B	Adobe

C) Natural Join: Natural Join is used when there has to be at least one common tuples attribute between two tables or relations. The attributes must have the same name and domain. Natural Join does not use any comparative operator. Natural Join works between those tuples whose attributes values are the same in both the tables and relations. It is denoted by ⋈.

Example: Two tables are given Student and Subject.

Student:

Roll no.	Name	Class
4	Aman	10
7	Raghav	11
8	Sameer	12

Subject:

Class	Subjects
10	Maths
11	Science
12	Biology

The Resulting table is:

Student ⋈ Subjects

Roll no.	Name	Class	Subjects
4	Aman	10	Maths
7	Raghav	11	Science
8	Sameer	12	Biology

2) Outer Join

An Outer Join is used when a user has to combine all the tuples with attributes without any discarding in resulting table or relation.

a) Left outer join: This operation is used when the user has to include tuples from the Left table in the resulting table. If the tuples in the Right side of the table are not similar to the Left side of the table then the tuples of the right side are made NULL.

Example,

Left Side Table:

A	B
203	Taj Palace
205	Royal plaza
207	Hotel Villas

Right Side Table:

A	B
203	Karan
204	Raghubir
205	Satyam

Resultant Table:

A	B	C	D
203	Taj Palace	203	Karan
205	Royal plaza	205	Satyam
207	Hotel Villas	---	---

b) Right outer join: This operation is used when the user has to include tuples from the right table in the resulting table. If the tuples in the left side of the table are not similar to the right side of the table then the tuples of the right side are made NULL.

Example,

Left Side Table:

A	B
203	Taj Palace
205	Royal plaza
207	Hotel Villas

Right Side Table:

A	B
203	Karan
204	Raghubir
205	Satyam

Resultant Table:

A	B	C	D
203	Taj Palace	203	Karan
---	---	204	Raghubir
205	Taj Palace	205	Satyam

c) Full outer join: This operation is used when the user has to include tuples from both sides in the resulting table. If the tuples are not the same in both the tables, then the not similar attributes are made NULL.

Example,

Left Side Table:

A	B
203	Taj Palace
205	Royal plaza
207	Hotel Villas

Right Side Table:

A	B
203	Karan
204	Raghubir
205	Satyam

Resultant Table:

A	B	C	D
203	Taj Palace	203	Karan
---	---	204	Raghubir
205	Taj Palace	205	Satyam
207	Hotel Villas	---	---

Let's look at how nested query works?

Nested Query

A Nested query also is known as Subquery, sub-selector, an inner query is a SELECT query within another SQL query, which is embedded within the WHERE or HAVING Clause.

The data which is used in Nested query is returned by the nested query and used in the form of the condition in the main query, which further restricts so that the retrieval of the data should not be followed.

The rules which are followed by nested queries are,

1. A nested query must always be enclosed within parentheses.
2. An ORDER BY command can be used by the main query but not by the nested query. For the ORDER BY command, in nested query GROUP BY command can be used to perform the same function.
3. For the Column purpose, A nested query must return a single column within the Select* Clause.
4. For the Row purpose, a nested query may use to return multiple columns.
5. The nested queries that return more than one row can only be used with multiple value operators, such as IN or NOT IN operator.
6. Those values that evaluate to a BLOB, ARRAY, CLOB, or NCLOB cannot include any references in the SELECT LIST.
7. A nested query cannot form towards a union. Only the statement, which is a single SELECT, is allowed.
8. A nested query cannot be appearing within a set function immediately.
9. Within the nested query, The BETWEEN operator can be used but not with a nested query.

Repeatedly, Nested queries are mostly used with the SELECT statement. They can also be used within another nested query, INSERT, UPDATE or DELETE.

Nested queries with the SELECT statement

Nested queries are most often used with the SELECT statement. Its syntax is as follows,

Example: The Engineers table with their salaries record is given,

ID	Name	Age	Address	Salary
1	Arun	34	Kanpur	30,000
2	Kamal	23	Lucknow	34,000
3	Ajay	32	Mumbai	25,000
4	Shubham	28	Delhi	26,000
5	Anurag	26	Bangalore	24,000
6	Shivam	27	Hyderabad	23,000
7	karan	24	Noida	32,000
8	Himanshu	33	Chennai	20,000

Now, The Nested query for the following records,

SQL> SELECT *
   FROM Engineers 
   WHERE ID IN (SELECT ID 
         FROM Engineers
         WHERE SALARY > 26,000) ;

The result is,

ID	Name	Age	Address	Salary
1	Arun	34	Kanpur	30,000
2	Kamal	23	Lucknow	34,000
7	karan	24	Noida	32,000

Nested queries with the UPDATE statement

The nested query can be used with the UPDATE statement in Conjunction. By using the nested query with the UPDATE statement, we can update either single or multiple columns in the table.

Example: Suppose the Engineers_rally table is already given with the data of Engineers table. Now through the nested query, we are going to update the salary section by 2 times in the Engineers table for all those engineers whose age is greater than 27.

QUERY: Nested query to update the data from one table to another

SQL> UPDATE Engineers
       SET SALARY= SALARY * 2
       WHERE AGE IN (SELECT AGE FROM Engineers_rally)
       WHERE AGE >= 27);

The result is,

ID	Name	Age	Address	Salary
1	Arun	34	Kanpur	30,000
2	Kamal	23	Lucknow	34,000
3	Ajay	32	Mumbai	25,000
4	Shubham	28	Delhi	26,000
5	Anurag	26	Bangalore	24,000
6	Shivam	27	Hyderabad	23,000
7	karan	24	Noida	32,000
8	Himanshu	33	Chennai	20,000

Nested queries with the DELETE statement

The Nested query can be used with the DELETE statement in conjunction as the way it was used above with other statements.

Example: Suppose the Engineers_rally table is already given with the data of Engineers table. Now through the nested query, we are going to delete the records from the Engineers table of those engineers whose age is greater than 27.

Nested query to delete the data from the table,

SQL> DELETE FROM Engineers
       WHERE AGE IN (SELECT AGE FROM Engineers_rally
       WHERE AGE >= 27);

The result is,

ID	Name	Age	Address	Salary
2	Kamal	23	Lucknow	34,000
5	Anurag	26	Bangalore	24,000
7	karan	24	Noida	32,000

Differences Between Join operations and Nested Queries

Now, the differences which we got from the above data between join operations and nested queries are:

Join operations	Nested queries
Join operations are better in optimization.	Nested queries are not better in optimization.
Join operations takes more time because they fetch whole table data with attributes.	Nested queries take less time because they fetch only relevant data from the tables.
Join operations return index data, So on larger dataset working on them is faster.	Nested queries return set of data with no cache facility, So on larger dataset working on them is slower.
Joins operations are not easier to read, understand and evaluate.	Nested queries are easier to read, understand and evaluate.
Join operations can be used in return rows.	Nested queries can be used to return either a scalar value or row set.
Join operations are powerful relational operators	Nested queries are not powerful relational operators.