Home » DBMS

Join operation vs nested query | DBMS

Join operation vs nested query: Here, we are going to learn about the join operations and nested queries in details, differences between join operations and nested queries in the database management system (DBMS).
Submitted by Anushree Goswami, on August 10, 2019

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.NameSection
202Anubhav verma2B
203Smita singh3B

Project:

Standard Projects
2BC++
2BPython
3BJava
3BAdobe

After combining them through Equi Join, we get

Employee Details:

Emp.I.d.NameSectionStandardProjects
202Anubhav verma2B2BC++
202Anubhav verma2B2BPython
203Smita singh3B3BJava
203Smita singh3B3BAdobe


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.NameClass
4Aman10
7Raghav11
8Sameer12

Subject:

Class Subjects
10Maths
11Science
12Biology

The Resulting table is:

Student ⋈ Subjects

Roll no. Name Class Subjects
4Aman10Maths
7Raghav11Science
8Sameer12Biology

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:

AB
203Taj Palace
205Royal plaza
207Hotel Villas

Right Side Table:

AB
203Karan
204Raghubir
205Satyam

Resultant Table:

ABCD
203Taj Palace203Karan
205Royal plaza205Satyam
207Hotel 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:

AB
203Taj Palace
205Royal plaza
207Hotel Villas

Right Side Table:

AB
203Karan
204Raghubir
205Satyam

Resultant Table:

ABCD
203Taj Palace203Karan
------204Raghubir
205Taj Palace205Satyam

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:

AB
203Taj Palace
205Royal plaza
207Hotel Villas

Right Side Table:

AB
203Karan
204Raghubir
205Satyam

Resultant Table:

ABCD
203Taj Palace203Karan
------204Raghubir
205Taj Palace205Satyam
207Hotel 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,

IDNameAgeAddressSalary
1Arun34Kanpur30,000
2Kamal23Lucknow34,000
3Ajay32Mumbai25,000
4Shubham28Delhi26,000
5Anurag26Bangalore24,000
6Shivam27Hyderabad23,000
7karan24Noida32,000
8Himanshu33Chennai20,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,

IDNameAgeAddressSalary
1Arun34Kanpur30,000
2Kamal23Lucknow34,000
7karan24Noida32,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,

IDNameAgeAddressSalary
1Arun34Kanpur30,000
2Kamal23Lucknow34,000
3Ajay32Mumbai25,000
4Shubham28Delhi26,000
5Anurag26Bangalore24,000
6Shivam27Hyderabad23,000
7karan24Noida32,000
8Himanshu33Chennai20,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,

IDNameAgeAddressSalary
2Kamal23Lucknow34,000
5Anurag26Bangalore24,000
7karan24Noida32,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.






Comments and Discussions

Ad: Are you a blogger? Join our Blogging forum.




Languages: » C » C++ » C++ STL » Java » Data Structure » C#.Net » Android » Kotlin » SQL
Web Technologies: » PHP » Python » JavaScript » CSS » Ajax » Node.js » Web programming/HTML
Solved programs: » C » C++ » DS » Java » C#
Aptitude que. & ans.: » C » C++ » Java » DBMS
Interview que. & ans.: » C » Embedded C » Java » SEO » HR
CS Subjects: » CS Basics » O.S. » Networks » DBMS » Embedded Systems » Cloud Computing
» Machine learning » CS Organizations » Linux » DOS
More: » Articles » Puzzles » News/Updates


© https://www.includehelp.com some rights reserved.