# Lesson 3-4

## LEFT and RIGHT JOINs

INNER JOIN filters out records that don’t meet the ON conditions from both - left and right - tables of the JOIN. If there is a record in the left table having no match in the right table, it will be filtered out. And other way around - a record from the right table having no match in the left table will be filtered out too. In this respect, INNER JOIN is symmetrical. You can write

SELECT ... FROM Table1 INNER JOIN Table2 ON ...

or

SELECT … FROM Table2 INNER JOIN Table2 ON ...

and the result will be exactly the same (if you use the same conditions, of course). Therefore, there is no such thing as LEFT or RIGHT INNER JOIN. Please, also note that INNER JOIN is a default type of JOIN, so you can omit the keyword INNER.

This is not the case for OUTER JOINs. Say, you have these two Catalogs:

The query

```SELECT C1.Field1, C2.Field1
FROM Catalog.Catalog1 AS C1
LEFT JOIN Catalog.Catalog2 AS C2
ON C1.Field1 = C2.Field1
```

will return this recordset:

So, all records from the left table have made it into the query result. For those of them who haven’t found a match, the query simply put NULL in the correspondent column.

What happens when we swap the tables around leaving the same type of JOIN? This query

```SELECT C1.Field1, C2.Field1
FROM Catalog.Catalog2 AS C2
LEFT JOIN Catalog.Catalog1 AS C1
ON C1.Field1 = C2.Field1
```

will return the different result:

How come we’ve lost one of the records? Well, this time query selects all records from the Catalog2 table and looks for matching pair in the Catalog1 table. Therefore, we cannot see 4-NULL record anymore. It wasn’t in the Catalog2 and didn’t make it to the resulting recordset.

So. For OUTER JOIN, there is no symmetry. Depending on the place of the tables (to the left or right side of the JOIN) the result can be different. This is what we need LEFT and RIGHT keywords in queries for. It defines do we get all records from this table or only matching once.

LEFT OUTER JOIN tells to a query to select records from the left table and then look for matching records in the right table. RIGHT OUTER JOIN tells to a query to select records from the right table and then look for matching records in the left table.

Note, that you can omit the OUTER keyword.

Needless to say, that LEFT and RIGHT keywords are completely interchangeable if you swap the tables at the same time. In other words, Table1 LEFT JOIN Table will return exactly the same result as Table2 RIGHT JOIN Table1.

## JOINs’ types resume

Let’s summarize all we have known about JOINs by now.

## Multiple JOINs

You can use as many joins as you need in a single query. Say, we can join both MaterialsAndServices and Warehouses catalogs to the Balance table query like this:

```SELECT * FROM
AccumulationRegister.BalanceOfMaterials.Balance AS Balance
LEFT JOIN Catalog.Warehouses AS Warehouses
ON Balance.Warehouse = Warehouses.Ref
LEFT JOIN Catalog.MaterialsAndServices AS Materials
ON Balance.Material = Materials.Ref
```

Or we can select all warehouse, join them to the tabular section MaterialsAndServices of the Services document and then join the MaterialsAndServices catalog:

```SELECT * FROM
Catalog.Warehouses AS Warehouses
LEFT JOIN Document.Services.MaterialsAndServices AS Material
ON (Material.Warehouse = Warehouses.Ref)
LEFT JOIN Catalog.MaterialsAndServices AS MaterialsAndServices
ON (Material.MaterialOrService = MaterialsAndServices.Ref)
```

## WHERE and JOIN conditions

Say, we need to select all materials from MaterialsAndServices tabular section of Services documents. If we write the query like this

```SELECT * FROM
Document.Services.MaterialsAndServices AS ServicesMaterialsAndServices
LEFT JOIN Catalog.MaterialsAndServices AS MaterialsAndServices
ON ServicesMaterialsAndServices.MaterialOrService = MaterialsAndServices.Ref
```

we will get all tabular section records, including services we don’t need. So we need to add a condition MaterialsAndServices.MaterialOrService = VALUE(Enum.MaterialOrService.Material).

