OK. Now we know how to select records from one table using the FROM clause. We also know that we shouldn’t use several tables there. This is what we’re supposed to do using JOINs. So, let’s see what it is and how it works.
But wait a second. Why would we use several tables in one query? Are there cases that require that? Let me show you. This is our ShiftSchedule information register. It remembers what cashier is to work what shift, but the only thing it knows about the cashier is its reference. None of the cashier attributes are stored in this table. The same is true for the Shifts catalog. So we cannot get the cashier’s name or the shift details from the ShiftSchedule table. But what happens if I run this query then? Hmmm... This looks like the Shifts catalog code attribute, and this is the Cashiers catalog Description attribute, which we use to store the Cashier’s full name. So, how did they get here? This is how. The Platform knows that the references won’t make much sense to a user. So, instead of showing them, it goes to both tables, looks up for the records with these references and shows their so-called presentation fields. For the Cashiers catalog, it would be the Description attribute, and for the Shifts catalog, it’s the Code attribute.
This look-up-the-presentation operation is what the Platform gets done using JOINs. We call it a hidden JOIN because you cannot see it anywhere in the query. But if you trace the actual SQL query sent by the platform to the DBMS, you will see something like that. This is how the Platform gets the Shifts data. It takes the Shift reference from the first record of the ShiftSchedule catalog, goes to the Shifts catalog, searches for the Shift with the same reference and gets the Code value from there. Then, the same gets repeated for all ShiftShedule records. After it’s done, the query goes to the Cashiers catalog and retrieves the Cashiers’ full names the same way. The difference between the hidden and the explicit JOINs is, obviously, that the hidden one knows how to read the only attribute of the joined table - its representation. The explicit JOIN can afford reading anything it wants. Just like this.
OK. Let’s take a look at the JOIN syntax. Being a part of the FROM clause, they live right next floor down. There might be as many JOINs as you need in a single query. The JOIN has a type and a logical condition the query uses when looking up joined table records.
OK. Now let’s talk the JOIN types and how they affect the query results. I recently decided to store the employment contracts along with the supplier contracts. So, I made the Cashiers catalog another owner of the Agreements catalog, and now I have some agreements belonging to companies and others belonging to cashiers, like these two. So, let’s take this table and the Companies table and play with different JOINs between these two.
Let’s start with the same old LEFT JOIN from the Companies catalog to the Agreements catalog. First of all, what’s so left about this JOIN? Here is the deal. LEFT means that the query will cycle through all the records in the table to the left of the JOIN. Which is the Companies catalog. For each record in the left table it will try and find the record in the right table that matches the JOIN condition. There is a single match for the Gross Grocery, so there’ll be a single record in the resulting recordset. For the Infinity Foods, there are three matches in the Agreements table, so there will be three Infinity Foods records in the results. There are no matches for the Shoppers Drug Mart, but the LEFT JOIN doesn’t care. It has to take all the records from the left table regardless of whether there is a match or not. So, it adds Shoppers Drug Mart with nothing in the Agreement description field. This "nothing" value is called NULL, and we can check it in the query language using IS NULL condition like this. So, this was the LEFT JOIN. What happens if I change it to the RIGHT one?
This is what. This time the query took all the records from the table to the right of the JOIN and filled out the Company column with matches found in the left table. So, the Shoppers Drug Mart didn’t make it to the results, because no agreements belong to it. Instead, there are these two records that have nothing to do with my suppliers - the cashiers’ employment contracts. So, these were the LEFT and the RIGHT JOINs.
Next one is called the INNER JOIN and this is what it does: it takes only matching records from both tables. So, there is no Shoppers Drug Mart in the results because the query couldn’t find the matching agreement. There are also no employment contracts, because they don’t belong to any company. The last JOIN type is called the FULL JOIN, and this is how it works. It takes all records from both tables, fills all matches it can find, and leaves Nulls where no matches are found. This is how the JOIN works in 1C:Enterprise.