Today's Offer - SQL Server Certification Training - Enroll at Flat 10% Off.

- SQL Server Blogs -

What Is The Difference Between The SQL Inner Join And Outer Joins?

Today SQL has become necessary to manage web and network-based projects. The database is associated with almost every project. Relational database management systems are the most important for every organization and the selection of the right database can ensure the quality application performance. Microsoft SQL is chosen by several developers and users to manage the database operations. SQL is one of the well-known and most used query languages by the database professionals. There are a few operations that can be performed on SQL tables like Create, Select, Delete, Update and others. One of the most important operations for SQL tables is Join and here in this blog post, we will see the definition and examples of joins and its subtypes, inner and outer joins and the difference between both joins.

Basic Types of SQL Joins

A SQL Join is a Structured Query Language instruction that is used to merge data from two different data sources or tables. Before we discuss SQL Joins and its types, let us know why the what is the significance of SQL joins? SQL is a special and structured programming language that is used to manage information in RDBMS or relational database management system. Here the relational word is the key and base of RDBMS that specifies the database management system is organized in the way so that clear relation can be defined between various datasets. SQL Server Curriculum In case of RDBMS you need to extract, load and transform data into RDBMS, so that it can be managed through SQL. For this purpose, there are several queries in SQL. One of the queries that we will discuss in this blog is SQL join that can be of following types:

  • Inner Join
  • Outer Join

Let us discuss Inner and Outer joins one by one:

Definition of Inner Join

SQL inner join is used to return the result by combining rows from two or more table. Technically, Inner Join combines all rows from a table with that of another table. So, if the first table has three rows and second table four rows then the final table will have 3 x 4 =12 rows. Joins are used to limit the row combinations here usually the rows are limited for the combination. They are joined or combined as per their column combination.  Inner join can be represented through vein diagram as in below figure: Inner Join Like if we have two tables, one is product price table and another product quantity, then the common column of both the tables will be Product ID or Product Name, so logically the tables will be joined based on this column. In both the tables, some products will be common, and others may not. Inner join performed on both of these tables will only return the common products of both the tables.

PRODUCT PRICE TABLE PRICES

Read: How to Compare MongoDB and DynamoDB?
 PRODUCT  PRICE
Potato $4
Kiwis $3
Melons $2
Oranges $5
Tomatoes $4
Avocado $6

PRODUCT QUANTITY TABLE QUANTITIES

PRODUCT QUANTITY
 Potato 221
Broccoli 23
Squash 55
Melon 45
Kiwi 67
Avocado 87

Query: SELECT PRICES.*, QUANTITIES.QUANTITY FROM PRICE INNER JOIN QUANTITIES ON PRICE.PRODUCT=QUANTITIES.PRODUCT; Resulting Table from Above Query  

PRODUCT PRICE QUANTITY
     
POTATO $4 22
KIWI $3 67
MELON $2 45
AVOCADO $6 87

Outer Join

Unlike Inner Join, Outer Join returns the rows that of an inner join, including leftover rows that are not common in both the tables. So, the number of rows in outer join result is different and more than that of Inner Join. An outer join can further be divided into three types:

  1. Left Outer Join
  2. Right Outer Join
  3. Full Outer Join

Each of these outer joins differs on the basis their way to compare, combine and return the table rows. Null values are also part of these joins as sometimes no values are present in the tables for that column.

Left Outer Join

In the case of left outer join all data of the table on the left side is returned as the result of the join operation, and from the right side or second table, only corresponding data is returned. This can be shown through following Venn diagram. SQL Server quiz You can combine data from one or more tables by using SQL join operation. In SQL join operation is performed to compare and combine or we can say join two or more tables and as a result, a new table with some specific rows is returned. A SQL inner join operation returns matching data from the compared tables, while an outer join finds and returns matching and non-matching or dissimilar data from the tables for which it is performed. LEFT OUTER JOIN Here for the above product tables in the left outer join operations will be performed on these tables   through the following query: SELECT PRICES. *, QUANTITIES.QUANTITY FROM PRICES LEFT OUTER JOIN QUANTITIES ON PRICES.PRODUCT=QUANTITIES.PRODUCT The result of the above query will be below table:  

