Scenarios of combining tables using JOIN with additional AND and OR conditions

1. Overview

The <ext:join> operation enables joining tables using different conditions. It is used when creating search conditions inside the <ext:createSearchCondition> tag to get the necessary data in roll-up tables.

There are three main join types:

  • INNER JOIN: An intersection of data from two tables. For example, <ext:join type="inner">.

  • LEFT JOIN: Extracts data from the first table (left) and joins data from the second table (right) where possible. For example, <ext:join type="left">.

  • RIGHT JOIN: The opposite of LEFT JOIN. For example, <ext:join type="right">.

You can use the <ext:join> operation with additional AND and OR operators, which you can define within the <ext:condition> tag as the value of the logicOperator attribute.

2. Usage examples

Let’s consider several examples of using JOIN in search conditions with additional AND and OR operators in the context of the role model and the KATOTTG territorial units codifier.

2.1. Prerequisites

Create two tables to join and use in search conditions.

  1. <createTable tableName="katottg"> creates a table with KATOTTG codes.

    Example of the "katottg" table 
    <changeSet id="table katottg" author="registry_owner">
    <createTable tableName="katottg" ext:historyFlag="true" remarks="katottg">
        <column name="katottg_id"  type="UUID" defaultValueComputed="uuid_generate_v4()"
                remarks="katottg ID">
            <constraints nullable="false" primaryKey="true" primaryKeyName="pk_katottg_id"/>
        </column>

        <column name="code" type="TEXT" remarks="Code">
            <constraints nullable="true"/>
        </column>
        <column name="name" type="TEXT" remarks="Name">
            <constraints nullable="true"/>
        </column>
        <column name="category" type="TEXT" remarks="Category">
            <constraints nullable="true"/>
        </column>
        <column name="level" type="INT">
            <constraints nullable="false"/>
        </column>
        <column name="katottg_parent_id" type="UUID">
            <constraints nullable="true"
                         foreignKeyName="fk_katottg_parent"
                         referencedTableName="katottg"
                         referencedColumnNames="katottg_id"/>
        </column>
    </createTable>
    <addUniqueConstraint tableName="katottg" columnNames="code"/>
</changeSet>

  2. <createTable tableName="katottg_category" creates a table with KATOTTG code categories.

    Example of the "katottg_category" table 
    <changeSet id="table katottg_category" author="registry_owner">
    <createTable tableName="katottg_category" ext:historyFlag="true" remarks="katottg_category">
        <column name="katottg_category_id"  type="UUID" defaultValueComputed="uuid_generate_v4()"
                remarks="ID">
            <constraints nullable="false" primaryKey="true" primaryKeyName="pk_katottg_category_id"/>
        </column>
        <column name="code" type="TEXT" remarks="Code">
            <constraints nullable="false"/>
        </column>
        <column name="name" type="TEXT" remarks="Name">
            <constraints nullable="true"/>
        </column>
    </createTable>
    <addUniqueConstraint tableName="katottg_category" columnNames="code"/>
</changeSet>

2.2. Scenarios

Let’s create a search condition called get_regions_or_city_regions. This search condition creates an endpoint in the data factory, which can return a list of regions, cities with a special status, their KATOTTG codes, and KATOTTG code categories using the following conditions:

  • Joining tables using JOIN with an AND condition:

    <ext:condition logicOperator="and" columnName="k.category" operator="eq"  value="'K'"/>

  • Joining tables using JOIN with an OR condition:

    <ext:condition logicOperator="or" columnName="k.category" operator="eq"  value="cat.code">
	<ext:condition logicOperator="and" columnName="k.category" operator="ne"  value="'K'"/>
	<ext:condition logicOperator="and" columnName="k.level" operator="eq"  value="'1'"/>
</ext:condition>

The result depends on the condition you specify in the request. A data sample is formed from several records joined in a roll-up table.

2.2.1. Using INNER JOIN without additional conditions

Example 1. XML schema. Using INNER JOIN without additional conditions
<ext:createSearchCondition name="get_regions_or_city_regions">
	<ext:table name="katottg" alias="k">
		<ext:column name="katottg_id" />
		<ext:column name="name" alias="name_region" searchType="startsWith" />
		<ext:column name="category" />
	</ext:table>
	<ext:table name="katottg_category" alias="cat">
		<ext:column name="name" alias="name_category" />
		<ext:column name="code" />
	</ext:table>
	<ext:join type="inner">
		<ext:left alias="k">
			<ext:column name="category" />
		</ext:left>
		<ext:right alias="cat">
			<ext:column name="code" />
		</ext:right>
	</ext:join>