VALUE keyword is used to obtain a predefined catalog item or enumeration item and use it directly in the query text. Another option which is available here - is using a parameter (&<parameter name>) and setting its value with Query.SetParameter(<parameter name>, <parameter value>).

One more question is where we need to add this condition to? Into JOIN conditions or into WHERE conditions?

Let’s check it out.

Here is the texts of the queries we just used. Query with WHERE filter:

```SELECT
MaterialsAndServices.Description,
ServicesMaterialsAndServices.Quantity
FROM
Document.Services.MaterialsAndServices AS ServicesMaterialsAndServices
LEFT JOIN Catalog.MaterialsAndServices AS MaterialsAndServices
ON ServicesMaterialsAndServices.MaterialOrService = MaterialsAndServices.Ref
WHERE
MaterialsAndServices.MaterialOrService = VALUE(Enum.MaterialOrService.Material
```

Query with INNER JOIN filter

```SELECT
MaterialsAndServices.Description,
ServicesMaterialsAndServices.Quantity
FROM
Document.Services.MaterialsAndServices AS ServicesMaterialsAndServices
INNER JOIN Catalog.MaterialsAndServices AS MaterialsAndServices
ON ServicesMaterialsAndServices.MaterialOrService = MaterialsAndServices.Ref AND
MaterialsAndServices.MaterialOrService = VALUE(Enum.MaterialOrService.Material
```

One more question to answer: if WHERE and INNER JOIN conditions do the same filtering job, which one of them is better to use? Well, in most cases there will be no real difference performance-wise, but we still recommend you to use JOIN when you filter one table against another. It’s a more natural choice and there is a possibility of speeding-up your application in some cases.

## Hidden JOINs in 1C query syntax

Let’s take another look at our last query.

This is the query text we used in the video above:

```SELECT
ServicesMaterialsAndServices.Quantity,
ServicesMaterialsAndServices.MaterialOrService.Description,
ServicesMaterialsAndServices.MaterialOrService.Parent.Description
FROM
Document.Services.MaterialsAndServices AS ServicesMaterialsAndServices
```

So, how does the Platform gets this fields’ values from other tables using no JOINs? Well, it’s simple: the Platform, actually, uses JOINs, it just doesn’t show them to us. Before the query will be run on the DBMS, the Platform will convert it into a pure SQL form, getting rid of everything that cannot be understood by the DBMS (replacing the 1C metadata objects’ names with DBMS table names and so on). During this procedure the Platform will replace these dots with LEFT OUTER JOINS to the correspondent tables.

So, you can consider this dot syntax to be 1C query language extension to the classic SQL. It doesn’t introduce any new functionality - it just allows you to shorten you query text and make it more transparent.

Note, that there is no difference between dot and classic JOIN syntax whatsoever. The query above will be executed exactly in the same way as the following query:

```SELECT
ServicesMaterialsAndServices.Quantity,
MaterialsAndServices.Description,
MaterialsAndServices1.Description AS Description1
FROM
Document.Services.MaterialsAndServices AS ServicesMaterialsAndServices
LEFT JOIN Catalog.MaterialsAndServices AS MaterialsAndServices
ON ServicesMaterialsAndServices.MaterialOrService = MaterialsAndServices.Ref
LEFT JOIN Catalog.MaterialsAndServices AS MaterialsAndServices1
ON MaterialsAndServices1.Ref = MaterialsAndServices.Parent
```

Note, that in the query above we used Catalog.MaterialsAndServices twice - first time to get the Description of the material or service and second time - to get the Description of the folder it sits in.

So, we can use the same metadata object as many times as we need.

## Checking the balance using a query with JOIN

Now we are ready to write the checking balance query using a JOIN filtering.

We have two tables to get data from:

• AccumulationRegister.BalanceOfMaterials.Balance AS BalanceTable
• Document.Services.MaterialsAndServices AS TabularSection

And five conditions to check:

• TabularSection.Ref = &Ref
• TabularSection.MaterialOrService = &Material
• BalanceTable.QuantityBalance < 0
• BalanceTable.Material = TabularSection.MaterialOrService
• BalanceTable.Warehouse = TabularSection.Warehouse

