OUTER JOIN – step by step walkthrough with examples

The JOIN operation allows you to combine rows from two or more tables based on a related column. OUTER JOIN includes the FULL, RIGHT, and LEFT OUTER JOINS. When two tables are joined there can be NULL values from either table. For INNER JOINs, records with nulls won’t match, and they will be discarded and won’t appear in the result set. If we want to get the records that don’t match, then we can use OUTER JOIN. These Joins are capable of matching records from both tables based on our needs and will return all records relevant to the type of join that we use. It extracts match rows along with unmatched rows as well from one or both of the tables. 

• INNER JOIN: Returns rows when there is a match in both tables.
• LEFT JOIN: Returns all rows from the left table, and matched rows from the right table. NULL if no match.
• RIGHT JOIN: Returns all rows from the right table, and matched rows from the left table. NULL if no match.
• FULL JOIN: Returns all rows when there is a match in either table. NULLs where there is no match.

Inner Join vs Outer Join

OUTER JOINS differ from traditional INNER JOINS in their ability to return all rows from one table, regardless of whether they have matching counterparts in the other table. This feature makes OUTER JOINS invaluable for scenarios where complete information is desired, even if there are no exact matches between the tables.

SELECT ColumnList 
FROM EmployeeName Table L
(LEFT/RIGHT/FULL) OUTER JOIN Position Table R
ON L.Column=R.Column 

• All records that match the join condition or predicate. That’s the expression right after the ON keyword, much like the INNER JOIN output.
• Non-NULL values are from the left table, and the null counterparts are from the right table. Non-NULL values are from the right table, and the null counterparts are from the left table.
• Finally, it could be a combination of all things described above.

Features of JOINS

Feature	Inner Join	Left Outer Join	Right Outer Join	Full Outer Join
Purpose	Returns rows where the join condition is met in both tables.	Returns all rows from the left table, even if there is no matching row in the right table.	Returns all rows from the right table, even if there is no matching row in the left table.	Returns all rows from both tables, regardless of whether there is a matching row in the other table.
Join Condition	WHERE clause	ON clause	ON clause	ON clause
Matching Rows	Rows with matching values in the join columns are included in the result.	Rows with matching values in the join columns are included in the result. Rows from the left table with no matching values in the right table are included in the result, but with NULL values in the right table columns.	Rows with matching values in the join columns are included in the result. Rows from the right table with no matching values in the left table are included in the result, but with NULL values in the left table columns.	All rows from both tables are included in the result, regardless of whether there is a matching row in the other table. Rows with no matching values have NULL values in the corresponding columns.
Result Size	Smallest of the two tables	Same size as the left table	Same size as the right table	Largest possible size (sum of both tables)
Example	SELECT * FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;	SELECT * FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;	SELECT * FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;	SELECT * FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Additional Notes:

• Inner joins are the most common type of join and are used when you only want to include rows where there is a match in both tables.
• Left outer joins are useful when you want to see all the data from the left table, even if there is no matching row in the right table.
• Right outer joins are useful when you want to see all the data from the right table, even if there is no matching row in the left table.
• Full outer joins are useful when you want to see all the data from both tables, regardless of whether there is a matching row in the other table.
• Optimize Outer Join Queries in the COBOL DB2 Program – Click Here
• OUTER JOIN Queries Common Errors and Resolutions – Click Here

Left Outer Join

At its core, a left outer join is a method for combining rows from two or more tables based on a related column between them. Unlike inner joins, which only return rows that have matching values in both tables, a left outer join returns all rows from the left table (referred to as the “left” or “first” table) and matching rows from the right table (referred to as the “right” or “second” table). If there is no match in the right table, NULL values are returned for the columns from the right table.

Input Data
 EmployeeName Table-(T1)Position Table-(T2)         
  ID     NAME           ID     TITLE         TEAMSIZE    
  10     Sandy          20     Sales Mgr        5
  20     Sam            30     Clerk           10 
  30     Cindy          30     Manager          2
                        40     Sales Rep        7
                        50     Sr. Manager.    11
 
 Query
 SELECT T1.NAME, 
        T1.ID, 
        T2.ID, 
        T2.TITLE,
        T2.TEAMSIZE
 FROM EmployeeName T1
 LEFT OUTER JOIN Position T2 
 ON T1.ID = T2.ID
 ORDER BY T1.ID
        , T2.TITLE; 

 Result         
 ID   NAME     ID     TITLE        TEAMSIZE
 10   Sandy    --     ---------    ---------
 20   Sam      20     Sales Mgr     5
 30   Cindy    30     Clerk        10
 30   Cindy    30     Manager.      2 

Suppose we want to return the employee with no title. To do that, add a WHERE clause to include only rows with nulls from the Position table.

Query using ON or WHERE

 SELECT T1.NAME, 
        T1.ID, 
        T2.ID, 
        T2.TITLE,
        T2.TEAMSIZE
 FROM EmployeeName T1
 LEFT OUTER JOIN Position T2 
 ON T1.ID = T2.ID
 AND T2.TITLE IS NULL
 
 SELECTT1.NAME, 
        T1.ID, 
        T2.ID, 
        T2.TITLE,
        T2.TEAMSIZE
 FROMEmployeeName T1
 LEFT OUTER JOINPosition T2 
 ONT1.ID=T2.ID
 WHERE T2.TITLE IS NULL
 
Both the above queries will give the same result.
Result         
 ID   NAME     ID     TITLE       TEAMSIZE
 10   Sandy    --     ---------   --------- 