</ext:createSearchCondition>
At the output, Liquibase generates the following SQL query:
Example 2. SQL query. Using INNER JOIN without additional conditions
CREATE
OR REPLACE VIEW registry.get_regions_or_city_regions_v AS
SELECT
  k.katottg_id,
  k.name AS name_region,
  cat.name AS name_category,
  cat.code
FROM
  katottg k
  JOIN katottg_category cat
  ON k.category = cat.code

2.2.2. Using INNER JOIN with an AND condition

Example 3. XML schema. Using INNER JOIN with an AND condition
<ext:createSearchCondition name="get_regions_or_city_regions">
	<ext:table name="katottg" alias="k">
		<ext:column name="katottg_id" />
		<ext:column name="name" alias="name_region" searchType="startsWith" />
		<ext:column name="category" />
	</ext:table>
	<ext:table name="katottg_category" alias="cat">
		<ext:column name="name" alias="name_category" />
		<ext:column name="code" />
	</ext:table>
	<ext:join type="inner">
		<ext:left alias="k">
			<ext:column name="category" />
		</ext:left>
		<ext:right alias="cat">
			<ext:column name="code" />
		</ext:right>
		<ext:condition logicOperator="and" columnName="k.category" operator="eq"  value="'K'"/>
	</ext:join>
</ext:createSearchCondition>
At the output, Liquibase generates the following SQL query:
Example 4. SQL query. Using INNER JOIN with an AND condition
CREATE
OR REPLACE VIEW registry.get_regions_or_city_regions_v AS
SELECT
  k.katottg_id,
  k.name AS name_region,
  cat.name AS name_category,
  cat.code
FROM
  katottg k
  JOIN katottg_category cat
  ON k.category = cat.code
  AND k.category = 'K' :: text;
The result of the request is as follows:

When joining the tables using JOIN with an AND condition, you will get a sample of 2 records:

join and or usage 1
Figure 1. Request result when using JOIN + AND condition

2.2.3. Using INNER JOIN with an OR condition

Example 5. Using INNER JOIN with an OR condition
<ext:createSearchCondition name="get_regions_or_city_regions">
	<ext:table name="katottg" alias="k">
		<ext:column name="katottg_id" />
		<ext:column name="name" alias="name_region" searchType="startsWith" />
		<ext:column name="category" />
	</ext:table>
	<ext:table name="katottg_category" alias="cat">
		<ext:column name="name" alias="name_category" />
		<ext:column name="code" />
	</ext:table>
	<ext:join type="inner">
		<ext:left alias="k">
			<ext:column name="category" />
		</ext:left>
		<ext:right alias="cat">
			<ext:column name="code" />
		</ext:right>
		<ext:condition logicOperator="or" columnName="k.category" operator="eq"  value="cat.code">
			<ext:condition logicOperator="and" columnName="k.category" operator="ne"  value="'K'"/>
			<ext:condition logicOperator="and" columnName="k.level" operator="eq"  value="'1'"/>
                </ext:condition>
	</ext:join>
</ext:createSearchCondition>
At the output, Liquibase generates the following SQL query:
Example 6. SQL query. Using INNER JOIN with an OR condition
CREATE
OR REPLACE VIEW registry.get_regions_or_city_regions_v AS
SELECT
  k.katottg_id,
  k.name AS name_region,
  cat.name AS name_category,
  cat.code
FROM
  katottg k
  JOIN katottg_category cat
  ON k.category = cat.code
  OR k.category = cat.code AND k.category <> 'K'::text AND k.level = 1;
The result of the request is as follows:

When joining the tables using JOIN with an OR condition, you will get a sample of 25 records:

join and or usage 2
Figure 2. Request result when using JOIN + OR condition

2.2.4. Using INNER JOIN with AND and OR conditions

Example 7. XML schema. Using INNER JOIN with AND and OR conditions
<ext:createSearchCondition name="get_regions_or_city_regions">
	<ext:table name="katottg" alias="k">
		<ext:column name="katottg_id" />
		<ext:column name="name" alias="name_region" searchType="startsWith" />
		<ext:column name="category" />
	</ext:table>
	<ext:table name="katottg_category" alias="cat">
		<ext:column name="name" alias="name_category" />
		<ext:column name="code" />
	</ext:table>
	<ext:join type="inner">
		<ext:left alias="k">
			<ext:column name="category" />
		</ext:left>
		<ext:right alias="cat">
			<ext:column name="code" />
		</ext:right>
		<ext:condition logicOperator="and" columnName="k.category" operator="eq"  value="'K'"/>
        <ext:condition logicOperator="or" columnName="k.category" operator="eq"  value="cat.code">
			<ext:condition logicOperator="and" columnName="k.category" operator="ne"  value="'K'"/>
			<ext:condition logicOperator="and" columnName="k.level" operator="eq"  value="'1'"/>
        </ext:condition>
	</ext:join>
