SQL Outer Join

Types of OUTER JOIN in SQL

The sub-parts of Outer Join are as follows:

• Left Outer Join.
   
• Right Outer Join.
   
• Full Outer Join.
   

Left Outer Join

In the left outer Join, you will get data in the following manner:

• Common values from both tables.
   
• The remaining Values are from the left table.
   
• NULL value from the right table(except common).
  
  The visual representation of Left Outer Join is as shown below:

Syntax

The Syntax for Left Outer Join in DBMS is as shown below:

SELECT column-name-list 
FROM table_1 LEFT JOIN table_2
ON table_1.column = table_2.column;

Here is an example for you to understand the Left Outer Join:

 

You can write the query as follows:

SELECT user.name, courses.courses 
FROM user LEFT JOIN courses
ON user.id = courses.user_id;

 

Right Outer Join

In the right outer Join, you will get data in the following manner:

• Common values from both tables.
   
• NULL value from the left table(except common).
   
• The remaining values are from the right table.
  
  The visual representation of Right Outer Join in DBMS is as shown below:

Syntax

The Syntax for Right Outer Join in DBMS is as shown below:

SELECT column-name-list 
FROM table_1 RIGHT JOIN table_2
ON table_1.column = table_2.column

Here is an example for you to understand the Right Outer Join:

You can write the query as follows:

SELECT user.name, courses.courses 
FROM user RIGHT JOIN courses
ON user.id = courses.user_id;

Full Outer Join

In Full Outer Join, you will get data in the following manner:

• Common values from both tables.
   
• The remaining values are from both the left and right tables.

The visual representation of Full Outer Join in DBMS is as shown below:

Syntax

The Syntax for Full Outer Join in DBMS is as shown below:

SELECT column-name-list 
FROM table_1 FULL JOIN table_2
ON table_1.column = table_2.column;
WHERE condition;

Here is an example for you to understand the Full Outer Join:

You can write the query as follows:

SELECT user.name, courses.courses 
FROM user LEFT JOIN courses
ON user.id = courses.user_id;

UNION 

SELECT user.name, courses.courses 
FROM user RIGHT JOIN courses
ON user.id = courses.user_id;

 

Must Recommended Topic, Schema in DBMS

Implementation of an Outer Join with an Example 

To perform an Outer Join, You should perform the following steps:

Step 1: Create Table One.

CREATE TABLE user (
 id INTEGER,
 name varchar(100),
);

 

Step 2: Add values to table One.

INSERT INTO user VALUES (1, 'Ninja A');
INSERT INTO user VALUES (2, 'Ninja B');
INSERT INTO user VALUES (3, 'Ninja C');
INSERT INTO user VALUES (4, 'Ninja D');
INSERT INTO user VALUES (5, 'Ninja E');

 

Step 3: Create the second table.

CREATE TABLE courses(
user_ id INTEGER,
 courses varchar(100),
);

 

Step 4: Add the values in the second table.

INSERT INTO courses VALUES (3, 'DBMS');
INSERT INTO courses VALUES (1, 'CN');
INSERT INTO courses VALUES (1, 'DSA');
INSERT INTO courses VALUES (6, 'JAVA');
INSERT INTO courses VALUES (4, 'SQL');

Step 5: Write the Query to perform an Outer Join. We are using the Union method to implement the full outer join.

-- Full Outer Join(Using Union)
SELECT user.name, courses.courses FROM 
user LEFT JOIN courses 
ON user.id = courses.user_id 
UNION 
SELECT user.name, courses.courses FROM 
user RIGHT JOIN courses 
ON user.id = courses.user_id;

 

Here is the Complete Code:

-- Table 1
CREATE TABLE user (
 id INTEGER,
 name varchar(100),
);
-- inserting values in the user table.
INSERT INTO user VALUES (1, 'Ninja A');
INSERT INTO user VALUES (2, 'Ninja B');
INSERT INTO user VALUES (3, 'Ninja C');
INSERT INTO user VALUES (4, 'Ninja D');
INSERT INTO user VALUES (5, 'Ninja E');

