in Education by
Difference between inner and outer join. i am using two table and want to fetch data from both table so which type join we should use owning of that we can solve our problem Select the correct answer from above options

1 Answer

0 votes
by
 
Best answer
Let’s understand what exactly is “INNER JOIN” and “OUTER JOIN”, then we’ll gradually understand the differences between them: INNER JOIN Definition: Inner Join is also known as Natural Join. The result-set of an Inner Join is the commonality between the two or more tables. So, Inner Join always compares two or more tables and combines the matching rows or tuples in the combined tables. It’s also referred to as the default type of join because we write join clause without using any inner keyword and it performs Natural Join. So, if you write Join clause without mentioning Outer Keyword then also it’ll perform the inner join. Have a look at this video to understand the INNER JOIN in detail. Example: There are two tables Table A: Employee Table and Table B: Department Table. Now let’s understand what is the result after performing Inner Join on these Table. Employee_ID Name Department_ID Salary 5110 Joseph HR 30,000 5111 Harry MN 45,000 5112 Scarlet OP 27,000 5113 Peter CC 30,000 5114 Haely TC 35,000 5115 James HR 33,000 5116 Roobina MN 47,000 5117 Lily OP 29,000 Table A: Employee Department_ID Department_name HR Human Resource MN Management OP Operation TC Technical SC Sales Table B: Department Query: SELECT Name, Salary, Department_name FROM Employee INNER JOIN Department ON Employee.Department_ID= Department.ID. Name Salary Department_name Joseph 30,000 Human Resource Harry 45,000 Management Scarlet 25,000 Operation James 33,000 Human Resource Haley 35,000 Technical Roobina 47,000 Management Lily 29,000 Operation Result of Inner Join. Outer Join Definition: In Outer Join, only those rows are given as output that has the same attribute values in both the tables which are getting compared. The result of Outer Join is all the tuples of both the table. Outer Join is of three following types: 1. Left Outer Join 2. Right Outer Join 3. Full Outer Join. Let us understand the types of Outer Join one by one. 1. Left Outer Join: This keyword returns all the records from the left table (table1) and the matched records from the right table (table2). If there is no match, then the result is NULL from the right side in the resultant table. Head over to this video to understand LEFT OUTER JOIN in detail. Let’s consider the above Employees and Department Table for example to understand the resultset of the LEFT OUTER JOIN: Query: Select Name, Department_name From Employee Left Outer Join Department ON Employee.Department_ID=Depoartment.ID. Left Outer Join’s Result: All the tuples from Employee Table are displayed in the result. Name Department_name Joseph Human Resource Harry Management Scarlet Operation Peter Null Haely Technical James Human Resource Roobina Management Lily Operation 2. Right Outer Join: This keyword returns all records from the right table (table2), and the matched records from the left table (table1). If there is no match, the result is NULL from the left side. For more information you can refer to this video: Let’s consider the above Employees and Department Table for example to understand the resultset of RIGHT OUTER JOIN: Query: Select Name, Department_name From Department Right Outer Join Student ON Employee.Department_ID=Depoartment.ID. Right Outer Join’s Result: All the rows from the Department table are displayed. Name Department_name Joseph Human Resource Harry Management Scarlet Operation Haely Technical James Human Resource Roobina Management Lily Operation Null Sales 3. Full Outer Join: This keyword returns all the records when there is a match in either left (table1) or right (table2) table rows (records). You can also refer to this video for detailed information about Full Outer Join: Let’s consider the above Employees and Department Table for example to understand the resultset of the FULL OUTER JOIN: Query: Select Name, Department_name From Employee Full Outer Join Department ON Employee.Department_ID=Depoartment.ID. Full Outer Join’s Result: All the tuples from both the tables are included in the result. Name Department_name Joseph Human Resource Harry Management Scarlet Operation Peter Null Haely Technical James Human Resource Roobina Management Lily Operation Null Sales Now, let’s compare the Inner Join and Outer Join Based on some parameters: Comparison Inner Join Outer Join DB(Database) The size of the database returned by Inner Join is smaller than the Outer Join This returns a comparatively larger database. Types No Type Left Outer Join, Right Outer Join and Full Outer Join Fundamental Inner Join returns only the matching rows from both the table. This returns all the rows from both the tables.

Related questions

0 votes
    Difference between inner and outer join. i am using two table and want to fetch data from both table so which ... solve our problem. Select the correct answer from above options...
asked Jan 26, 2022 in Education by JackTerrance
0 votes
    Both these joins will give me the same results: SELECT * FROM table JOIN otherTable ON table.ID = ... different SQL implementations? Select the correct answer from above options...
asked Jan 28, 2022 in Education by JackTerrance
0 votes
    Here are two data frames : df1 = data.frame(CId = a(1:6), Item = b(rep("TV", 3), rep("Book", 3 ... I do a SQL style select statement Select the correct answer from above options...
asked Jan 20, 2022 in Education by JackTerrance
0 votes
    I have two tables containing Tasks and Notes, and want to retrieve a list of tasks with the number ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Mar 12, 2022 in Education by JackTerrance
0 votes
    What is the difference between the EXISTS and IN clause in SQL? When should we use EXISTS, and when should we use IN? Select the correct answer from above options...
asked Feb 8, 2022 in Education by JackTerrance
0 votes
    What is the difference between the EXISTS and IN clause in SQL? When should we use EXISTS, and when should we use IN? Select the correct answer from above options...
asked Feb 5, 2022 in Education by JackTerrance
0 votes
    What is the difference between UNION and UNION ALL? Select the correct answer from above options...
asked Jan 26, 2022 in Education by JackTerrance
0 votes
    What is the difference between DELETE and TRUNCATE statements?...
asked Dec 11, 2020 by JackTerrance
0 votes
    Find the magnetic field intensity at the radius of 6cm of a coaxial cable with inner and ... proposed by,electromagnetic theory engineering physics,electromagnetic theory nptel...
asked Nov 11, 2021 in Education by JackTerrance
0 votes
    In Subexpressions, the results of outer helpers will be passed as arguments to the inner helpers. (1)True (2)False...
asked Nov 30, 2020 in Technology by JackTerrance
0 votes
    Lets say I have this following table: +------+-------------------------+-- ... JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Apr 2, 2022 in Education by JackTerrance
0 votes
    I have a table in Db2 called myTable. It has several columns: a | b | date1 | date2 ----- ... , JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Jun 14, 2022 in Education by JackTerrance
0 votes
    I have a table in Db2 called myTable. It has several columns: a | b | date1 | date2 ----- ... , JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Jun 14, 2022 in Education by JackTerrance
...