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