</ext:createSearchCondition>
At the output, Liquibase generates the following SQL query:
Example 8. SQL query. Using INNER JOIN with AND and OR conditions
CREATE
OR REPLACE VIEW registry.get_regions_or_city_regions_v AS
SELECT
  k.katottg_id,
  k.name AS name_region,
  cat.name AS name_category,
  cat.code
FROM
  katottg k
  JOIN katottg_category cat
  ON k.category = cat.code
  AND k.category = 'K' :: text
  OR k.category = cat.code AND k.category <> 'K'::text AND k.level = 1;
The result of the request is as follows:

When joining the tables using JOIN with AND and OR conditions, you will get a sample of 27 records:

join and or usage 3
Figure 3. Request result when using JOIN + AND + OR conditions

3. Other usage examples

You can add custom conditions to the primary <ext:join> conditions using Liquibase’s functional extension.

3.1. Using LEFT JOIN with a single additional OR condition

For example, to join two tables, cities and katottg_dictionary, on the condition that katottg equals either level4 or add_level, you would use the following conditions combined with an OR operator:

  • <ext:left> and <ext:right> in <ext:join> to define the main condition, katottg=level4

  • <ext:condition> to define the additional condition, katottg=add_level

Example 9. XML schema of joining the "cities" and "katottg_dictionary" tables using a single additional OR condition
<changeSet author="registry owner" id="or join">
    <ext:createSearchCondition name="cities_4_or_5_level">
        <ext:table name="cities" alias="ci">
            <ext:column name="city_id" />
            <ext:column name="name" searchType="startsWith" />
            <ext:column name="katottg" searchType="equal" />
        </ext:table>
        <ext:table name="katottg_dictionary" alias="kd">
            <ext:column name="name" alias="name_dict" />
        </ext:table>
        <ext:join type="left">
            <ext:left alias="ci">
                <ext:column name="katottg" />
            </ext:left>
            <ext:right alias="kd">
                <ext:column name="level4" />
            </ext:right>
            <ext:condition logicOperator="or" columnName="ci.katottg" operator="eq" value="kd.add_level" />
        </ext:join>
    </ext:createSearchCondition>
</changeSet>
At the output, Liquibase generates the following SQL query:
Example 10. SQL query. Using LEFT JOIN with an OR condition
SELECT ci.city_id, ci.name, ci.katottg, kd.name AS name_dict
  FROM cities AS ci LEFT JOIN katottg_dictionary AS kd
    ON (ci.katottg = kd.level4) or (ci.katottg = kd.add_level);

3.2. Using LEFT JOIN with several additional AND and OR conditions

You can use <ext:condition> to model more complex conditions of joining tables.

For example, to join the same two tables, cities and katottg_dictionary, on the condition that katottg equals level4 when category does not equal 'B', or katottg equals add_level when category equals 'B', you would use the following search condition:

Example 11. XML schema of joining the "cities" and "katottg_dictionary" tables using several additional AND and OR conditions
<changeSet author="registry owner" id="or join by category">
    <ext:createSearchCondition name="cities_4_or_5_level_by_category">
        <ext:table name="cities" alias="ci">
            <ext:column name="city_id" />
            <ext:column name="name" searchType="startsWith" />
            <ext:column name="katottg" searchType="equal" />
        </ext:table>
        <ext:table name="katottg_dictionary" alias="kd">
            <ext:column name="name" alias="name_dict" />
        </ext:table>
        <ext:join type="left">
            <ext:left alias="ci">
                <ext:column name="katottg" />
            </ext:left>
            <ext:right alias="kd">
                <ext:column name="level4" />
            </ext:right>
            <ext:condition logicOperator="and" columnName="kd.category" operator="ne" value="'B'">
                <ext:condition logicOperator="or" columnName="ci.katottg" operator="eq" value="kd.add_level" />
                <ext:condition logicOperator="and" columnName="kd.category" operator="eq" value="'B'"/>
            </ext:condition>
        </ext:join>
    </ext:createSearchCondition>
</changeSet>
At the output, Liquibase generates the following SQL query:
Example 12. SQL query. Using LEFT JOIN with several additional AND and OR conditions
SELECT ci.city_id, ci.name, ci.katottg, kd.name AS name_dict
  FROM cities AS ci LEFT JOIN katottg_dictionary AS kd
    ON (ci.katottg = kd.level4) and (kd.category = 'B')
       or ((ci.katottg = kd.add_level) and (kd.category <> 'B'));

Note that the nested conditions in <ext:condition> are grouped by parentheses inside the code.

For this example, grouping is optional: without the parentheses, the code will work the same as with the parentheses because the AND conditions have a higher priority than OR and will be resolved first.

Keep this in mind for other potential use cases.