However, we not only have INNER JOIN, but also other types of JOIN.

LEFT JOIN

Let's move on to LEFT JOIN. A LEFT JOIN is a special form of joining two tables that guarantees that all records from the left table appear completely in the result, regardless of whether there are matching entries in the right table. You can think of it this way: The left table forms the fixed point, and for each of its rows, the database tries to find the corresponding information from the right table. If a matching record exists in the right table, both are combined and output. However, if there is no corresponding entry, the query still includes the row from the left table in the result. In this case, the fields in the right table are simply filled with NULL.

LEFT JOINs are useful when the left table is the focal point of the evaluation. A classic example would be a list of all available products (left table) that needs to be linked to the sales for the current day (right table). Even if a product has not been sold today, it still appears in the result because it is in the left table. In this way, missing information, anomalies, or gaps can be clearly identified. In our example, we now want the movies in the left table and the roles in the right table. These are also linked via the primary key of Movies2 to the Movies field in the Rols2 table.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Movies2.Name, Movies2.Director, Movies2.Launch, Movies2.PrimaryKey, 
   Rols2.Movie, Rols2.Name, Rols2.Actor 
   FROM Movies2 LEFT JOIN Rols2 ON Movies2.PrimaryKey = Rols2.Movie" ) ]
Set Variable [ $r ; Value: MBS("FM.SQL.Text"; $SQLRes; ""; ""; ""; ""; "¶"; "-") ]
Show Custom Dialog [ "Result" ; $r ]
Set Variable [ $r ; Value: MBS("FM.SQL.ReleaseAll") ]

This is what the result looks like:

Unsupported joins in FileMaker

The FileMaker engine for SQL, that the plugin also works with, cannot handle RIGHT JOIN and FULL OUTER JOIN. Therefore, you cannot use them in conjunction with FileMaker. However, in case you encounter them, we will explain both here. RIGHT JOIN is very similar to LEFT JOIN, except that instead of taking all records from the left table, we take them from the right - regardless of whether a corresponding record exists in the left table or not, the fields in the left table that correspond to a record that has no match in the left table are filled with NULL. Another join type is the FULL OUTER JOIN. This includes all records from the left and right table. If there is no match for a record in the opposing table, the records are still transferred to the result set and we also fill them with NULL.

CROSS JOIN

I would also like to mention CROSS JOIN. This join does not use a specific criterion or field, but connects all records in the right table with all records in the left table, regardless of whether the records are matching. So if table A has x entries and table B has y entries, we end up with a result set containing x*y records. We also talk about forming the Cartesian product of these two tables. If we then want to narrow down the records, we can use a WHERE clause. A CROSS JOIN can have the following structure:

SELECT *
FROM 
tabel 1 CROSS JOIN tabel 2

However, another notation is also possible, because we also form the Cartesian product when we combine two tables in the FROM section by listing them one after the other.

SELECT *
FROM tabel 1 ,tabel 2

Today, I would like to give you a little task, which we will solve tomorrow in the door. How would our query

SELECT Rols2.Movie, Movies2.Name, Movies2.Director, Movies2.Launch, Rols2.Actor 
   FROM Movies2 
   JOIN Rols2 ON Movies2.PrimaryKey = Rols2.Movie

look like if we wanted to express it with a CROSS JOIN?

Have fun trying it out, and I hope to see you again tomorrow.