What JOIN should we use - LEFT or RIGHT? In what order should we list the tables? BalanceTable JOIN TabularSection or TabularSection JOIN BalanceTable? How we use the conditions - what conditions in the WHERE clause and what conditions in the ON section of JOIN?

Let’s use the following data set as an example and consider the step-by-step approach to build the query we need.

Here is a basic algorithm to follow:

### 1. Choosing a main table

Let’s call a “main” the table in the FROM section of a query. Of course, from SQL syntax viewpoint there is no such thing as a main table in a query. We can write “BalanceTable JOIN TabularSection” query or “TabularSection JOIN BalanceTable” and if we do everything right, the result will be the same in every sense.

So, choosing the main table is just a matter of convenience. On my liking, the TabularSection table is more meaningful in this role then the BalanceTable, so I will be using it.

The first step on the way to the resulting query is this:

```SELECT
TabularSection.Warehouse AS Warehouse,
TabularSection.MaterialOrService AS Material
FROM
Document.Services.MaterialsAndServices AS TabularSection
```

The result will be the following:

### 2. Filtering the main table against its independent conditions

If you take another look at the conditions list, you will see that there are conditions of two different types:

• Independent - that filter the table against its own fields’ values:
• TabularSection.Ref = &Ref
• TabularSection.MaterialOrService = &Material
• BalanceTable.QuantityBalance < 0
• Dependent - that filter one table against another table fields’ values
• BalanceTable.Material = TabularSection.MaterialOrService
• BalanceTable.Warehouse = TabularSection.Warehouse

On this step we need to add all main table’s independent conditions to the WHERE clause:

```SELECT
TabularSection.Warehouse AS Warehouse,
TabularSection.MaterialOrService AS Material
FROM
Document.Services.MaterialsAndServices AS TabularSection
WHERE
TabularSection.Ref = &Ref
AND TabularSection.MaterialOrService = &Material
```

And this would be the result:

### 3. Choosing the JOIN type

We use <main table> LEFT JOIN to look up for additional information we don’t have in the main table. We use <main table> INNER JOIN to filter the main table against joined table.

Therefore, we need INNER JOIN here.

### 4. Using main table’s dependent conditions in JOIN’s ON clause

Let’s add our INNER JOIN and specify all dependent conditions in its ON section:

```SELECT
TabularSection.Warehouse AS Warehouse,
TabularSection.MaterialOrService AS Material,
BalanceTable.QuantityBalance AS QuantityBalance
FROM
Document.Services.MaterialsAndServices AS TabularSection
INNER JOIN AccumulationRegister.BalanceOfMaterials.Balance AS BalanceTable
ON (BalanceTable.Material = TabularSection.MaterialOrService)
AND (BalanceTable.Warehouse = TabularSection.Warehouse)
WHERE
TabularSection.Ref = &Ref
AND TabularSection.MaterialOrService = &Material
```

Note, that now (after we added the JOIN) we can use the joined table fields in the SELECT list, so we added BalanceTable.QuantityBalance there. Also note, that when we join multiple tables, we should use correspondent conditions in every ON clause like this:

```SELECT… FROM <main table>
JOIN <table1> ON <table1 dependent conditions>
JOIN <table2> ON <table2 dependent conditions>
...
```

This is the resulting recordset we will get on this step:

### 5. Adding joined tables’ independent conditions to the WHERE clause

We have one independent condition left. Let’s add it to the WHERE clause:

```SELECT
TabularSection.Warehouse AS Warehouse,
TabularSection.MaterialOrService AS Material,
BalanceTable.QuantityBalance AS QuantityBalance
FROM
Document.Services.MaterialsAndServices AS TabularSection
INNER JOIN AccumulationRegister.BalanceOfMaterials.Balance AS BalanceTable
ON (BalanceTable.Material = TabularSection.MaterialOrService)
AND (BalanceTable.Warehouse = TabularSection.Warehouse)
WHERE
TabularSection.Ref = &Ref
AND TabularSection.MaterialOrService = &Material
AND BalanceTable.QuantityBalance < 0
```

And this is the final result we get:

Lesson 3-3 | Course description | Lesson 3-5