Trouble seeing the images? Right click on images and open in new tab to enlarge or zoom in on the page (Ctrl + mousewheel).

In this article we look at the different types of joins used in VDM's linking process.

What are Joins?

JOINS are used to combine rows from two or more tables, based on a related column/field between them.

How many types of Joins are there in SQL?

There are four main types of joins to understand. They are:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN

Video Tutorial:

For the examples in this article, we are going to be using the tables below. The tables will be joined on the artist_id.

Table 1 - song:

Table 2 - artist:

INNER JOIN

An Inner Join is the most commonly used Join. An Inner Join will match the set of records that appear in both tables.

Summary: Select all records from Table 1 and Table 2, where the join condition is met.

Note: Implicit is available for databases that do not support Explicit

SQL Syntax: Explicit Join

SELECT Table1.Column1,Table1.Column2,Table2.Column1,....
FROM Table1
INNER JOIN Table2
ON Table1.MatchingColumnName = Table2.MatchingColumnName;

VDM Linking (Explicit):

SQL Syntax: Implicit Join

SELECT Table1.Column1,Table1.Column2,Table2.Column1,....
FROM Table1,Table2
WHERE Table1.MatchingColumnName = Table2.MatchingColumnName;

VDM Linking (Implicit): 

INNER JOIN Output:

As you can see in the image above, all records where artist_id is equal from the left table (Table 1) and from the right table (Table 2) are returned. This would be artist_id 1, 2, 3 and 4.

LEFT JOIN

A Left Join returns all records from the left table (Table 1), the matched records from the right table (Table 2) and null values in the right table, if there is no match condition.

Summary: Select all records from Table 1, along with records from Table 2 for which the join condition is met (if at all).

SQL Syntax: 

SELECT Table1.Column1,Table1.Column2,Table2.Column1,....
FROM Table1
LEFT JOIN Table2
ON Table1.MatchingColumnName = Table2.MatchingColumnName;

VDM LEFT JOIN Linking:

LEFT JOIN Output:

As you can see in the image above, all records from the left table (Table 1) are returned, and records from the right table (Table 2) are returned as null values if there is no match between the two tables.

This is shown on artist_id 6 where the results from Table 1 are shown with null values in Table 2. This happened because there was no matching artist_id in Table 2.

RIGHT JOIN

A Right Join is similar to the Left Join only reversed. Right join returns all records from the right table (Table 2), the matched records from the left table (Table 1) and null values for left side, when there is no match.

Summary: Return all records from the right table, and the matched records from the left table.

SQL Syntax: 

SELECT Table1.Column1,Table1.Column2,Table2.Column1,....
FROM Table1
RIGHT JOIN Table2
ON Table1.MatchingColumnName = Table2.MatchingColumnName;

VDM RIGHT JOIN Linking:

RIGHT JOIN Output:

As you can see in the image above, all records from the Right Table (Table 2) are returned, and records from the Left Table (Table 1) are returned as null values if there is no match between the two tables.

This is shown for artist_id 5 where the results from Table 2 are shown with null values in Table 1. This happened because there was no matching artist_id in Table 1.

FULL JOIN

Full Join is a combination of LEFT JOIN and RIGHT JOIN, as it takes all the records from both tables and places NULL values where the information from the matching table is missing.

Summary: Return all records when there is a match in either left or right table

Note: FULL JOIN can potentially return very large result sets.

SQL Syntax: 

SELECT Table1.Column1,Table1.Column2,Table2.Column1,....
FROM Table1
FULL JOIN Table2
ON Table1.MatchingColumnName = Table2.MatchingColumnName;

VDM FULL JOIN Linking:

FULL JOIN Output:

As you can see in the image above, Full Join combines the results of a Left and Right Join. Artist_id 6 has no matching record in the Right Table, so nulls are returned (Left Join). Artist_id 5 has no matching record in the Left Table, so nulls are returned (Right Join).