Understanding SQL Joins in VDM

Trouble Viewing Images? Right-click on any image and select "Open in new tab" to view a larger version. You can also zoom in using Ctrl + Mouse Wheel for easier readability.

Article Goal

This article provides a clear overview of the different SQL join types supported by VDM's linking feature and explains how each join works using simple examples and visual aids.

What Are Joins?

Joins are used in SQL—and in VDM—to combine rows from two or more tables based on a related column. This is essential when working with multiple tables in a view, especially when organizing, filtering, or analyzing connected data.

Why Learn About Join Types?

Understanding join types helps you:

• Choose the correct method for combining data across multiple tables.

• Ensure complete and accurate results in your queries.

• Avoid unintentionally omitting critical data (e.g., unmatched records).

Video Tutorial:

Types of SQL Joins Supported in VDM

We’ll be using two example tables joined on the artist_id field to demonstrate each join type:

• Table 1: song

• Table 2: artist

Table 1 - song:

Table 2 - artist:

INNER JOIN (Explicit and Implicit)

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

Summary: Selects all records from both tables where the join condition is met.

Note: Implicit joins are available for databases that do not support explicit joins.

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:

Output Example:

Only matching artist_id values (e.g., 1, 2, 3, 4) are returned.

LEFT JOIN

Returns all records from the left table, and the matched records from the right table. If no match exists, the right side will show NULL.

Summary: Selects everything from Table 1 and the matched values from Table 2, or NULL if there’s no match.

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:

Output Example:

artist_id = 6 appears from Table 1 with NULL values for Table 2, since there is no match.

RIGHT JOIN

Returns all records from the right table, and the matched records from the left table. If no match exists, the left side will show NULL.

Summary: Selects everything from Table 2 and the matched values from Table 1, or NULL if there’s no match.

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:

Output Example:

artist_id = 5 appears from Table 2 with NULL values for Table 1, since there is no match.

FULL JOIN

Returns all records from both tables, inserting NULL where there is no match.

Summary: Combines results of both LEFT and RIGHT joins.

Note: FULL JOIN can return very large datasets and should be used carefully.

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:

Output Example:

Includes:

• artist_id = 6 (LEFT side unmatched → right is NULL)

• artist_id = 5 (RIGHT side unmatched → left is NULL)

Tips & Best Practices

• Use INNER JOIN when you only need matching data from both tables.

• Use LEFT JOIN if you need everything from the first table regardless of matches.

• Use RIGHT JOIN for the reverse.

• Use FULL JOIN when you want to include all records from both sides.

Article Summary

This article outlines the main SQL join types—INNER, LEFT, RIGHT, and FULL—and how they are used in VDM. It provides syntax examples, output explanations, and tips for choosing the right join type for your report or view.