Right Outer Join

A right outer join is also known as a right join. It merges rows from two or more tables. This is done based on a related column between them. Unlike inner joins, right joins return all rows from the right table. They also return matching rows from the left table. The right table is referred to as the “right” or “second” table. The left table is referred to as the “left” or “first” table. If there is no match in the left table, NULL values are returned for the columns from the left table.

Input Data
 EmployeeName Table-(T1)Position Table-(T2)         
  ID     NAME           ID     TITLE         TEAM    
  10     Sandy          20     Sales Mgr        5
  20     Sam            30     Clerk           10 
  30     Cindy          30     Manager          2
                        40     Sales Rep        7
                        50     Sr. Manager.    11
 
Query
 SELECT T1.NAME, 
        T1.ID, 
        T2.ID, 
        T2.TITLE
 FROM EmployeeName T1
 RIGHT OUTER JOIN Position T2 
 ON T1.ID=T2.ID
 ORDER BY T1.ID
        , T2.TITLE;
 
Result         
 ID   NAME     ID     TITLE
 20   Sam      20     Sales Mgr
 30   Cindy    30     Clerk
 30   Cindy    30     Manager
 --   -----    40     Sales Rep
 --   -----    50     Manager  

Suppose we want to return the employee with no title. To do that, add a WHERE clause to include only rows with nulls from the EmployeeName table.

Query using ON or WHERE

 SELECT T1.NAME, 
        T1.ID, 
        T2.ID, 
        T2.TITLE
 FROM EmployeeName T1
 RIGHT OUTER JOIN Position T2 
 ON T1.ID = T2.ID
 AND T1.NAME IS NULL
 
 SELECT T1.NAME, 
        T1.ID, 
        T2.ID, 
        T2.TITLE
 FROM EmployeeName T1
 LEFT OUTER JOIN Position T2 
 ON T1.ID=T2.ID
 WHERE T1.NAME IS NULL
 
 Both the above queries will give the same result.
 Result         
 ID   NAME     ID     TITLE
 --   -----    40     Sales Rep
 --   -----    50     Manager 

Full Outer Join

A full outer join is also known as a full join. It merges rows from two tables based on a related column. Unlike inner joins, it returns all rows from both tables. Inner joins only return rows with matching values in both tables. Outer joins (left and right) include all rows from one table and matching rows from the other. A full outer join returns NULL values if there is no match in either table.

Input Data
 EmployeeName Table-(T1)Position Table-(T2)         
  ID     NAME           ID     TITLE         TEAM    
  10     Sandy          20     Sales Mgr        5
  20     Sam            30     Clerk           10 
  30     Cindy          30     Manager          2
                        40     Sales Rep        7
                        50     Sr. Manager.    11 

Query
 SELECT T1.NAME, 
        T1.ID, 
        T2.ID, 
        T2.TITLE
 FROM EmployeeName T1
 FULL OUTER JOIN Position T2 
 ON T1.ID=T2.ID
 ORDER BY T1.ID,
          T2.ID
        , T2.TITLE;
 
Result         
 ID   NAME     ID     TITLE
 10   Sandy    --     ---------
 20   Sam      20     Sales Mgr
 30   Cindy    30     Clerk
 30   Cindy    30     Manager
 --   -----    40     Sales Rep
 --   -----    50     Manager  

Suppose we want to return the employee with ID<30. To do that, add a WHERE clause to include only rows with ID<30 from the EmployeeName table.

Query using ON or WHERE

 SELECT T1.NAME, 
        T1.ID, 
        T2.ID, 
        T2.TITLE
 FROM EmployeeName T1
 FULL OUTER JOIN Position T2 
 ON T1.ID = T2.ID
 AND T1.ID < 30
 ORDER BY T1.ID,
          T2.ID
        , T2.TITLE;
 
Result         
 ID   NAME     ID     TITLE
 10   Sandy    --     ---------
 20   Sam      20     Sales Mgr

In this example the “T1.ID < 30” check is done during the join where it does not eliminate rows but rather limits those that match in the two views:

SELECTT1.NAME, 
        T1.ID, 
        T2.ID, 
        T2.TITLE
 FROMEmployeeName T1
 FULL OUTER JOIN Position T2 
 ONT1.ID=T2.ID
 WHERE T1.ID < 30
 ORDER BY T1.ID,
          T2.ID
        , T2.TITLE;
 
Result         
 ID   NAME     ID     TITLE
 10   Sandy    --     ---------
 20   Sam      20     Sales Mgr
 30   Cindy    --     ---------
 --   -----    30     Clerk
 --   -----    30     Manager
 --   -----    40     Sales Rep
 --   -----    50     Manager 

Summary

Outer Join can be summarized as 

• It can return both inner rows and outer rows. Inner rows are the result that’s similar to INNER JOIN’s result. Outer rows are the non-null values with their null counterparts based on the join condition.
• It can be LEFT, RIGHT, or FULL. We had examples for each.
• The outer rows returned by this can be used in a variety of practical ways. We had ideas on when you can use this stuff.

In summary, LEFT OUTER JOIN retrieves unmatched rows from the left table. RIGHT OUTER JOIN retrieves unmatched rows from the right table. FULL OUTER JOIN retrieves unmatched rows from both tables. These operations enhance your ability to analyze data relationships. It also handles scenarios where not every record has a match in the related table.

INNER JOIN: Click Here IBM DB2 Manual: Click Here