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.