Joins

Cory Fifield -

Trouble viewing images? Click on images to enlarge.

 

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

 

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:
mceclip13.png

Table 2 - artist:
mceclip12.png

 

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.

mceclip3.png

 

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):
mceclip4.png

 

SQL Syntax: Implicit Join

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

 

VDM Linking (Implicit): 
mceclip6.png

 

INNER JOIN Output:
mceclip9.png

 

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).

mceclip1.png

 

SQL Syntax: 

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

 

VDM LEFT JOIN Linking:
mceclip16.png

 

LEFT JOIN Output:
mceclip15.png

 

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.

mceclip0.png

 

SQL Syntax: 

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

 

VDM RIGHT JOIN Linking:
mceclip17.png

 

RIGHT JOIN Output:
mceclip10.png

 

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.

mceclip2.png

 

SQL Syntax: 

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

 

VDM FULL JOIN Linking:
mceclip4.png

 

FULL JOIN Output:
mceclip8.png

 

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).

Have more questions? Submit a request

Comments