-- Table 2
CREATE TABLE courses(
user_ id INTEGER,
 courses varchar(100),
);
-- inserting values in the courses table.
INSERT INTO courses VALUES (3, 'DBMS');
INSERT INTO courses VALUES (1, 'CN');
INSERT INTO courses VALUES (1, 'DSA');
INSERT INTO courses VALUES (6, 'JAVA');
INSERT INTO courses VALUES (4, 'SQL');

-- Full Outer Join(Using Union)
SELECT user.name, courses.courses FROM 
user LEFT JOIN courses 
ON user.id = courses.user_id 

UNION 

SELECT user.name, courses.courses FROM 
user RIGHT JOIN courses 
ON user.id = courses.user_id;

The Output of the above code:

Why Use an OUTER JOIN in SQL?

An OUTER JOIN in SQL is used to retrieve records from two or more tables based on a related column, including unmatched rows from one or both tables. It's useful when you want to retrieve all records from one table, even if there are no matching records in the other table(s). OUTER JOINs are particularly helpful in scenarios where you need to combine data from multiple tables while preserving all rows from one or both tables, regardless of matches. This helps in comprehensive data analysis, reporting, and ensuring data integrity in relational database systems.

Also Read - Cardinality In DBMS

Difference between Inner Join and Outer Join in SQL

Below is the comparison table between outer join and inner join in SQL.

Outer Join	Inner Joins
Outer join returns all the records from the tables.	Inner join only returns the common and matching records between the tables.
Outer join is a union of tables.	Inner join is an intersection of tables.
An outer join can be left join, right join, or full join.	There is no variation in the case of inner join.
The SQL outer join is fast compared to the inner join.	Inner joins are slower.
There is no requirement to use an outer join.	In order to use the inner join, you need to apply some constraints.
Outer joins provides higher performance compared to inner joins.	Inner joins have lower performance.
Outer join includes NULL values for non-matching rows.	Inner join does not include NULL values for non-matching rows.
Outer join is used when you want complete information.	Inner join is used when you want information about any specific attribute.
The FULL JOIN and FULL OUTER JOIN clauses work exactly the same.	The INNER JOIN and JOIN clauses work the same.

Frequently Asked Questions

What is a outer join in SQL?

An outer join in SQL retrieves records from two or more tables based on related columns, including unmatched rows from one or both tables.

What is inner join and outer join in DBMS?

In DBMS, an inner join retrieves records with matching values, while an outer join includes unmatched rows from one or both tables.

What is the outer join also called?

The outer join is also known as a full outer join. The term "full outer join" in SQL refers to an outer join that includes all rows from both tables being joined, whether or not they have matching rows in the other table. 

What is outer join types?

Outer join types include left outer join, right outer join, and full outer join, each retrieving different combinations of matched and unmatched rows from the tables involved.

Conclusion

In this article, we have discussed the SQL Outer Join. The outer join in SQL is a powerful tool for retrieving data from multiple tables based on related columns while preserving unmatched rows from one or both tables. Its versatility enables comprehensive data analysis, reporting, and ensures data integrity in relational databases. 

We hope this blog has helped you. We recommend you visit our articles on different topics of DBMS, such as

• DBMS Architecture.
• DBMS Storage System.
• SQL vs MYSQL.
• LIMIT in SQL
   

You can refer to our Guided Path on Coding Ninjas Studio to upskill yourself in Data Structures and Algorithms, Competitive Programming, System Design, and many more!

Start practicing SQL Problems to know frequently asked interview questions and land your dream job.

Also, check out some other topics such as,Operating Systems, Computer Networks, DBMS, etc. as well as some Contests and Test Series only on Coding Ninjas Studio. Enrol in our courses and refer to the mock test and problems available. Take a look at the interview experiences and interview bundle for placement preparations. 
 

Happy Reading!!