Read: SQL Server Developer & Database Administrator Salary Structure
PRODUCT  PRICE QUANTITY
Potato $4 22
Kiwis $3 67
Melons $2 NULL
Oranges $5 NULL
Tomatoes $4 NULL
Avocado $6 87

Right Outer Join

SQL Right Outer Join returns all data of the right side table. Means all the data of the second table is included in the query result, while that of left side table only corresponding data is returned. It can be shown through following Venn diagram:Right Outer Join In case of right outer join query, the query result includes all the rows of that of inner join and all remaining rows of a right-side table that were not having the matching entry from the left side table.

Query: SELECT PRICES.*, QUANTITIES.QUANTITY FROM PRICES RIGHT OUTER JOIN QUANTITIES ON PRICES.PRODUCT=QUANTITIES.PRODUCT;

PRODUCT  PRICE QUANTITY
 Potato $4 22
Broccoli NULL 23
Squash NULL 55
Melon $2 45
Kiwi $3 67
Avocado $6 87

FULL OUTER JOIN

A full outer join is a join that is not supported by MySQL database, but in this type of join the data from both the tables get combined, regardless whether the tables have common data or not. In the case of full join, the resulting table may have duplicate data as well in the resulting table and more nulls may be produced in this table as well. FULL OUTER JOIN Full Join Query

SELECT PRICES.*, QUANTITIES.QUANTITY FROM PRICES FULL OUTER JOIN QUANTITIES ON PRICES.PRODUCT=QUANTITIES.PRODUCT; The resulting table will be as

 Prices.PRODUCT  PRICE Quantities.PRODUCT QUANTITY
Potato $4  Potato 221
Kiwis $3 Broccoli 23
Melons $2 Squash 55
Oranges $5 Melon 45
Tomatoes $4 Kiwi 67
Avocado $6 Avocado 87

These are just basics of SQL joins, they can be used in many different cases and situations to resolve the problems. Many join queries are used by the database programmers. You can also try and test the queries on your own so that it can be done smoothly. Even the SQL join query can help the user in extracting data in their desired format and they can perform it to access table records by eliminating and duplicate data.

Read: How to Add A New Column to a Table in SQL?

Difference Between Inner and Outer Joins

Both the Inner and Outer Joins are used to combine data from two or more tables and get the single table result. Here the join condition is being used to perform the query that specifies the way in which the data from each table is matched to another table. Here the aim of performing a join on two database tables is to match the table data and extract the common information from both. For match condition both inner and outer joins compare and match the condition, in the same way, however, they differ when the match condition is not successful. free SQL Server demo Where in case of inner join only matched values are included in the resulting table, so in case of outer join that is of three types, NULL values are also the part of resulting table. Inner joins are also known as natural joins and by default inner join is performed on the table data. The differences between both can be understood through the following table:

Inner and Outer Join Comparison Chart

Comparison Property Inner Join Outer Join
Basic Inner join outputs only matching tuples from both the tables Outer join displays all tuples of both the tables
Table Size The size of the resulting database table is quite smaller than outer join resulting table size Outer join returns larger size tables
Sub-Types It has no sub type It has again three types Left Outer Join, Right Outer Join, Full Outer Join

Here it can be said that Inner and Outer joins can be performed on similar tables. The user can easily get the desired data. Inner and Outer joins are used by SQL users just to compare the table tuples and get either similar data or the best one.

Conclusion

Joins are one of the most used queries by database professionals. Joins returns the table data in the way in which they are used, like in case of inner joins the user can get only common values from both the tables, while in case of outer joins all data from both the tables are included in resulting tables. Inner and outer joins both can combine the tables, but still, they are different. Inner join resulting tables are smaller in size, while the tables of an outer join are quite larger. So as per user requirement, they can use any of the tables.

Read: SSIS Tutorial for Beginners

SQL Tutorial Overview


    Janbask Training

    JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.


Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

4 days 24 Nov 2019

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

5 days 25 Nov 2019

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

5 days 25 Nov 2019

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

6 days 26 Nov 2019

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

14 days 04 Dec 2019

Course for testing

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

34 days 24 Dec 2019

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

13 days 03 Dec 2019

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

5 days 25 Nov 2019

SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

8 days 28 Nov 2019

Comments

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews