Open In App

Inner Join vs Outer Join

Last Updated : 04 Sep, 2024
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Save
Share
Report
News Follow

Inner Join and Outer Join are the types of join. The inner join has the work to return the common rows between the two tables, whereas the Outer Join has the work of returning the work of the inner join in addition to the rows that are not matched. 

Let’s discuss both of them in detail in this article. Before moving ahead, let’s discuss what is Join in SQL.

In a relational database management system (RDBMS), there are different types of joins that can be used to combine data from two or more tables in a database. The two most common types of joins are Inner Join and Outer Join.

What is Join in SQL?

SQL Joins are simply a way to combine data from two or more tables based on a common field between them. SQL joins are of two types. We will discuss the types of SQL in detail.

Let’s proceed with an example demonstrating the process of Inner Join and Outer Join.

Student Table

EnrollNo StudentName Address
1001 geek1 geeksquiz1
1002 geek2 geeksquiz2
1003 geek3 geeksquiz3
1004 geek4 geeksquiz4

StudentCourse Table

CourseID EnrollNo
1 1001
2 1001
3 1001
1 1002
2 1003

Inner Join/Simple Join

In an INNER join, it allows retrieving data from two tables with the same ID.

An Inner Join returns only the matching rows between the two tables based on a specified condition. It combines data from two tables based on a common column between them, which is specified using the ON keyword in SQL. Only the rows that meet the join condition from both tables are returned. If a row in one table does not have a matching row in the other table, that row will not be included in the result set.

Inner Join

Inner Join

Syntax:

SELECT COLUMN1, COLUMN2 FROM

 [TABLE 1] INNER JOIN [TABLE 2] 

ON Condition;

The following is a join query that shows the names of students enrolled in different courses.

Query:

SELECT StudentCourse.CourseID,Student.StudentName
FROM Student
INNER JOIN StudentCourse
ON StudentCourse.EnrollNo = Student.EnrollNo
ORDER BY StudentCourse.CourseID;

Note: Inner is optional above. Simple JOIN is also considered as INNER JOIN The above query would produce the following result.

CourseID StudentName
1 geek1
1 geek2
2 geek1
2 geek3
3 geek1

Example:

For example, let’s say we have two tables, Table1 and Table2, with the following data:

Table 1

ID Name
1 John
2 Sarah
3 David

Table 2

ID Address
1 123 Main St.
2 456 Elm St.
4 789 Oak St.

If we perform an Inner Join on these tables using the ID column, the result set would only include the matching rows from both tables, which are the rows with ID values of 1 and 2:

Query:

SELECT Table1.ID, 
Table 1. Name, Table 2.Address
FROM Table1
INNER JOIN Table2
ON Table1.ID = Table2.ID

Output:

ID Name Address
1 John 123 Main St.
2 Sarah 456 Elm St.

How To Use Inner Join?

Inner Join is basically performed by just selecting the records having the common values or the matching values in both tables. In case of no common values, no data is shown in the output.

Syntax:

Select Table1.Col_Name, Table2.Col_Name....
From Table1
Inner Join Table2
on Table1.Common_Col = Table2.Common_Col;

If there are 3 Tables present in the database, then the Inner Join works as follows:

Select Table1.Col_Name, Table2.Col_Name, Table3.Col_Name....
From ((Table1
Inner Join Table2
on Table1.Common_Col = Table2.Common_Col)
Inner Join Table3
on Table1.Common_Col = Table3.Common_Col);

Advantages of Inner Join

  • Reduced Data Duplication: Inner joins only return rows that have matching values in both tables being joined, which can reduce the amount of duplicate data returned in the result set.
  • Efficient Query Execution: Since inner joins only involve rows that match in both tables, they can be more efficient in terms of query execution time compared to outer joins.
  • Data Accuracy: Inner joins only return rows that have matching values in both tables, which can improve data accuracy by excluding irrelevant or mismatched data.

Disadvantages of Inner Join

  • Data Loss: Since inner joins only return rows that have matching values in both tables, some data may be lost if there are no matching values.
  • Query Complexity: Inner joins can become complex and difficult to write and understand when working with multiple tables.
  • Overlapping Data: In some cases, inner joins may return overlapping data that needs not to be duplicated in post-processing.

Outer Join

An Outer Join returns all the rows from one table and matching rows from the other table based on a specified condition. It combines data from two tables based on a common column between them, which is also specified using the ON keyword in SQL. In addition to the matching rows, it also includes rows from one table that do not have matching rows in the other table.

Outer Join is of three types:

  1. Left outer join 
  2. Right outer join 
  3. Full Join

1. Left Outer join 

Left Outer Join returns all rows of a table on the left side of the join. For the rows for which there is no matching row on the right side, the result contains NULL on the right side.

Left Outer Join returns all the rows from the left table and matching rows from the right table. If a row in the left table does not have a matching row in the right table, the result set will include NULL values for the columns in the right table.

Left Outer Join

Left Outer Join

Syntax:

SELECT  T1.C1, T2.C2

 FROM TABLE T1 

LEFT JOIN TABLE T2 

ON T1.C1= T2.C1;

Query:

SELECT Student.StudentName,StudentCourse.CourseID
FROM Student
LEFT OUTER JOIN StudentCourse
ON StudentCourse.EnrollNo = Student.EnrollNo
ORDER BY StudentCourse.CourseID;

Note: OUTER is optional above. Simple LEFT JOIN is also considered as LEFT OUTER JOIN

StudentName CourseID
geek4 NULL
geek2 1
geek1 1
geek1 2
geek3 2
geek1 3

2. Right Outer Join 

Right Outer Join is similar to Left Outer Join (Right replaces Left everywhere). Right Outer Join returns all the rows from the right table and matching rows from the left table. If a row in the right table does not have a matching row in the left table, the result set will include NULL values for the columns in the left table.

Right Outer Join

Right Outer Join

Syntax:

SELECT T1.C1, T2.C2

 FROM TABLE T1 

RIGHT JOIN TABLE T2 

ON T1.C1= T2.C1;

Example:

Table Record

Roll_Number Name Age
1 Harsh 18
2 Ankesh 19
3 Rupesh 18
4 Vaibhav 15
5 Naveen 13
6 Shubham 15
7 Ankit 19
8 Bhupesh 18

Table Course

Course_ID Roll_Number
1 1
2 2
2 3
3 4
1 5
4 9
5 10
4 11

Query:

SELECT Record.NAME,Course.COURSE_ID 
FROM Record
RIGHT JOIN Course
ON Course.Roll_Number = Record.Roll_Number;

Output:

Name Course_ID
Harsh 1
Ankesh 2
Rupesh 2
Vaibhav 3
Naveen 1
NULL 4
NULL 5
NULL 4

3. Full Outer Join 

Full Outer Join contains the results of both the Left and Right outer joins. It is also known as cross-join. It will provide a mixture of two tables. 

Full Outer Join returns all the rows from both tables, including matching and non-matching rows. If a row in one table does not have a matching row in the other table, the result set will include NULL values for the columns in the table that do not have a match.

Full Outer Join

Full Outer Join

Syntax:

SELECT * FROM T1 

CROSS-JOIN T2;

Query:

SELECT Record.NAME,Course.COURSE_ID 
FROM Record
FULL JOIN Course
ON Course.Roll_Number = Record.Roll_Number;

Output:

Name Course_ID
Harsh 1
Ankesh 2
Rupesh 2
Vaibhav 3
Naveen 1
Shubham NULL
Ankit NULL
Bhupesh NULL
NULL 4
NULL 5
NULL 4

How To Use Outer Join?

Outer Join is performed by the selection of the records from all the tables given there. Left Outer Join works by selecting all records from the left table and matching records from the right table. Similarly, Right Outer Join works by selecting all records from the right table and matching records from the left table and the Full Outer Join returns all records if a match occurs in the left or the right table.

Advantages of Outer Join

  • Increased Data Retrieval: Outer joins can retrieve more data than inner joins since they include non-matching rows in the result set.
  • Data Integrity: Outer joins can help maintain data integrity by ensuring that all records in the primary table are returned, even if there are no corresponding records in the secondary table.
  • Data Analysis: Outer joins can be useful for data analysis, especially when exploring relationships between data sets and identifying trends and patterns.

Disadvantages of Outer Join

  • Slow Query Execution: Outer joins can be slower to execute than inner joins, especially when dealing with large data sets.
  • Data Duplication: Outer joins can return duplicate data when the secondary table has multiple matching records.
  • Data Quality: Outer joins may return inaccurate or incomplete data if the tables being joined have incomplete or inconsistent data.

Inner Join vs Outer Join (Difference Table)

Feature

Inner Join

Outer Join

Defination

Returns rows with matching values in both tables by filtering out.

It is directly opposite of Inner join returns rows but return all of them including the unmatched also.

Use cases

Used when we want matching data.

when we need all data without concerning about match and unmatched.

Types

Single Type.

Three different Types: Left Outer Join, Right Outer Join, Full Outer Join.

Example

Result

Only the common data between one or more tables.

All data from one or both tables, with giving NULLs for non-matching rows or the unmatched ones.

Conclusion

In SQL, The selection between Inner vs Outer is what are requirements of our scenario including the data we are using in. A inner join is used when we need to use filtering data which have matching values from both tables, ensuring related data must be come up as result. Whereas, Outer Join is used when we need to retain all records from both the tables without considering the matching records overheads. Generally, Inner Joins are faster than the Outer Join as they only retrieve the matching data. Understanding the Inner Join and Outer Join makes a SQL data administrator a robust database manager to retain and retrieve data effectively and quickly.

Frequently Asked Questions on Inner Join vs Outer Join – FAQs

What types of Outer Joins are there?

There are 3 types of Outer Join naming Left outer Join, Right outer Join, & Full outer Join each of them finally returns the unmatched rows from one or both tables.

Can I use both Inner Join and Outer Join in a single query?

Yes, you can use both outer join as well as inner join simultaneously in single query to achieve efficiency in complex data relationships. This is useful only when different parts of your query require different and modified types of joins.

Which is better Inner Join or Outer Join?

Inner Join is used when related data inputs are required while on other hand when we just required all rows data without thinking of the related data requirement.

Does inner join remove duplicates?

The inner join statement combines different row from two different tables which leads to removal of duplication.

In the battle of Performance which is better Inner join or Outer Join?

Inner Joins are generally faster than outer join as they only provide those rows which have matching keys in both tables, while on other `Outer Join` iterate all rows from both the tables including those which are not matching and due of this we get more processing overhead and increased memory usage.

How can I replace an OUTER JOIN if my SQL database doesn’t support it?

If your database doesn’t support the `FULL OUTER JOIN` then you can use the combination of `LEFT OUTER JOIN` and `RIGHT OUTER JOIN` including a `UNION` to combine the results or upcoming output of their result, which leads to final `FULL OUTER JOIN`.



Previous Article
Next Article

Similar Reads

Difference between Inner Join and Outer Join in SQL
JOINS in SQL are fundamental operations used to combine data from multiple tables based on related columns. They are essential for querying data that is distributed across different tables, allowing you to retrieve and present it as a single or similar result set. In this article, We will learn about Inner Join vs Outer Join in SQL in detail. What
5 min read
Difference Between “INNER JOIN” and “OUTER JOIN”
Joins in SQL are essential tools that allow us to combine rows from multiple tables based on specific conditions, often involving a relation between columns in those tables. These joins help in pulling related data together in a meaningful way. Among the most commonly used joins are INNER JOIN and OUTER JOIN. In this article, We will learn about th
5 min read
Difference Between Left Join and Left Outer Join
In SQL language, different joins are used to assemble rows from two or more tables from the related column. The terms "Left Join" and "Left Outer Join" are used interchangeably in SQL but they refer to the same concept. A Left Join retrieves all records from the left table (the first table in the query) and matches them with records from the right
5 min read
Difference Between Right Join and Right Outer Join
Joins in a Database (SQL) are mostly used for combining data or the rows of two or more table records that are based on the same or common attribute. There are various types of Joins like Right Join, Left Join, Full Join, etc. Each join has its own syntax and data-returning capability. In this article, we will see the information about Right Join a
5 min read
Difference between Natural join and Inner Join in SQL
The join operation merges the two tables based on the same attribute name and their datatypes are known as Natural join Unlike INNER JOIN, which requires you to specify the columns and conditions for the join explicitly. In this article, we will also see the differences between them. Let's start with Natural Join. Example: If you have two tables "S
3 min read
Full join and Inner join in MS SQL Server
Full Join Full join selects all the rows from the left and right tables along with the matching rows as well. If there are no matching rows, it will be displayed as NULL. Syntax: select select_list from table1 full join table2 on join _predicate (OR) select table1.*, table2.* from table1 full join table2 on join _predicate (OR) select * from table1
2 min read
SQL Outer Join
In a relational DBMS, we follow the principles of normalization that allows us to minimize the large tables into small tables. By using a select statement in Joins, we can retrieve the big table back. Outer joins are of following three types. Left outer joinRight outer joinFull outer join Creating a database : Run the following command to create a
3 min read
SQL Full Outer Join Using Union Clause
In this article, we will discuss the overview of SQL, and our main focus will be on how to perform Full Outer Join Using Union Clause in SQL. Let's discuss it one by one. Overview :To manage a relational database, SQL is a Structured Query Language to perform operations like creating, maintaining database tables, retrieving information from the dat
3 min read
SQL Full Outer Join Using Where Clause
A SQL join statement is used to combine rows or information from two or more than two tables on the basis of a common attribute or field. There are basically four types of JOINS in SQL. In this article, we will discuss about FULL OUTER JOIN using WHERE clause. Consider the two tables below: Sample Input Table 1 : PURCHASE INFORMATIONProduct_IDMobil
3 min read
Difference between Left, Right and Full Outer Join
When working with SQL and relational databases, joins are essential tools that allow you to combine data from multiple tables based on related columns. Among the various types of joins, Left Join, Right Join, and Full Outer Join are commonly used for different scenarios. Understanding the differences between these joins is crucial for efficient dat
6 min read
SQL | Join (Cartesian Join & Self Join)
SQL| JOIN(Inner, Left, Right and Full Joins) In this article, we will discuss about the remaining two JOINS: CARTESIAN JOIN SELF JOIN Consider the two tables below: StudentCourse CARTESIAN JOIN: The CARTESIAN JOIN is also known as CROSS JOIN. In a CARTESIAN JOIN there is a join for each row of one table to every row of another table. This usually h
2 min read
SQL Inner Join
SQL INNER JOIN combines two or more tables based on specified columns and retrieves records with matching values in the common columns. INNER JOIN IN SQLThe INNER JOIN clause in SQL is used to combine multiple tables and fetch records that have the same values in the common columns. The difference in the use of INNER JOIN vs OUTER JOIN is that INNE
2 min read
SQL Joins (Inner, Left, Right and Full Join)
SQL Join operation combines data or rows from two or more tables based on a common field between them. In this article, we will learn about Joins in SQL, covering JOIN types, syntax, and examples. SQL JOINSQL JOIN clause is used to query and access data from multiple tables by establishing logical relationships between them. It can access data from
5 min read
Difference between Natural join and Cross join in SQL
1. Natural Join : Natural Join joins two tables based on same attribute name and datatypes. The resulting table will contain all the attributes of both the tables but only one copy of each common column. Example: Consider the two tables given below: Student Table Marks Table Consider the given query SELECT * FROM Student S NATURAL JOIN Marks M; Out
2 min read
Left join and Right join in MS SQL Server
Prerequisite – Introduction of MS SQL Server 1. Left Join : A join combines the set of two tables only. A left join is used when a user wants to extract the left table's data only. Left join not only combines the left table's rows but also the rows that match alongside the right table. Syntax - select select_list from table1 left join table2 on joi
2 min read
Difference between Nested Loop join and Sort Merge Join
1. Nested Loop Join : Nested Loop Join is the simplest join algorithm which usually has better performance than all other types of joins due to a lesser number of comparisons involved in it. Each row in the outer table is compared to each row in the inner table. The Nested Loop Join algorithm for 2 relations R and S : For each record x of R read in
3 min read
Difference between Hash Join and Sort Merge Join
1. Hash Join : It is also known as "go-to-guy" in case of join operators. This means that in case no other join is preferred (maybe due to no sorting or indexing etc), then, Hash join is used. Hash join is best algorithm when large, unsorted, and non-indexed data (residing in tables) is to be joined. Hash join algorithm consists of probe phase and
3 min read
Self Join and Cross Join in MS SQL Server
Prerequisite - Introduction of MS SQL Server 1. Self Join : Self-join allows us to join a table itself. It is useful when a user wants to compare the data (rows) within the same table. Syntax - select select_list from T t1 [Inner|Left] Join on T t2 on join_predicate. Here T refers to the table we use for comparison and it is referred twice. To avoi
2 min read
SQL | EQUI Join and NON EQUI JOIN
Types of SQL Joins are explained in left, right, and full join and SQL | Join (Cartesian Join & Self Join). And Remaining EQUI Join and NON-EQUI will discuss in this article. Let's discuss one by one. SQL JOINS : EQUI JoinNON-EQUI Join Example - Let's Consider the two tables given below. Table name — Student In this table, you have I'd, name, c
2 min read
Difference Between Anti-Join and Semi-Join
In the context of SQL, Anti-join, and semi-join are two essential operations in relational databases used for querying and manipulating data. These operations focus on comparing data from two related tables, but they serve distinct purposes. In this article let us discuss these two operations in detail along with some examples. What is SQL Join?Joi
5 min read
Difference Between Left Join and Right Join
In DBMS(Database Management System) Join is an operation that combines the row of two or more tables based on related columns between them. The main purpose of Join is to retrieve the data from multiple tables in other words Join is used to perform multi-table queries. So for that purpose, joins come into the picture. So in this article, we will go
5 min read
Difference Between Nested Loop Join and Hash Join
These join operations are important to the optimization of SQL operations, especially in guaranteed cases concerning database management systems. Mostly where clause conditions can be transformed into Nested Loop Join and Hash Join main two methods of joining two or more data tables on the same attribute. Nested Loop Join is a simple method, usuall
6 min read
Join algorithms in Database
There are two algorithms to compute natural join and conditional join of two relations in database: Nested loop join, and Block nested loop join. To understand these algorithms we will assume there are two relations, relation R and relation S. Relation R has TR tuples and occupies BR blocks. Relation S has TS tuples and occupies BS blocks. We will
4 min read
Difference between JOIN and UNION in SQL
Pre-requisites: JOIN, UNION JOIN in SQL is used to combine data from many tables based on a matched condition between them. The data combined using the JOIN statement results in new columns. Consider the two tables: Boys Girls Example: sql> SELECT Boys.Name, Boys.Age, Girls.Address, FROM Boys INNER JOIN Girls ON Boys.Rollno = Girls.Rollno; The r
2 min read
Join statement in JCL
Prerequisite - Job Control Language (JCL) In RDBMS, SQL JOIN clause is used to join tables and perform multiple operations on it. To perform operations on records in different flat files, JOINKEYS are used. With the help of JOINKEYS in SORT JCL, various join operation on matched and non-matched records can be executed based on matching fields or Ke
3 min read
SQL | UPDATE with JOIN
SQL UPDATE JOIN could be used to update one table using another table and join condition. Syntax - UPDATE tablename INNER JOIN tablename ON tablename.columnname = tablename.columnname SET tablenmae.columnnmae = tablenmae.columnname; Use multiple tables in SQL UPDATE with JOIN statement. Let us assume we have two tables - Geeks1 and Geeks2. To check
2 min read
How to Left Join Multiple Tables in SQL
Left Join is one of the Keywords used while writing queries in SQL. In SQL we normally use Join for the purpose of forming a new table by taking out common data like rows or records or tuples from both the tables which are having matching records in general. Here when it comes to Left Join in SQL it only returns all the records or tuples or rows fr
3 min read
SQL Natural Join
Natural join is an SQL join operation that creates a join on the base of the common columns in the tables. To perform natural join there must be one common attribute(Column) between two tables. Natural join will retrieve from multiple relations. It works in three steps. In this article, we will discuss the overview of SQL Natural Join and then main
3 min read
SQL Query to select Data from Tables Using Join and Where
The aim of this article is to make a simple program to Join two tables using Join and Where clause using MySQL. Below is the method to do the same using MySQL. The prerequisites of this article are MySQL and Apache Server on your computer are installed. What is a Query in SQL? A SQL query is a request passed for data/information from a table in a d
3 min read
Double-Pipelined Join
Double-pipelined join is a type of distributed query processing technique used to join two large datasets stored in a distributed environment. The two datasets are joined using a two-phase pipelined approach, which allows for faster query processing than traditional join algorithms. In this approach, each dataset is split into two separate streams,
4 min read
Article Tags :
three90RightbarBannerImg