Repository: squashql/squashql
Pull request #248 (2024-01-25 06:58:46)

[Experimental] Add the ability to JOIN mulitple SELECT statement results
Usage:
```typescript
const a = new TableField("myTable1.a")
const b1 = new TableField("myTable1.b")
const c1 = new TableField("myTable1.c")
const query1 = from("myTable1")
      .select([a, b1, c1], [], [sum("sum", new TableField("f1"))])
      .build()

const b2 = new TableField("myTable2.b")
const c2 = new TableField("myTable2.c")
const query2 = from("myTable2")
      .select([b2, c2], [], [avg("sum", new TableField("f2"))])
      .build()

const c3 = new TableField("myTable3.c3")
const query3 = from("myTable3")
      .select([c3], [], [avg("max", new TableField("f3"))])
      .build()

const orders = new Map([[a, new SimpleOrder(OrderKeyword.ASC)], [c3, new SimpleOrder(OrderKeyword.DESC)]])
const q = new QueryJoin(query1)
      .join(query2, JoinType.LEFT,
              all([
                criterion_(b1, b2, ConditionType.EQ),
                criterion_(c1, c2, ConditionType.EQ)
              ]))
      .join(query3, JoinType.INNER)
      .orderBy(orders)
      .limit(12)

new Querier("http://...")
      .executeQueryJoin(q)
      .then(r => ...)
```


Patch:
diff --git a/bigquery/src/main/java/io/squashql/query/database/BigQueryEngine.java b/bigquery/src/main/java/io/squashql/query/database/BigQueryEngine.java
index 0a3d589..b338cee 100644
--- a/bigquery/src/main/java/io/squashql/query/database/BigQueryEngine.java
+++ b/bigquery/src/main/java/io/squashql/query/database/BigQueryEngine.java
@@ -125,6 +125,6 @@ public class BigQueryEngine extends AQueryEngine<BigQueryDatastore> {
 
   @Override
   public QueryRewriter queryRewriter(DatabaseQuery query) {
-    return new BigQueryQueryRewriter(this.datastore.getProjectId(), this.datastore.getDatasetName(), query);
+    return new BigQueryQueryRewriter(this.datastore.getProjectId(), this.datastore.getDatasetName());
   }
 }
diff --git a/bigquery/src/main/java/io/squashql/query/database/BigQueryQueryRewriter.java b/bigquery/src/main/java/io/squashql/query/database/BigQueryQueryRewriter.java
index 414201a..d4dc2d2 100644
--- a/bigquery/src/main/java/io/squashql/query/database/BigQueryQueryRewriter.java
+++ b/bigquery/src/main/java/io/squashql/query/database/BigQueryQueryRewriter.java
@@ -3,29 +3,19 @@ package io.squashql.query.database;
 import io.squashql.query.date.DateFunctions;
 import io.squashql.type.FunctionTypedField;
 import io.squashql.type.TypedField;
+import lombok.AllArgsConstructor;
 
+@AllArgsConstructor
 public class BigQueryQueryRewriter implements QueryRewriter {
 
   private final String projectId;
   private final String datasetName;
-  private final DatabaseQuery query;
-
-  BigQueryQueryRewriter(String projectId, String datasetName, DatabaseQuery query) {
-    this.projectId = projectId;
-    this.datasetName = datasetName;
-    this.query = query;
-  }
-
-  @Override
-  public DatabaseQuery query() {
-    return this.query;
-  }
 
   @Override
   public String functionExpression(FunctionTypedField ftf) {
     if (DateFunctions.SUPPORTED_DATE_FUNCTIONS.contains(ftf.function())) {
       // https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#extract
-      return String.format("EXTRACT(%s FROM %s)", ftf.function(), getFieldFullName(ftf.field()));
+      return String.format("EXTRACT(%s FROM %s)", ftf.function(), ftf.field().sqlExpression(this));
     } else {
       throw new IllegalArgumentException("Unsupported function " + ftf);
     }
diff --git a/clickhouse/src/main/java/io/squashql/query/database/ClickHouseQueryEngine.java b/clickhouse/src/main/java/io/squashql/query/database/ClickHouseQueryEngine.java
index de6ade0..e6993f8 100644
--- a/clickhouse/src/main/java/io/squashql/query/database/ClickHouseQueryEngine.java
+++ b/clickhouse/src/main/java/io/squashql/query/database/ClickHouseQueryEngine.java
@@ -125,22 +125,11 @@ public class ClickHouseQueryEngine extends AQueryEngine<ClickHouseDatastore> {
 
   @Override
   public QueryRewriter queryRewriter(DatabaseQuery query) {
-    return new ClickHouseQueryRewriter(query);
+    return new ClickHouseQueryRewriter();
   }
 
   static class ClickHouseQueryRewriter implements QueryRewriter {
 
-    private final DatabaseQuery query;
-
-    ClickHouseQueryRewriter(DatabaseQuery query) {
-      this.query = query;
-    }
-
-    @Override
-    public DatabaseQuery query() {
-      return this.query;
-    }
-
     @Override
     public String fieldName(String field) {
       return SqlUtils.backtickEscape(field);
diff --git a/core/src/main/java/io/squashql/query/ExperimentalQueryMergeExecutor.java b/core/src/main/java/io/squashql/query/ExperimentalQueryMergeExecutor.java
deleted file mode 100644
index a459194..0000000
--- a/core/src/main/java/io/squashql/query/ExperimentalQueryMergeExecutor.java
+++ /dev/null
@@ -1,275 +0,0 @@
-package io.squashql.query;
-
-import io.squashql.jackson.JacksonUtil;
-import io.squashql.query.compiled.*;
-import io.squashql.query.database.DatabaseQuery;
-import io.squashql.query.database.QueryEngine;
-import io.squashql.query.database.QueryRewriter;
-import io.squashql.query.database.SQLTranslator;
-import io.squashql.query.dto.*;
-import io.squashql.store.UnknownType;
-import io.squashql.table.ColumnarTable;
-import io.squashql.table.Table;
-import io.squashql.type.AliasedTypedField;
-import io.squashql.type.TableTypedField;
-import io.squashql.type.TypedField;
-import lombok.AllArgsConstructor;
-import lombok.extern.slf4j.Slf4j;
-import org.eclipse.collections.api.tuple.Pair;
-import org.eclipse.collections.api.tuple.Twin;
-import org.eclipse.collections.impl.list.mutable.MutableListFactoryImpl;
-import org.eclipse.collections.impl.tuple.Tuples;
-
-import java.util.*;
-
-import static io.squashql.query.QueryExecutor.LIMIT_DEFAULT_VALUE;
-import static io.squashql.query.database.AQueryEngine.transformToColumnFormat;
-import static io.squashql.query.database.SQLTranslator.addLimit;
-
-@Slf4j
-@AllArgsConstructor
-public class ExperimentalQueryMergeExecutor {
-
-  private final QueryEngine<?> queryEngine;
-
-  class Holder {
-    final QueryDto query;
-    final QueryResolver queryResolver;
-    final DatabaseQuery dbQuery;
-    final QueryRewriter queryRewriter;
-    final String originalTableName; // Can be null if using a sub-query
-    final String cteTableName;
-    final String sql;
-
-    Holder(String cteTableName, QueryDto query) {
-      this.query = query;
-      this.cteTableName = cteTableName;
-      this.queryResolver = new QueryResolver(query, ExperimentalQueryMergeExecutor.this.queryEngine.datastore().storesByName());
-      this.dbQuery = this.queryResolver.toDatabaseQuery(this.queryResolver.getScope(), -1);
-      this.queryResolver.getMeasures().values().forEach(this.dbQuery::withMeasure);
-      this.queryRewriter = ExperimentalQueryMergeExecutor.this.queryEngine.queryRewriter(this.dbQuery);
-      this.originalTableName = query.table != null ? query.table.name : null;
-      this.sql = SQLTranslator.translate(this.dbQuery, this.queryRewriter);
-    }
-  }
-
-  record CteTable(String name, List<CompiledJoin> joins) implements CompiledTable, NamedTable {
-
-    @Override
-    public String sqlExpressionTableName(QueryRewriter queryRewriter) {
-      return queryRewriter.cteName(this.name);
-    }
-
-    @Override
-    public String sqlExpression(QueryRewriter queryRewriter) {
-      return CompiledTable.sqlExpression(queryRewriter, this);
-    }
-  }
-
-  public Table execute(QueryDto first,
-                       QueryDto second,
-                       JoinType joinType,
-                       CriteriaDto joinCondition,
-                       Map<Field, OrderDto> orders,
-                       int limit) {
-    int queryLimit = limit <= 0 ? LIMIT_DEFAULT_VALUE : limit;
-
-    Holder left = new Holder("__cteL__", first);
-    Holder right = new Holder("__cteR__", second);
-
-    StringBuilder sb = new StringBuilder("with ");
-    sb.append(left.queryRewriter.cteName(left.cteTableName)).append(" as (").append(left.sql).append("), ");
-    sb.append(right.queryRewriter.cteName(right.cteTableName)).append(" as (").append(right.sql).append(") ");
-
-    CompiledCriteria compiledCriteria = compiledCriteria(joinType, joinCondition, left, right);
-    CompiledJoin compiledJoin = new CompiledJoin(new CteTable(right.cteTableName, List.of()), joinType, compiledCriteria);
-    CteTable cteLeftTable = new CteTable(left.cteTableName, List.of(compiledJoin));
-
-    Twin<List<TypedField>> selectColumns = getSelectElements(cteLeftTable, left, right);
-    List<String> selectSt = new ArrayList<>();
-    selectColumns.getOne().forEach(typedField -> selectSt.add(replaceTableNameByCteNameIfNotNull(left, left.queryRewriter.select(typedField))));
-    selectColumns.getTwo().forEach(typedField -> selectSt.add(replaceTableNameByCteNameIfNotNull(right, right.queryRewriter.select(typedField))));
-    left.query.measures.forEach(m -> selectSt.add(left.queryRewriter.escapeAlias(m.alias())));
-    right.query.measures.forEach(m -> selectSt.add(right.queryRewriter.escapeAlias(m.alias())));
-    sb
-            .append("select ")
-            .append(String.join(", ", selectSt))
-            .append(" from ");
-
-    String tableExpression = cteLeftTable.sqlExpression(left.queryRewriter);
-    tableExpression = replaceTableNameByCteNameIfNotNull(left, tableExpression);
-    tableExpression = replaceTableNameByCteNameIfNotNull(right, tableExpression);
-    sb.append(tableExpression);
-
-    addOrderBy(orders, sb, left, right);
-    addLimit(queryLimit, sb);
-
-    String sql = sb.toString();
-    log.info("sql=" + sql);
-    Table table = this.queryEngine.executeRawSql(sql);
-
-    List<? extends Class<?>> columnTypes = table.headers().stream().map(Header::type).toList();
-    List<CompiledMeasure> measures = new ArrayList<>();
-    left.query.measures.forEach(m -> measures.add(left.queryResolver.getMeasures().get(m)));
-    right.query.measures.forEach(m -> measures.add(right.queryResolver.getMeasures().get(m)));
-
-    Pair<List<Header>, List<List<Object>>> result = transformToColumnFormat(
-            MutableListFactoryImpl.INSTANCE.withAll(selectColumns.getOne()).withAll(selectColumns.getTwo()),
-            measures,
-            columnTypes,
-            (columnType, name) -> columnType,
-            table.iterator(),
-            (i, row) -> row.get(i));
-    return new ColumnarTable(
-            result.getOne(),
-            new HashSet<>(measures),
-            result.getTwo());
-  }
-
-  private CompiledCriteria compiledCriteria(JoinType joinType, CriteriaDto joinCondition, Holder left, Holder right) {
-    if (joinCondition != null) {
-      QueryDto leftCteQuery = new QueryDto().table(left.cteTableName);
-      QueryDto rightCteQuery = new QueryDto().table(right.cteTableName);
-      CriteriaDto joinConditionCopy = JacksonUtil.deserialize(JacksonUtil.serialize(joinCondition), CriteriaDto.class);
-      CriteriaDto rewrittenJoinCondition = rewriteJoinCondition(joinConditionCopy);
-      leftCteQuery.table.join(new TableDto(rightCteQuery.table.name), joinType, rewrittenJoinCondition);
-      QueryResolver queryResolver = new QueryResolver(leftCteQuery, this.queryEngine.datastore().storesByName());
-      return queryResolver.compileCriteria(rewrittenJoinCondition);
-    } else {
-      // Try to guess the condition
-      List<TypedField> leftColumns = new ArrayList<>();
-      List<TypedField> rightColumns = new ArrayList<>();
-      for (Field field : left.query.columns) {
-        TypedField typedField = left.queryResolver.resolveField(field);
-        leftColumns.add(typedField.alias() != null ? new AliasedTypedField(typedField.alias()) : typedField); // we have to use the aliased field in the select
-      }
-      for (Field field : right.query.columns) {
-        TypedField typedField = right.queryResolver.resolveField(field);
-        rightColumns.add(typedField.alias() != null ? new AliasedTypedField(typedField.alias()) : typedField); // we have to use the aliased field in the select
-      }
-      leftColumns.retainAll(rightColumns);
-      if (!leftColumns.isEmpty()) {
-        List<CompiledCriteria> children = new ArrayList<>(leftColumns.size());
-        for (TypedField leftColumn : leftColumns) {
-          TableTypedField l = new TableTypedField(left.originalTableName, leftColumn.name(), UnknownType.class, leftColumn.alias());
-          TableTypedField r = new TableTypedField(right.originalTableName, leftColumn.name(), UnknownType.class, leftColumn.alias());
-          children.add(new CompiledCriteria(null, ConditionType.EQ, l, r, null, null));
-
-        }
-        return children.size() > 1
-                ? new CompiledCriteria(null, ConditionType.AND, null, null, null, children)
-                : children.get(0);
-      } else {
-        return null; // no condition
-      }
-    }
-  }
-
-  private static CriteriaDto rewriteJoinCondition(CriteriaDto joinCondition) {
-    List<CriteriaDto> children = joinCondition.children;
-    if (children != null && !children.isEmpty()) {
-      for (CriteriaDto child : children) {
-        rewriteJoinCondition(child);
-      }
-    } else {
-      String alias = joinCondition.field.alias();
-      if (alias != null) {
-        joinCondition.field = new AliasedField(alias); // replace with aliased field
-      }
-
-      String otherAlias = joinCondition.fieldOther.alias();
-      if (otherAlias != null) {
-        joinCondition.fieldOther = new AliasedField(otherAlias); // replace with aliased field
-      }
-    }
-    return joinCondition;
-  }
-
-  private static void addOrderBy(Map<Field, OrderDto> orders, StringBuilder sb, Holder left, Holder right) {
-    if (orders != null && !orders.isEmpty()) {
-      sb.append(" order by ");
-      List<String> orderList = new ArrayList<>();
-      for (Map.Entry<Field, OrderDto> e : orders.entrySet()) {
-        Field key = e.getKey();
-        Holder holder = left;
-        // Where does it come from ? Left or right?
-        TypedField typedField = left.queryResolver.getTypedFieldOrNull(key);
-        if (typedField == null) {
-          typedField = (holder = right).queryResolver.getTypedFieldOrNull(key);
-          if (typedField == null) {
-            throw new RuntimeException("Cannot resolve " + e.getKey());
-          }
-        }
-        String orderByField = holder.queryRewriter.aliasOrFullExpression(typedField);
-        orderByField = replaceTableNameByCteNameIfNotNull(holder, orderByField);
-        orderList.add(orderByField + " nulls last");
-      }
-      sb.append(String.join(", ", orderList));
-    }
-  }
-
-  /**
-   * Returns a list of elements that will end up in the select statement based on the given join table, left holder,
-   * and right holder. Columns first then measures.
-   *
-   * @param joinTable the compiled table containing join information
-   * @param left      the left holder
-   * @param right     the right holder
-   * @return a list of select elements
-   */
-  private static Twin<List<TypedField>> getSelectElements(CompiledTable joinTable, Holder left, Holder right) {
-    List<TypedField> leftColumns = new ArrayList<>();
-    List<TypedField> rightColumns = new ArrayList<>();
-    for (Field field : left.query.columns) {
-      TypedField typedField = left.queryResolver.resolveField(field);
-      leftColumns.add(typedField.alias() != null ? new AliasedTypedField(typedField.alias()) : typedField); // we have to use the aliased field in the select
-    }
-
-    // Try to guess from the conditions which field to keep.
-    CompiledCriteria jc = joinTable.joins().get(0).joinCriteria();
-    Set<TypedField> joinFields = jc != null ? collectJoinFields(jc) : null;
-    for (Field field : right.query.columns) {
-      TypedField typedField = right.queryResolver.resolveField(field);
-      TypedField tf = typedField.alias() != null ? new AliasedTypedField(typedField.alias()) : typedField;
-      // Do not add if it is in the join. We keep only the other field from the left query.
-      if (joinFields == null || !joinFields.contains(tf)) {
-        rightColumns.add(tf); // we have to use the aliased field in the select
-      }
-    }
-
-    // Remove any ambiguity and keep left columns if right columns are contained in the left columns
-    Set<TypedField> inter = new HashSet<>(leftColumns);
-    inter.retainAll(rightColumns);
-    rightColumns.removeAll(leftColumns);
-    List<TypedField> newLeft = new ArrayList<>();
-    for (TypedField leftColumn : leftColumns) {
-      if (inter.contains(leftColumn)) {
-        newLeft.add(new TableTypedField(left.originalTableName, leftColumn.name(), UnknownType.class, leftColumn.alias()));
-      } else {
-        newLeft.add(leftColumn);
-      }
-    }
-    return Tuples.twin(newLeft, rightColumns);
-  }
-
-  private static Set<TypedField> collectJoinFields(CompiledCriteria joinCriteria) {
-    Set<TypedField> collected = new HashSet<>();
-    List<CompiledCriteria> children = joinCriteria.children();
-    if (children != null && !children.isEmpty()) {
-      for (CompiledCriteria child : children) {
-        collected.addAll(collectJoinFields(child));
-      }
-    } else {
-      collected.add(joinCriteria.field());
-      collected.add(joinCriteria.fieldOther());
-    }
-    return collected;
-  }
-
-  private static String replaceTableNameByCteNameIfNotNull(Holder holder, String s) {
-    if (holder.originalTableName != null) {
-      s = s.replace(holder.queryRewriter.tableName(holder.originalTableName), holder.queryRewriter.cteName(holder.cteTableName));
-    }
-    return s;
-  }
-}
diff --git a/core/src/main/java/io/squashql/query/MeasureUtils.java b/core/src/main/java/io/squashql/query/MeasureUtils.java
index 78076b2..68ab24f 100644
--- a/core/src/main/java/io/squashql/query/MeasureUtils.java
+++ b/core/src/main/java/io/squashql/query/MeasureUtils.java
@@ -1,7 +1,6 @@
 package io.squashql.query;
 
 import io.squashql.query.compiled.*;
-import io.squashql.query.database.DatabaseQuery;
 import io.squashql.query.database.QueryRewriter;
 import io.squashql.query.dto.QueryDto;
 import io.squashql.type.TableTypedField;
@@ -15,17 +14,7 @@ import java.util.function.Consumer;
 @NoArgsConstructor
 public final class MeasureUtils {
 
-  public static final QueryRewriter BASIC = new QueryRewriter() {
-    @Override
-    public DatabaseQuery query() {
-      return null;
-    }
-
-    @Override
-    public boolean usePartialRollupSyntax() {
-      return false;
-    }
-  };
+  public static final QueryRewriter BASIC = () -> false;
 
   public static String createExpression(Measure m) {
     if (m instanceof AggregatedMeasure a) {
@@ -63,7 +52,7 @@ public final class MeasureUtils {
     }
 
     @Override
-    protected TypedField resolveField(Field field) {
+    public TypedField resolveField(Field field) {
       return new TableTypedField(null, field.name(), String.class);
     }
 
diff --git a/core/src/main/java/io/squashql/query/QueryExecutor.java b/core/src/main/java/io/squashql/query/QueryExecutor.java
index 221f002..28e38d5 100644
--- a/core/src/main/java/io/squashql/query/QueryExecutor.java
+++ b/core/src/main/java/io/squashql/query/QueryExecutor.java
@@ -6,6 +6,8 @@ import io.squashql.query.database.DatabaseQuery;
 import io.squashql.query.database.QueryEngine;
 import io.squashql.query.database.SqlUtils;
 import io.squashql.query.dto.*;
+import io.squashql.query.dto.QueryJoinDto;
+import io.squashql.query.join.ExperimentalQueryMergeExecutor;
 import io.squashql.query.parameter.QueryCacheParameter;
 import io.squashql.table.ColumnarTable;
 import io.squashql.table.PivotTable;
@@ -373,14 +375,8 @@ public class QueryExecutor {
     return QueryMergeExecutor.executeQueryMerge(this, first, second, joinType, user);
   }
 
-  public Table executeExperimentalQueryMerge(QueryDto first,
-                                             QueryDto second,
-                                             JoinType joinType,
-                                             CriteriaDto joinCondition,
-                                             Map<Field, OrderDto> orders,
-                                             int limit) {
-    return new ExperimentalQueryMergeExecutor(this.queryEngine)
-            .execute(first, second, joinType, joinCondition, orders, limit);
+  public Table executeExperimentalQueryMerge(QueryJoinDto queryJoin) {
+    return new ExperimentalQueryMergeExecutor(this.queryEngine).execute(queryJoin);
   }
 
   /**
diff --git a/core/src/main/java/io/squashql/query/QueryResolver.java b/core/src/main/java/io/squashql/query/QueryResolver.java
index f66fb9d..5c6535d 100644
--- a/core/src/main/java/io/squashql/query/QueryResolver.java
+++ b/core/src/main/java/io/squashql/query/QueryResolver.java
@@ -23,6 +23,7 @@ public class QueryResolver {
 
   private final QueryDto query;
   private final Map<String, Store> storesByName;
+  private final Set<String> cteTableNames = new HashSet<>();
   private final QueryExecutor.QueryScope scope;
   private final List<TypedField> bucketColumns;
   private final List<TypedField> columns;
@@ -37,6 +38,7 @@ public class QueryResolver {
     if (query.virtualTableDtos != null) {
       for (VirtualTableDto virtualTableDto : query.virtualTableDtos) {
         this.storesByName.put(virtualTableDto.name, VirtualTableDto.toStore(virtualTableDto));
+        this.cteTableNames.add(virtualTableDto.name);
       }
     }
     this.columns = query.columns.stream().map(this::resolveField).toList();
@@ -71,14 +73,14 @@ public class QueryResolver {
   /**
    * Field resolver
    */
-  protected TypedField resolveField(final Field field) {
+  public TypedField resolveField(final Field field) {
     return this.cache.computeIfAbsent(field, f -> {
       // Special case for the column that is created due to the column set.
       ColumnSet columnSet = this.query.columnSets.get(BUCKET);
       if (columnSet != null) {
         Field newField = ((BucketColumnSetDto) columnSet).newField;
         if (field.equals(newField)) {
-          return new TableTypedField(null, newField.name(), String.class, null);
+          return new TableTypedField(null, newField.name(), String.class, null, false);
         }
       }
 
@@ -104,7 +106,7 @@ public class QueryResolver {
     } catch (FieldNotFoundException e) {
       // This can happen if the using a "field" coming from the calculation of a subquery. Since the field provider
       // contains only "raw" fields, it will throw an exception.
-      return new TableTypedField(null, field.name(), Number.class, field.alias());
+      return new TableTypedField(null, field.name(), Number.class, field.alias(), false);
     }
   }
 
@@ -117,7 +119,7 @@ public class QueryResolver {
       if (store != null) {
         for (TableTypedField field : store.fields()) {
           if (field.name().equals(fieldNameInTable)) {
-            return alias == null ? field : new TableTypedField(field.store(), field.name(), field.type(), alias);
+            return alias == null ? field : new TableTypedField(field.store(), field.name(), field.type(), alias, this.cteTableNames.contains(store.name()));
           }
         }
       }
@@ -127,14 +129,14 @@ public class QueryResolver {
           if (field.name().equals(fieldName)) {
             // We omit on purpose the store name. It will be determined by the underlying SQL engine of the DB.
             // if any ambiguity, the DB will raise an exception.
-            return new TableTypedField(null, fieldName, field.type(), alias);
+            return new TableTypedField(null, fieldName, field.type(), alias, this.cteTableNames.contains(store.name()));
           }
         }
       }
     }
 
     if (fieldName.equals(CountMeasure.INSTANCE.alias())) {
-      return new TableTypedField(null, CountMeasure.INSTANCE.alias(), long.class, alias);
+      return new TableTypedField(null, CountMeasure.INSTANCE.alias(), long.class, alias, false);
     }
     throw new FieldNotFoundException("Cannot find field with name " + fieldName);
   }
@@ -217,19 +219,24 @@ public class QueryResolver {
   /**
    * Table
    */
-  private CompiledTable compileTable(TableDto table, QueryDto subQuery) {
+  public CompiledTable compileTable(TableDto table, QueryDto subQuery) {
     if (table != null) {
-      // Can be a cte.
-      List<VirtualTableDto> vts = this.query.virtualTableDtos;
-      if (vts != null) {
-        List<CteRecordTable> cteRecordTables = compileVirtualTables(vts);
-        for (CteRecordTable cteRecordTable : cteRecordTables) {
-          if (cteRecordTable.name().equals(table.name)) {
-            return cteRecordTable;
+      List<CompiledJoin> joins = compileJoins(table.joins);
+      if (table.isCte) {
+        return new CteTable(table.name, joins);
+      } else {
+        // Can be a cte.
+        List<VirtualTableDto> vts = this.query.virtualTableDtos;
+        if (vts != null) {
+          List<CteRecordTable> cteRecordTables = compileVirtualTables(vts);
+          for (CteRecordTable cteRecordTable : cteRecordTables) {
+            if (cteRecordTable.name().equals(table.name)) {
+              return cteRecordTable;
+            }
           }
         }
       }
-      return new MaterializedTable(table.name, compileJoins(table.joins));
+      return new MaterializedTable(table.name, joins);
     } else if (subQuery != null) {
       return new NestedQueryTable(toSubQuery(subQuery));
     } else {
@@ -244,11 +251,11 @@ public class QueryResolver {
   /**
    * Joins
    */
-  private List<CompiledJoin> compileJoins(List<JoinDto> joins) {
+  public List<CompiledJoin> compileJoins(List<JoinDto> joins) {
     return joins.stream().map(this::compileJoin).collect(Collectors.toList());
   }
 
-  private CompiledJoin compileJoin(JoinDto join) {
+  public CompiledJoin compileJoin(JoinDto join) {
     CompiledTable table = compileTable(join.table, null);
     if (table instanceof NamedTable nt) {
       return new CompiledJoin(nt, join.type, compileCriteria(join.joinCriteria));
diff --git a/core/src/main/java/io/squashql/query/compiled/CompiledJoin.java b/core/src/main/java/io/squashql/query/compiled/CompiledJoin.java
index 8fb0f12..88cdd45 100644
--- a/core/src/main/java/io/squashql/query/compiled/CompiledJoin.java
+++ b/core/src/main/java/io/squashql/query/compiled/CompiledJoin.java
@@ -11,10 +11,11 @@ public record CompiledJoin(NamedTable table, JoinType type, CompiledCriteria joi
             .append(this.type.name().toLowerCase())
             .append(" join ")
             .append(this.table.sqlExpressionTableName(queryRewriter));
-    if (joinCriteria() != null) {
+    CompiledCriteria jc = joinCriteria();
+    if (jc != null) {
       statement
               .append(" on ")
-              .append(joinCriteria().sqlExpression(queryRewriter));
+              .append(jc.sqlExpression(queryRewriter));
     }
 
     if (!this.table.joins().isEmpty()) {
diff --git a/core/src/main/java/io/squashql/query/compiled/CteRecordTable.java b/core/src/main/java/io/squashql/query/compiled/CteRecordTable.java
index 2cd9138..0a45c6e 100644
--- a/core/src/main/java/io/squashql/query/compiled/CteRecordTable.java
+++ b/core/src/main/java/io/squashql/query/compiled/CteRecordTable.java
@@ -17,7 +17,7 @@ public record CteRecordTable(String name,
 
   @Override
   public List<CompiledJoin> joins() {
-    return Collections.emptyList(); // FIXME not sure about this one. Might be chained with other cte??
+    return Collections.emptyList();
   }
 
   @Override
diff --git a/core/src/main/java/io/squashql/query/compiled/CteTable.java b/core/src/main/java/io/squashql/query/compiled/CteTable.java
new file mode 100644
index 0000000..91c679c
--- /dev/null
+++ b/core/src/main/java/io/squashql/query/compiled/CteTable.java
@@ -0,0 +1,30 @@
+package io.squashql.query.compiled;
+
+import io.squashql.query.database.QueryRewriter;
+
+import java.util.List;
+
+public record CteTable(String name,
+                       List<CompiledJoin> joins) implements CompiledTable, NamedTable {
+
+  @Override
+  public String sqlExpressionTableName(QueryRewriter queryRewriter) {
+    return queryRewriter.cteName(this.name);
+  }
+
+  @Override
+  public String sqlExpression(QueryRewriter queryRewriter) {
+    return CompiledTable.sqlExpression(queryRewriter, this);
+  }
+
+  @Override
+  public String toString() {
+    final StringBuilder sb = new StringBuilder("CteTable{");
+    sb.append("name='").append(this.name).append('\'');
+    if (this.joins != null && !this.joins.isEmpty()) {
+      sb.append(", joins=").append(this.joins);
+    }
+    sb.append('}');
+    return sb.toString();
+  }
+}
diff --git a/core/src/main/java/io/squashql/query/database/DefaultQueryRewriter.java b/core/src/main/java/io/squashql/query/database/DefaultQueryRewriter.java
index fa7bf65..0117008 100644
--- a/core/src/main/java/io/squashql/query/database/DefaultQueryRewriter.java
+++ b/core/src/main/java/io/squashql/query/database/DefaultQueryRewriter.java
@@ -8,11 +8,6 @@ public class DefaultQueryRewriter implements QueryRewriter {
     this.query = query;
   }
 
-  @Override
-  public DatabaseQuery query() {
-    return this.query;
-  }
-
   @Override
   public String fieldName(String field) {
     return SqlUtils.backtickEscape(field);
diff --git a/core/src/main/java/io/squashql/query/database/QueryRewriter.java b/core/src/main/java/io/squashql/query/database/QueryRewriter.java
index 0fbffd1..0d11b40 100644
--- a/core/src/main/java/io/squashql/query/database/QueryRewriter.java
+++ b/core/src/main/java/io/squashql/query/database/QueryRewriter.java
@@ -1,29 +1,12 @@
 package io.squashql.query.database;
 
-import io.squashql.query.compiled.CteRecordTable;
 import io.squashql.type.AliasedTypedField;
 import io.squashql.type.FunctionTypedField;
 import io.squashql.type.TableTypedField;
 import io.squashql.type.TypedField;
 
-import java.util.List;
-
 public interface QueryRewriter {
 
-  DatabaseQuery query();
-
-  default String getFieldFullName(TableTypedField f) {
-    List<CteRecordTable> vts = query() != null ? query().cteRecordTables : null;
-    if (vts != null) {
-      for (CteRecordTable virtualTable : vts) {
-        if (virtualTable.name().equals(f.store()) && virtualTable.fields().contains(f.name())) {
-          return SqlUtils.getFieldFullName(cteName(f.store()), fieldName(f.name()));
-        }
-      }
-    }
-    return SqlUtils.getFieldFullName(f.store() == null ? null : tableName(f.store()), fieldName(f.name()));
-  }
-
   default String fieldName(String field) {
     return field;
   }
@@ -43,7 +26,7 @@ public interface QueryRewriter {
   }
 
   default String functionExpression(FunctionTypedField ftf) {
-    return ftf.function() + "(" + getFieldFullName(ftf.field()) + ")";
+    return ftf.function() + "(" + ftf.field().sqlExpression(this) + ")";
   }
 
   /**
@@ -81,7 +64,7 @@ public interface QueryRewriter {
   default String _select(TypedField f, boolean withAlias) {
     StringBuilder sb = new StringBuilder();
     if (f instanceof TableTypedField ttf) {
-      sb.append(getFieldFullName(ttf));
+      sb.append(ttf.sqlExpression(this));
     } else if (f instanceof FunctionTypedField ftf) {
       sb.append(functionExpression(ftf));
     } else if (f instanceof AliasedTypedField atf) {
@@ -91,7 +74,7 @@ public interface QueryRewriter {
     }
     String alias = f.alias();
     if (withAlias && alias != null) {
-      sb.append(" AS ").append(escapeAlias(alias));
+      sb.append(" as ").append(escapeAlias(alias));
     }
     return sb.toString();
   }
diff --git a/core/src/main/java/io/squashql/query/dto/QueryJoinDto.java b/core/src/main/java/io/squashql/query/dto/QueryJoinDto.java
index d302c30..affd0f0 100644
--- a/core/src/main/java/io/squashql/query/dto/QueryJoinDto.java
+++ b/core/src/main/java/io/squashql/query/dto/QueryJoinDto.java
@@ -1,23 +1,53 @@
 package io.squashql.query.dto;
 
+import com.fasterxml.jackson.annotation.JsonIgnore;
 import io.squashql.query.Field;
-import lombok.AllArgsConstructor;
 import lombok.EqualsAndHashCode;
 import lombok.NoArgsConstructor;
 import lombok.ToString;
 
+import java.util.ArrayList;
+import java.util.List;
 import java.util.Map;
 
 @ToString
 @EqualsAndHashCode
 @NoArgsConstructor // For Jackson
-@AllArgsConstructor
 public class QueryJoinDto {
 
-  public QueryDto first;
-  public QueryDto second;
-  public JoinType joinType;
-  public CriteriaDto joinCondition;
+  public List<QueryDto> queries = new ArrayList<>();
+  public TableDto table;
+  @JsonIgnore
+  public int current = 0;
   public Map<Field, OrderDto> orders;
   public int limit = -1;
+
+  private QueryJoinDto(QueryDto q1) {
+    this.queries.add(q1);
+    this.table = new TableDto(String.format("__cte%d__", this.current++));
+  }
+
+  public static QueryJoinDto from(QueryDto q) {
+    return new QueryJoinDto(q);
+  }
+
+  public QueryJoinDto join(QueryDto q, JoinType joinType, CriteriaDto criteriaDto) {
+    this.queries.add(q);
+    this.table.join(new TableDto(String.format("__cte%d__", this.current++)), joinType, criteriaDto);
+    return this;
+  }
+
+  public QueryJoinDto join(QueryDto q, JoinType joinType) {
+    return join(q, joinType, null);
+  }
+
+  public QueryJoinDto orderBy(Map<Field, OrderDto> orders) {
+    this.orders = orders;
+    return this;
+  }
+
+  public QueryJoinDto limit(int limit) {
+    this.limit = limit;
+    return this;
+  }
 }
diff --git a/core/src/main/java/io/squashql/query/dto/TableDto.java b/core/src/main/java/io/squashql/query/dto/TableDto.java
index bd4f97d..45e6b5d 100644
--- a/core/src/main/java/io/squashql/query/dto/TableDto.java
+++ b/core/src/main/java/io/squashql/query/dto/TableDto.java
@@ -13,9 +13,9 @@ import java.util.List;
 public class TableDto {
 
   public String name;
-
   public List<JoinDto> joins = new ArrayList<>();
-
+  public boolean isCte = false;
+  
   public TableDto(String name) {
     this.name = name;
   }
diff --git a/core/src/main/java/io/squashql/query/dto/VirtualTableDto.java b/core/src/main/java/io/squashql/query/dto/VirtualTableDto.java
index 6eba397..a202f97 100644
--- a/core/src/main/java/io/squashql/query/dto/VirtualTableDto.java
+++ b/core/src/main/java/io/squashql/query/dto/VirtualTableDto.java
@@ -28,7 +28,7 @@ public class VirtualTableDto {
     List<TableTypedField> fields = new ArrayList<>();
     for (int i = 0; i < virtualTableDto.fields.size(); i++) {
       Class<?> klazz = virtualTableDto.records.get(0).get(i).getClass(); // take the first row to determine the type
-      fields.add(new TableTypedField(virtualTableDto.name, virtualTableDto.fields.get(i), klazz));
+      fields.add(new TableTypedField(virtualTableDto.name, virtualTableDto.fields.get(i), klazz, true));
     }
     return new Store(virtualTableDto.name, fields);
   }
diff --git a/core/src/main/java/io/squashql/query/join/ExperimentalQueryMergeExecutor.java b/core/src/main/java/io/squashql/query/join/ExperimentalQueryMergeExecutor.java
new file mode 100644
index 0000000..6857e3c
--- /dev/null
+++ b/core/src/main/java/io/squashql/query/join/ExperimentalQueryMergeExecutor.java
@@ -0,0 +1,411 @@
+package io.squashql.query.join;
+
+import io.squashql.jackson.JacksonUtil;
+import io.squashql.query.*;
+import io.squashql.query.compiled.CompiledMeasure;
+import io.squashql.query.database.*;
+import io.squashql.query.dto.*;
+import io.squashql.table.ColumnarTable;
+import io.squashql.table.Table;
+import io.squashql.type.TableTypedField;
+import io.squashql.type.TypedField;
+import lombok.AllArgsConstructor;
+import lombok.extern.slf4j.Slf4j;
+import org.eclipse.collections.api.tuple.Pair;
+import org.eclipse.collections.api.tuple.Triple;
+import org.eclipse.collections.api.tuple.Twin;
+import org.eclipse.collections.impl.tuple.Tuples;
+
+import java.util.*;
+import java.util.stream.Collectors;
+
+import static io.squashql.query.QueryExecutor.LIMIT_DEFAULT_VALUE;
+import static io.squashql.query.database.AQueryEngine.transformToColumnFormat;
+import static io.squashql.query.database.SQLTranslator.addLimit;
+
+@Slf4j
+@AllArgsConstructor
+public class ExperimentalQueryMergeExecutor {
+
+  private final QueryEngine<?> queryEngine;
+
+  class Holder {
+    final QueryDto query;
+    final QueryResolver queryResolver;
+    final DatabaseQuery dbQuery;
+    final QueryRewriter queryRewriter;
+    final String originalTableName; // Can be null if using a sub-query
+    final String cteTableName;
+    final String sql;
+
+    Holder(String cteTableName, QueryDto query) {
+      this.query = query;
+      this.cteTableName = cteTableName;
+      this.queryResolver = new QueryResolver(query, ExperimentalQueryMergeExecutor.this.queryEngine.datastore().storesByName());
+      this.dbQuery = this.queryResolver.toDatabaseQuery(this.queryResolver.getScope(), -1);
+      this.queryResolver.getMeasures().values().forEach(this.dbQuery::withMeasure);
+      this.queryRewriter = ExperimentalQueryMergeExecutor.this.queryEngine.queryRewriter(this.dbQuery);
+      this.originalTableName = query.table != null ? query.table.name : null;
+      this.sql = SQLTranslator.translate(this.dbQuery, this.queryRewriter);
+    }
+  }
+
+  public Triple<String, List<TypedField>, List<CompiledMeasure>> generateSql(QueryJoinDto queryJoin) {
+    int queryLimit = queryJoin.limit <= 0 ? LIMIT_DEFAULT_VALUE : queryJoin.limit;
+
+    List<Holder> holders = new ArrayList<>(queryJoin.queries.size());
+    for (int i = 0; i < queryJoin.queries.size(); i++) {
+      if (i == 0) {
+        holders.add(new Holder(queryJoin.table.name, queryJoin.queries.get(i)));
+      } else {
+        holders.add(new Holder(queryJoin.table.joins.get(i - 1).table.name, queryJoin.queries.get(i)));
+      }
+    }
+
+    // Start by setting all CTEs
+    StringBuilder sb = new StringBuilder("with ");
+    List<List<Twin<String>>> columnNamesAndNamesOrAliases = new ArrayList<>();
+    Map<String, TypedField> typedFieldByFullName = new HashMap<>();
+    for (int i = 0; i < holders.size(); i++) {
+      Holder holder = holders.get(i);
+      sb.append(holder.queryRewriter.cteName(holder.cteTableName)).append(" as (").append(holder.sql);
+      sb.append(i < holders.size() - 1 ? "), " : ") ");
+
+      columnNamesAndNamesOrAliases.add(
+              holder.queryResolver.getColumns().stream().map(f -> {
+                String fieldFullName = SqlUtils.getFieldFullName(holder.queryRewriter.cteName(holder.cteTableName), holder.queryRewriter.fieldName(getFieldName(f)));
+                typedFieldByFullName.put(fieldFullName, f);
+                return Tuples.twin(fieldFullName, f.alias() != null ? holder.queryRewriter.escapeAlias(f.alias()) : fieldFullName);
+              }).toList());
+    }
+
+    // Order is important here (hence LinkedHashMap). The columns from first queries will take precedence over the last queries
+    Map<String, String> fullNameByAlias = new LinkedHashMap<>();
+    for (List<Twin<String>> columnNamesOrAliases : columnNamesAndNamesOrAliases) {
+      for (Twin<String> nameOrAlias : columnNamesOrAliases) {
+        fullNameByAlias.putIfAbsent(nameOrAlias.getTwo(), nameOrAlias.getOne());
+      }
+    }
+
+    List<JoinDto> newJoins = new ArrayList<>();
+    // Iterate over the joins to rewrite the condition when necessary (to use aliases)
+    Map<Integer, Map<String, Field>> fieldBySquashQLExpression = new TreeMap<>();
+    for (Field field : queryJoin.queries.get(0).columns) {
+      TypedField typedField = holders.get(0).queryResolver.resolveField(field);
+      fieldBySquashQLExpression.computeIfAbsent(0, k -> new HashMap<>()).put(SqlUtils.squashqlExpression(typedField), field);
+    }
+
+    int index = 1;
+    for (JoinDto join : queryJoin.table.joins) {
+      CriteriaDto joinConditionCopy = JacksonUtil.deserialize(JacksonUtil.serialize(join.joinCriteria), CriteriaDto.class);
+      if (joinConditionCopy == null) {
+        // Guess the condition
+        for (Field field : holders.get(index).query.columns) {
+          TypedField typedField = holders.get(index).queryResolver.resolveField(field);
+          fieldBySquashQLExpression.computeIfAbsent(index, k -> new HashMap<>()).put(SqlUtils.squashqlExpression(typedField), field);
+        }
+
+        List<Pair<Pair<Field, Integer>, Pair<Field, Integer>>> common = new ArrayList<>();
+        for (Map.Entry<Integer, Map<String, Field>> entry : fieldBySquashQLExpression.entrySet()) {
+          if (entry.getKey() == index) {
+            break;
+          }
+
+          for (Map.Entry<String, Field> fieldEntry : entry.getValue().entrySet()) {
+            if (fieldBySquashQLExpression.get(index).containsKey(fieldEntry.getKey())) {
+              common.add(Tuples.pair(Tuples.pair(fieldEntry.getValue(), entry.getKey()), Tuples.pair(fieldBySquashQLExpression.get(index).get(fieldEntry.getKey()), index)));
+            }
+          }
+        }
+
+        if (!common.isEmpty()) {
+          List<CriteriaDto> children = new ArrayList<>(common.size());
+          for (Pair<Pair<Field, Integer>, Pair<Field, Integer>> c : common) {
+            Field l = new TableField(holders.get(c.getOne().getTwo()).cteTableName, getFieldName(c.getOne().getOne()));
+            Field r = new TableField(holders.get(c.getTwo().getTwo()).cteTableName, getFieldName(c.getTwo().getOne()));
+            children.add(new CriteriaDto(l, r, null, null, ConditionType.EQ, Collections.emptyList()));
+          }
+          CriteriaDto criteriaDto = children.size() > 1
+                  ? new CriteriaDto(null, null, null, null, ConditionType.AND, children)
+                  : children.get(0);
+          newJoins.add(new JoinDto(join.table, join.type, criteriaDto));
+        } else {
+          newJoins.add(new JoinDto(join.table, JoinType.CROSS, null));
+        }
+      } else {
+        CriteriaDto rewrittenJoinCondition = rewriteJoinCondition(joinConditionCopy, holders);
+        newJoins.add(new JoinDto(join.table, join.type, rewrittenJoinCondition));
+      }
+      index++;
+    }
+
+    QueryRewriter queryRewriter = holders.get(0).queryRewriter;
+    StringBuilder joinSb = new StringBuilder();
+    joinSb.append(" from ").append(queryRewriter.cteName(holders.get(0).cteTableName));
+    Set<String> toRemoveFromSelectSet = new HashSet<>();
+    for (int i = 0; i < newJoins.size(); i++) {
+      Holder holder = holders.get(i + 1);
+      JoinDto jc = newJoins.get(i);
+      joinSb
+              .append(" ")
+              .append(jc.type.name().toLowerCase())
+              .append(" join ")
+              .append(queryRewriter.cteName(holder.cteTableName));
+      if (jc.joinCriteria != null) {
+        joinSb
+                .append(" on ")
+                .append(sqlExpression(jc.joinCriteria, queryRewriter, holders, toRemoveFromSelectSet));
+      }
+    }
+
+    sb.append("select ");
+
+    List<TypedField> selectedColumns = new ArrayList<>();
+    List<String> selectSt = new ArrayList<>();
+    for (Map.Entry<String, String> e : fullNameByAlias.entrySet()) {
+      String alias = e.getKey();
+      String fullName = e.getValue();
+      if (!toRemoveFromSelectSet.contains(fullName)) {
+        if (alias.equals(fullName)) { // no alias
+          selectSt.add(alias);
+        } else {
+          selectSt.add(fullName + " as " + alias); // with alias
+        }
+        selectedColumns.add(typedFieldByFullName.get(fullName));
+      }
+    }
+
+    // The measures
+    for (int i = 0; i < holders.size(); i++) {
+      Holder holder = holders.get(i);
+      holder.query.measures.forEach(m -> selectSt.add(holder.queryRewriter.escapeAlias(m.alias())));
+    }
+
+    sb.append(String.join(", ", selectSt));
+
+    sb.append(joinSb);
+
+    addOrderBy(queryJoin.orders, sb, queryRewriter, selectedColumns, holders);
+    addLimit(queryLimit, sb);
+
+    List<CompiledMeasure> measures = new ArrayList<>();
+    for (int i = 0; i < holders.size(); i++) {
+      Holder holder = holders.get(i);
+      holder.query.measures.forEach(measure -> measures.add(holder.queryResolver.getMeasures().get(measure)));
+    }
+
+    return Tuples.triple(sb.toString(), selectedColumns, measures);
+  }
+
+  public Table execute(QueryJoinDto queryJoin) {
+    Triple<String, List<TypedField>, List<CompiledMeasure>> sqlGenerationResult = generateSql(queryJoin);
+    log.info("sql=" + sqlGenerationResult.getOne());
+    Table result = this.queryEngine.executeRawSql(sqlGenerationResult.getOne());
+
+    List<? extends Class<?>> columnTypes = result.headers().stream().map(Header::type).toList();
+    Pair<List<Header>, List<List<Object>>> transform = transformToColumnFormat(
+            sqlGenerationResult.getTwo(),
+            sqlGenerationResult.getThree(),
+            columnTypes,
+            (columnType, name) -> columnType,
+            result.iterator(),
+            (i, row) -> row.get(i));
+    return new ColumnarTable(
+            transform.getOne(),
+            new HashSet<>(sqlGenerationResult.getThree()),
+            transform.getTwo());
+  }
+
+  /**
+   * Generates the sql expression corresponding to the {@link CriteriaDto}. In addition, the set is here to collect the
+   * columns from the {@link CriteriaDto} that should be removed from the select: e.g A.id = B.id, the set should contain
+   * B.id (or __cte1__.id) assuming A comes before B in the {@link Holder} list.
+   */
+  public String sqlExpression(CriteriaDto jc, QueryRewriter queryRewriter, List<Holder> holders, Set<String> toRemoveFromSelectSet) {
+    if (jc.field != null && jc.fieldOther != null && jc.conditionType != null) {
+      Holder leftHolder = getHolderOrigin(holders, jc.field);
+      Holder rightHolder = getHolderOrigin(holders, jc.fieldOther);
+
+      String left;
+      String leftFieldName = getFieldName(jc.field);
+      if (jc.field instanceof TableField tf) {
+        left = SqlUtils.getFieldFullName(queryRewriter.cteName(tf.tableName), queryRewriter.fieldName(leftFieldName));
+      } else {
+        // could be an aliased field? Need find where it comes from
+        left = SqlUtils.getFieldFullName(leftHolder == null ? null : queryRewriter.cteName(leftHolder.cteTableName), queryRewriter.fieldName(leftFieldName));
+      }
+
+      String right;
+      String rightFieldName = getFieldName(jc.fieldOther);
+      if (jc.fieldOther instanceof TableField tf) {
+        right = SqlUtils.getFieldFullName(queryRewriter.cteName(tf.tableName), queryRewriter.fieldName(rightFieldName));
+      } else {
+        // could be an aliased field? Need find where it comes from
+        right = SqlUtils.getFieldFullName(rightHolder == null ? null : queryRewriter.cteName(rightHolder.cteTableName), queryRewriter.fieldName(rightFieldName));
+      }
+
+      String toRemoveFromSelect = null;
+      for (Holder holder : holders) {
+        if (holder.equals(leftHolder)) {
+          toRemoveFromSelect = left; // Same logic than the select i.e __cte0__.fieldName
+        }
+        if (holder.equals(rightHolder)) {
+          toRemoveFromSelect = right; // Same logic than the select i.e __cte0__.fieldName
+        }
+      }
+
+      if (toRemoveFromSelect != null) {
+        toRemoveFromSelectSet.add(toRemoveFromSelect);
+      }
+
+      return String.join(" ", left, jc.conditionType.sqlInfix, right);
+    } else if (!jc.children.isEmpty()) {
+      String sep = switch (jc.conditionType) {
+        case AND -> " and ";
+        default -> throw new IllegalStateException("Unexpected value: " + jc.conditionType);
+      };
+      Iterator<CriteriaDto> iterator = jc.children.iterator();
+      List<String> conditions = new ArrayList<>();
+      while (iterator.hasNext()) {
+        String c = sqlExpression(iterator.next(), queryRewriter, holders, toRemoveFromSelectSet);
+        if (c != null) {
+          conditions.add(c);
+        }
+      }
+      return conditions.isEmpty() ? null : ("(" + String.join(sep, conditions) + ")");
+    } else {
+      return null;
+    }
+  }
+
+  public static void addOrderBy(Map<Field, OrderDto> orders, StringBuilder sb, QueryRewriter queryRewriter, List<TypedField> selectedColumns, List<Holder> holders) {
+    if (orders != null && !orders.isEmpty()) {
+      sb.append(" order by ");
+      List<String> orderList = new ArrayList<>();
+      for (Map.Entry<Field, OrderDto> e : orders.entrySet()) {
+        Field key = e.getKey();
+        TypedField typedField = null;
+        Holder h = null;
+        if (key.alias() != null) {
+          // Rely on the alias
+          for (TypedField selectedColumn : selectedColumns) {
+            if (key.alias().equals(selectedColumn.alias())) {
+              typedField = selectedColumn;
+              break;
+            }
+          }
+        } else {
+          // We assume it is a TableField, otherwise it is not supported
+          String tableName = ((TableField) key).tableName;
+          if (tableName != null) {
+            for (Holder holder : holders) {
+              if (holder.originalTableName.equals(tableName)) {
+                typedField = holder.queryResolver.resolveField(key);
+                h = holder;
+                break;
+              }
+            }
+          } else {
+            // Take the first one that matches
+            for (Holder holder : holders) {
+              if (holder.query.columns.stream().map(ExperimentalQueryMergeExecutor::getFieldName).collect(Collectors.toSet()).contains(getFieldName(key))) {
+                typedField = holder.queryResolver.resolveField(key);
+                h = holder;
+                break;
+              }
+            }
+          }
+        }
+
+        if (typedField == null) {
+          throw new RuntimeException("Cannot resolve " + e.getKey());
+        }
+
+
+        String orderByField = h == null ? queryRewriter.aliasOrFullExpression(typedField)
+                : SqlUtils.getFieldFullName(h.queryRewriter.cteName(h.cteTableName), h.queryRewriter.fieldName(getFieldName(typedField)));
+        orderList.add(orderByField + " nulls last");
+      }
+      sb.append(String.join(", ", orderList));
+    }
+  }
+
+  static CriteriaDto rewriteJoinCondition(CriteriaDto joinCondition, List<Holder> holders) {
+    Map<String, String> cteByOriginalTableName = new HashMap<>();
+    for (Holder holder : holders) {
+      cteByOriginalTableName.put(holder.originalTableName, holder.cteTableName);
+    }
+
+    if (joinCondition != null) {
+      List<CriteriaDto> children = joinCondition.children;
+      if (children != null && !children.isEmpty()) {
+        for (CriteriaDto child : children) {
+          rewriteJoinCondition(child, holders);
+        }
+      } else {
+        String alias = joinCondition.field.alias();
+        if (alias != null) {
+          joinCondition.field = new AliasedField(alias); // replace with aliased field
+        } else if (joinCondition.field instanceof TableField tf) {
+          joinCondition.field = new TableField(cteByOriginalTableName.get(tf.tableName), tf.fieldName);
+        }
+
+        String otherAlias = joinCondition.fieldOther.alias();
+        if (otherAlias != null) {
+          joinCondition.fieldOther = new AliasedField(otherAlias); // replace with aliased field
+        } else if (joinCondition.fieldOther instanceof TableField tf) {
+          joinCondition.fieldOther = new TableField(cteByOriginalTableName.get(tf.tableName), tf.fieldName);
+        }
+      }
+      return joinCondition;
+    } else {
+      return null;
+    }
+  }
+
+  private static String replaceTableNameByCteNameIfNotNull(Holder holder, String s) {
+    if (holder.originalTableName != null) {
+      s = s.replace(holder.queryRewriter.tableName(holder.originalTableName), holder.queryRewriter.cteName(holder.cteTableName));
+    }
+    return s;
+  }
+
+  private static Holder getHolderOrigin(List<Holder> holders, Field field) {
+    for (Holder holder : holders) {
+      if (field instanceof TableField tf) {
+        if (holder.cteTableName.equals(tf.tableName)) {
+          return holder;
+        }
+      } else if (field instanceof AliasedField af) {
+        for (Field column : holder.query.columns) {
+          if (af.alias().equals(column.alias())) {
+            return holder;
+          }
+        }
+      }
+    }
+    return null;
+  }
+
+  static String getFieldName(Field field) {
+    String alias = field.alias();
+    if (alias != null) {
+      return alias;
+    } else if (field instanceof TableField tf) {
+      return tf.fieldName;
+    } else {
+      throw new IllegalArgumentException("The field " + field + " need to have an alias or of type " + TableField.class);
+    }
+  }
+
+  static String getFieldName(TypedField field) {
+    String alias = field.alias();
+    if (alias != null) {
+      return alias;
+    } else if (field instanceof TableTypedField tf) {
+      return tf.name();
+    } else {
+      throw new IllegalArgumentException("The field " + field + " need to have an alias or of type " + TableTypedField.class);
+    }
+  }
+}
diff --git a/core/src/main/java/io/squashql/type/TableTypedField.java b/core/src/main/java/io/squashql/type/TableTypedField.java
index 323437d..4641520 100644
--- a/core/src/main/java/io/squashql/type/TableTypedField.java
+++ b/core/src/main/java/io/squashql/type/TableTypedField.java
@@ -2,10 +2,12 @@ package io.squashql.type;
 
 import io.squashql.query.CountMeasure;
 import io.squashql.query.database.QueryRewriter;
+import io.squashql.query.database.SqlUtils;
 
 import java.util.Objects;
 
-public record TableTypedField(String store, String name, Class<?> type, String alias) implements TypedField {
+public record TableTypedField(String store, String name, Class<?> type, String alias,
+                              boolean cte) implements TypedField {
 
   public TableTypedField {
     Objects.requireNonNull(name);
@@ -13,7 +15,11 @@ public record TableTypedField(String store, String name, Class<?> type, String a
   }
 
   public TableTypedField(String store, String name, Class<?> type) {
-    this(store, name, type, null);
+    this(store, name, type, null, false);
+  }
+
+  public TableTypedField(String store, String name, Class<?> type, boolean cte) {
+    this(store, name, type, null, cte);
   }
 
   @Override
@@ -21,12 +27,14 @@ public record TableTypedField(String store, String name, Class<?> type, String a
     if (CountMeasure.FIELD_NAME.equals(this.name)) {
       return CountMeasure.FIELD_NAME;
     } else {
-      return qr.getFieldFullName(this);
+      return SqlUtils.getFieldFullName(
+              this.store == null ? null : (this.cte ? qr.cteName(this.store) : qr.tableName(this.store)),
+              qr.fieldName(this.name));
     }
   }
 
   @Override
   public TypedField as(String alias) {
-    return new TableTypedField(this.store, this.name, this.type, alias);
+    return new TableTypedField(this.store, this.name, this.type, alias, this.cte);
   }
 }
diff --git a/duckdb/src/main/java/io/squashql/query/database/DuckDBQueryEngine.java b/duckdb/src/main/java/io/squashql/query/database/DuckDBQueryEngine.java
index 997ed44..11cb404 100644
--- a/duckdb/src/main/java/io/squashql/query/database/DuckDBQueryEngine.java
+++ b/duckdb/src/main/java/io/squashql/query/database/DuckDBQueryEngine.java
@@ -11,7 +11,8 @@ import java.util.function.BiFunction;
 public class DuckDBQueryEngine extends JdbcQueryEngine<DuckDBDatastore> {
 
   /**
-   * https://duckdb.org/docs/sql/aggregates. NOTE there is more but only a subset is proposed here.
+   * <a href="https://duckdb.org/docs/sql/aggregates">https://duckdb.org/docs/sql/aggregates</a>.
+   * NOTE there is more but only a subset is proposed here.
    */
   public static final List<String> SUPPORTED_AGGREGATION_FUNCTIONS = List.of(
           "ANY_VALUE",
@@ -45,6 +46,6 @@ public class DuckDBQueryEngine extends JdbcQueryEngine<DuckDBDatastore> {
 
   @Override
   public QueryRewriter queryRewriter(DatabaseQuery query) {
-    return new DuckDBQueryRewriter(query);
+    return new DuckDBQueryRewriter();
   }
 }
diff --git a/duckdb/src/main/java/io/squashql/query/database/DuckDBQueryRewriter.java b/duckdb/src/main/java/io/squashql/query/database/DuckDBQueryRewriter.java
index 898c5f0..54eaa87 100644
--- a/duckdb/src/main/java/io/squashql/query/database/DuckDBQueryRewriter.java
+++ b/duckdb/src/main/java/io/squashql/query/database/DuckDBQueryRewriter.java
@@ -2,17 +2,6 @@ package io.squashql.query.database;
 
 class DuckDBQueryRewriter implements QueryRewriter {
 
-  private final DatabaseQuery query;
-
-  DuckDBQueryRewriter(DatabaseQuery query) {
-    this.query = query;
-  }
-
-  @Override
-  public DatabaseQuery query() {
-    return this.query;
-  }
-
   @Override
   public String tableName(String table) {
     return SqlUtils.doubleQuoteEscape(table);
diff --git a/js/typescript-library/build-from-query-join.json b/js/typescript-library/build-from-query-join.json
index 2b7b67a..d0ab688 100644
--- a/js/typescript-library/build-from-query-join.json
+++ b/js/typescript-library/build-from-query-join.json
@@ -1,1 +1,1 @@
-{"first":{"table":{"name":"myTable1","joins":[]},"virtualTableDtos":[],"columns":[{"@class":"io.squashql.query.TableField","fullName":"myTable1.a","tableName":"myTable1","fieldName":"a"},{"@class":"io.squashql.query.TableField","fullName":"myTable1.b","tableName":"myTable1","fieldName":"b"},{"@class":"io.squashql.query.TableField","fullName":"myTable1.c","tableName":"myTable1","fieldName":"c"}],"rollupColumns":[],"columnSets":{},"parameters":{},"measures":[{"@class":"io.squashql.query.AggregatedMeasure","field":{"@class":"io.squashql.query.TableField","fullName":"f1","fieldName":"f1"},"aggregationFunction":"sum","alias":"sum"}],"orders":{},"limit":-1},"second":{"table":{"name":"myTable2","joins":[]},"virtualTableDtos":[],"columns":[{"@class":"io.squashql.query.TableField","fullName":"myTable2.b","tableName":"myTable2","fieldName":"b"},{"@class":"io.squashql.query.TableField","fullName":"myTable2.c","tableName":"myTable2","fieldName":"c"}],"rollupColumns":[],"columnSets":{},"parameters":{},"measures":[{"@class":"io.squashql.query.AggregatedMeasure","field":{"@class":"io.squashql.query.TableField","fullName":"f2","fieldName":"f2"},"aggregationFunction":"avg","alias":"sum"}],"orders":{},"limit":-1},"joinType":"LEFT","joinCondition":{"conditionType":"AND","children":[{"field":{"@class":"io.squashql.query.TableField","fullName":"myTable1.b","tableName":"myTable1","fieldName":"b"},"fieldOther":{"@class":"io.squashql.query.TableField","fullName":"myTable2.b","tableName":"myTable2","fieldName":"b"},"conditionType":"EQ"},{"field":{"@class":"io.squashql.query.TableField","fullName":"myTable1.c","tableName":"myTable1","fieldName":"c"},"fieldOther":{"@class":"io.squashql.query.TableField","fullName":"myTable2.c","tableName":"myTable2","fieldName":"c"},"conditionType":"EQ"}]},"orders":{"{\"@class\":\"io.squashql.query.TableField\",\"fullName\":\"myTable1.a\",\"tableName\":\"myTable1\",\"fieldName\":\"a\"}":{"@class":"io.squashql.query.dto.SimpleOrderDto","order":"ASC"}},"limit":10}
\ No newline at end of file
+{"table":{"name":"__cte0__","joins":[{"table":{"name":"__cte1__","joins":[]},"type":"LEFT","joinCriteria":{"conditionType":"AND","children":[{"field":{"@class":"io.squashql.query.TableField","fullName":"myTable1.b","tableName":"myTable1","fieldName":"b"},"fieldOther":{"@class":"io.squashql.query.TableField","fullName":"myTable2.b","tableName":"myTable2","fieldName":"b"},"conditionType":"EQ"},{"field":{"@class":"io.squashql.query.TableField","fullName":"myTable1.c","tableName":"myTable1","fieldName":"c"},"fieldOther":{"@class":"io.squashql.query.TableField","fullName":"myTable2.c","tableName":"myTable2","fieldName":"c"},"conditionType":"EQ"}]}},{"table":{"name":"__cte2__","joins":[]},"type":"INNER"}]},"queries":[{"table":{"name":"myTable1","joins":[]},"virtualTableDtos":[],"columns":[{"@class":"io.squashql.query.TableField","fullName":"myTable1.a","tableName":"myTable1","fieldName":"a"},{"@class":"io.squashql.query.TableField","fullName":"myTable1.b","tableName":"myTable1","fieldName":"b"},{"@class":"io.squashql.query.TableField","fullName":"myTable1.c","tableName":"myTable1","fieldName":"c"}],"rollupColumns":[],"columnSets":{},"parameters":{},"measures":[{"@class":"io.squashql.query.AggregatedMeasure","field":{"@class":"io.squashql.query.TableField","fullName":"f1","fieldName":"f1"},"aggregationFunction":"sum","alias":"sum"}],"orders":{},"limit":-1},{"table":{"name":"myTable2","joins":[]},"virtualTableDtos":[],"columns":[{"@class":"io.squashql.query.TableField","fullName":"myTable2.b","tableName":"myTable2","fieldName":"b"},{"@class":"io.squashql.query.TableField","fullName":"myTable2.c","tableName":"myTable2","fieldName":"c"}],"rollupColumns":[],"columnSets":{},"parameters":{},"measures":[{"@class":"io.squashql.query.AggregatedMeasure","field":{"@class":"io.squashql.query.TableField","fullName":"f2","fieldName":"f2"},"aggregationFunction":"avg","alias":"sum"}],"orders":{},"limit":-1},{"table":{"name":"myTable3","joins":[]},"virtualTableDtos":[],"columns":[{"@class":"io.squashql.query.TableField","fullName":"myTable3.c3","tableName":"myTable3","fieldName":"c3"}],"rollupColumns":[],"columnSets":{},"parameters":{},"measures":[{"@class":"io.squashql.query.AggregatedMeasure","field":{"@class":"io.squashql.query.TableField","fullName":"f3","fieldName":"f3"},"aggregationFunction":"avg","alias":"max"}],"orders":{},"limit":-1}],"orders":{"{\"@class\":\"io.squashql.query.TableField\",\"fullName\":\"myTable1.a\",\"tableName\":\"myTable1\",\"fieldName\":\"a\"}":{"@class":"io.squashql.query.dto.SimpleOrderDto","order":"ASC"},"{\"@class\":\"io.squashql.query.TableField\",\"fullName\":\"myTable3.c3\",\"tableName\":\"myTable3\",\"fieldName\":\"c3\"}":{"@class":"io.squashql.query.dto.SimpleOrderDto","order":"DESC"}},"limit":12}
\ No newline at end of file
diff --git a/js/typescript-library/pom.xml b/js/typescript-library/pom.xml
index 70cadc8..06edd32 100644
--- a/js/typescript-library/pom.xml
+++ b/js/typescript-library/pom.xml
@@ -81,6 +81,7 @@
         </executions>
       </plugin>
       <plugin>
+        <groupId>org.apache.maven.plugins</groupId>
         <artifactId>maven-antrun-plugin</artifactId>
         <version>3.1.0</version>
         <executions>
diff --git a/js/typescript-library/src/generate-json-query-join.ts b/js/typescript-library/src/generate-json-query-join.ts
index f894c44..f952f95 100644
--- a/js/typescript-library/src/generate-json-query-join.ts
+++ b/js/typescript-library/src/generate-json-query-join.ts
@@ -1,10 +1,11 @@
 import * as fs from "fs"
 import {TableField} from "./field"
 import {avg, sum} from "./measure"
-import {JoinType, QueryJoin} from "./query"
+import {JoinType} from "./query"
 import {from} from "./queryBuilder"
-import {all, ConditionType, criterion_} from "./conditions"
 import {OrderKeyword, SimpleOrder} from "./order"
+import {QueryJoin} from "./queryJoin"
+import {all, ConditionType, criterion_} from "./conditions"
 
 export function generateFromQueryJoin() {
   const a = new TableField("myTable1.a")
@@ -20,13 +21,21 @@ export function generateFromQueryJoin() {
           .select([b2, c2], [], [avg("sum", new TableField("f2"))])
           .build()
 
-  const q = new QueryJoin(query1, query2, JoinType.LEFT,
-          all([
-            criterion_(b1, b2, ConditionType.EQ),
-            criterion_(c1, c2, ConditionType.EQ)
-          ]),
-          new Map([[a, new SimpleOrder(OrderKeyword.ASC)]]),
-          10)
+  const c3 = new TableField("myTable3.c3")
+  const query3 = from("myTable3")
+          .select([c3], [], [avg("max", new TableField("f3"))])
+          .build()
+
+  const orders = new Map([[a, new SimpleOrder(OrderKeyword.ASC)], [c3, new SimpleOrder(OrderKeyword.DESC)]])
+  const q = new QueryJoin(query1)
+          .join(query2, JoinType.LEFT,
+                  all([
+                    criterion_(b1, b2, ConditionType.EQ),
+                    criterion_(c1, c2, ConditionType.EQ)
+                  ]))
+          .join(query3, JoinType.INNER)
+          .orderBy(orders)
+          .limit(12)
   const data = JSON.stringify(q)
   fs.writeFileSync('build-from-query-join.json', data)
 }
diff --git a/js/typescript-library/src/index.ts b/js/typescript-library/src/index.ts
index 35b5d87..6429392 100644
--- a/js/typescript-library/src/index.ts
+++ b/js/typescript-library/src/index.ts
@@ -1,7 +1,11 @@
 export {
-  Query, Table, JoinType, QueryMerge, QueryJoin,
+  Query, Table, JoinType, QueryMerge,
 } from './query'
 
+export {
+  QueryJoin,
+} from './queryJoin'
+
 export {
   Order, OrderKeyword, SimpleOrder,
 } from './order'
diff --git a/js/typescript-library/src/querier.ts b/js/typescript-library/src/querier.ts
index 5b1c08f..81ff2cf 100644
--- a/js/typescript-library/src/querier.ts
+++ b/js/typescript-library/src/querier.ts
@@ -1,8 +1,9 @@
 import axios, {AxiosInstance} from "axios"
-import {Query, QueryJoin, QueryMerge} from "./query"
+import {Query, QueryMerge} from "./query"
 import {CreateAxiosDefaults} from "axios/index"
 import {PivotConfig, PivotTableQuery, PivotTableQueryMerge} from "./pivotTableQuery"
 import {Measure} from "./measure"
+import {QueryJoin} from "./queryJoin"
 
 export class Querier {
 
diff --git a/js/typescript-library/src/query.ts b/js/typescript-library/src/query.ts
index b0c4777..2f5ec35 100644
--- a/js/typescript-library/src/query.ts
+++ b/js/typescript-library/src/query.ts
@@ -12,22 +12,6 @@ export class QueryMerge {
   }
 }
 
-export class QueryJoin {
-  constructor(readonly first: Query, readonly second: Query, readonly joinType: JoinType | null, readonly joinCondition: Criteria | null, readonly orders: Map<Field, Order> | null, readonly limit: number = -1) {
-  }
-
-  toJSON() {
-    return {
-      "first": this.first,
-      "second": this.second,
-      "joinType": this.joinType,
-      "joinCondition": this.joinCondition,
-      "orders": Object.fromEntries(serializeMap(this.orders)),
-      "limit": this.limit
-    }
-  }
-}
-
 export class Query {
   columns: Array<Field>
   rollupColumns: Array<Field>
diff --git a/js/typescript-library/src/queryJoin.ts b/js/typescript-library/src/queryJoin.ts
new file mode 100644
index 0000000..e4a26b0
--- /dev/null
+++ b/js/typescript-library/src/queryJoin.ts
@@ -0,0 +1,43 @@
+import {JoinType, Query, Table} from "./query"
+import {Criteria, Field, Order} from "./index"
+import {serializeMap} from "./util"
+
+export class QueryJoin {
+
+  private readonly _table: Table
+  private current: number = 0
+  private _queries: Array<Query>
+  private _orders: Map<Field, Order>
+  private _limit: number = -1
+
+  constructor(query: Query) {
+    this._queries = []
+    this._queries.push(query)
+    this._table = new Table(`__cte${this.current++}__`)
+  }
+
+  join(query: Query, joinType: JoinType, criteria?: Criteria): QueryJoin {
+    this._queries.push(query)
+    this._table.join(new Table(`__cte${this.current++}__`), joinType, criteria)
+    return this
+  }
+
+  orderBy(orders: Map<Field, Order>): QueryJoin {
+    this._orders = orders
+    return this
+  }
+
+  limit(limit: number) {
+    this._limit = limit
+    return this
+  }
+
+  toJSON() {
+    return {
+      "table": this._table,
+      "queries": this._queries,
+      "orders": Object.fromEntries(serializeMap(this._orders)),
+      "limit": this._limit
+    }
+  }
+}
diff --git a/server/src/main/java/io/squashql/spring/web/rest/QueryController.java b/server/src/main/java/io/squashql/spring/web/rest/QueryController.java
index c35f82c..fc67e5b 100644
--- a/server/src/main/java/io/squashql/spring/web/rest/QueryController.java
+++ b/server/src/main/java/io/squashql/spring/web/rest/QueryController.java
@@ -115,13 +115,7 @@ public class QueryController {
 
   @PostMapping(MAPPING_QUERY_JOIN_EXPERIMENTAL)
   public ResponseEntity<QueryResultDto> executeQueryJoin(@RequestBody QueryJoinDto queryJoinDto) {
-    Table table = this.queryExecutor.executeExperimentalQueryMerge(
-            queryJoinDto.first,
-            queryJoinDto.second,
-            queryJoinDto.joinType,
-            queryJoinDto.joinCondition,
-            queryJoinDto.orders,
-            queryJoinDto.limit);
+    Table table = this.queryExecutor.executeExperimentalQueryMerge(queryJoinDto);
     return ResponseEntity.ok(createQueryResultDto(table));
   }
 
diff --git a/snowflake/src/main/java/io/squashql/query/database/SnowflakeQueryEngine.java b/snowflake/src/main/java/io/squashql/query/database/SnowflakeQueryEngine.java
index 1b968e3..4f53c4f 100644
--- a/snowflake/src/main/java/io/squashql/query/database/SnowflakeQueryEngine.java
+++ b/snowflake/src/main/java/io/squashql/query/database/SnowflakeQueryEngine.java
@@ -106,6 +106,6 @@ public class SnowflakeQueryEngine extends JdbcQueryEngine<SnowflakeDatastore> {
 
   @Override
   public QueryRewriter queryRewriter(DatabaseQuery query) {
-    return new SnowflakeQueryRewriter(query);
+    return new SnowflakeQueryRewriter();
   }
 }
diff --git a/snowflake/src/main/java/io/squashql/query/database/SnowflakeQueryRewriter.java b/snowflake/src/main/java/io/squashql/query/database/SnowflakeQueryRewriter.java
index e61c3cb..22b8a24 100644
--- a/snowflake/src/main/java/io/squashql/query/database/SnowflakeQueryRewriter.java
+++ b/snowflake/src/main/java/io/squashql/query/database/SnowflakeQueryRewriter.java
@@ -2,17 +2,6 @@ package io.squashql.query.database;
 
 class SnowflakeQueryRewriter implements QueryRewriter {
 
-  private final DatabaseQuery query;
-
-  SnowflakeQueryRewriter(DatabaseQuery query) {
-    this.query = query;
-  }
-
-  @Override
-  public DatabaseQuery query() {
-    return this.query;
-  }
-
   @Override
   public String tableName(String table) {
     return SqlUtils.doubleQuoteEscape(table);


Test patch:
diff --git a/core/src/test/java/io/squashql/join/TestExperimentalQueryMergeExecutor.java b/core/src/test/java/io/squashql/join/TestExperimentalQueryMergeExecutor.java
new file mode 100644
index 0000000..0d17acd
--- /dev/null
+++ b/core/src/test/java/io/squashql/join/TestExperimentalQueryMergeExecutor.java
@@ -0,0 +1,287 @@
+package io.squashql.join;
+
+import io.squashql.query.TableField;
+import io.squashql.query.builder.Query;
+import io.squashql.query.compiled.CompiledMeasure;
+import io.squashql.query.database.*;
+import io.squashql.query.dto.JoinType;
+import io.squashql.query.dto.QueryDto;
+import io.squashql.query.dto.QueryJoinDto;
+import io.squashql.query.join.ExperimentalQueryMergeExecutor;
+import io.squashql.store.Datastore;
+import io.squashql.store.Store;
+import io.squashql.table.Table;
+import io.squashql.type.TableTypedField;
+import io.squashql.type.TypedField;
+import org.assertj.core.api.Assertions;
+import org.eclipse.collections.api.tuple.Triple;
+import org.junit.jupiter.api.Test;
+
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
+
+import static io.squashql.query.Functions.all;
+import static io.squashql.query.Functions.criterion;
+import static io.squashql.query.dto.ConditionType.EQ;
+
+/**
+ * Test sql generation, not the execution. See {@link io.squashql.query.ATestExperimentalQueryResultMerge}.
+ */
+public class TestExperimentalQueryMergeExecutor {
+
+  static final String storeA = "A";
+  static final String storeB = "B";
+  static final String storeC = "C";
+
+  static final TableField A_a = new TableField(storeA, "a");
+  static final TableField A_b = new TableField(storeA, "b");
+  static final TableField A_c = new TableField(storeA, "c");
+
+  static final TableField B_b = new TableField(storeB, "b");
+  static final TableField B_d = new TableField(storeB, "d");
+  static final TableField B_e = new TableField(storeB, "e");
+  static final TableField B_f = new TableField(storeB, "f");
+
+  static final TableField C_b = new TableField(storeC, "b");
+  static final TableField C_c = new TableField(storeC, "c");
+  static final TableField C_d = new TableField(storeC, "d");
+  static final TableField C_g = new TableField(storeC, "g");
+
+  static final ExperimentalQueryMergeExecutor ex = new ExperimentalQueryMergeExecutor(new FakeQueryEngine());
+
+  static class FakeQueryEngine implements QueryEngine<Datastore> {
+
+    @Override
+    public Table execute(DatabaseQuery query) {
+      return null;
+    }
+
+    @Override
+    public Table executeRawSql(String sql) {
+      return null;
+    }
+
+    @Override
+    public Datastore datastore() {
+      return () -> {
+        Map<String, Store> map = new HashMap<>();
+        try {
+          java.lang.reflect.Field[] fields = TestExperimentalQueryMergeExecutor.class.getDeclaredFields();
+          for (java.lang.reflect.Field field : fields) {
+            Class<?> type = field.getType();
+            if (type.equals(TableField.class)) {
+              TableField f = (TableField) field.get(null);
+              map.computeIfAbsent(f.tableName, k -> new Store(f.tableName, new ArrayList<>()))
+                      .fields()
+                      .add(new TableTypedField(f.tableName, f.fieldName, Object.class));
+            }
+          }
+          return map;
+        } catch (Exception e) {
+          throw new RuntimeException(e);
+        }
+      };
+    }
+
+    @Override
+    public List<String> supportedAggregationFunctions() {
+      return null;
+    }
+
+    @Override
+    public QueryRewriter queryRewriter(DatabaseQuery query) {
+      return new DefaultQueryRewriter(query) {
+
+        static final Pattern p = Pattern.compile("__cte([0-9])__");
+
+        @Override
+        public String cteName(String cteName) {
+          Matcher matcher = p.matcher(cteName);
+          if (matcher.find()) {
+            return super.cteName("CTE" + matcher.group(1).toUpperCase()); // to make sure it differs from tableName()
+          } else {
+            throw new RuntimeException("incorrect cteName");
+          }
+        }
+      };
+    }
+  }
+
+  @Test
+  void testCorrectColumnsAreKeptInSelectFromLeftToRight() {
+    QueryDto queryA = Query
+            .from(storeA)
+            .select(List.of(A_a, A_b, A_c), List.of())
+            .build();
+
+    QueryDto queryB = Query
+            .from(storeB)
+            .select(List.of(B_b, B_d, B_e, B_f), List.of())
+            .build();
+
+    QueryDto queryC = Query
+            .from(storeC)
+            .select(List.of(C_b, C_c, C_d, C_g), List.of())
+            .build();
+
+    QueryJoinDto queryJoin = QueryJoinDto.from(queryA)
+            .join(queryB, JoinType.LEFT, criterion(A_b, B_b, EQ))
+            .join(queryC, JoinType.LEFT, all(
+                    criterion(A_b, C_b, EQ),
+                    criterion(A_c, C_c, EQ),
+                    criterion(B_d, C_d, EQ)
+            ))
+            .limit(12);
+
+    Triple<String, List<TypedField>, List<CompiledMeasure>> result = ex.generateSql(queryJoin);
+    String expectedSql = "with `CTE0` as (" +
+            "select `A`.`a`, `A`.`b`, `A`.`c` from `A` group by `A`.`a`, `A`.`b`, `A`.`c`), " +
+            "`CTE1` as (" +
+            "select `B`.`b`, `B`.`d`, `B`.`e`, `B`.`f` from `B` group by `B`.`b`, `B`.`d`, `B`.`e`, `B`.`f`), " +
+            "`CTE2` as (" +
+            "select `C`.`b`, `C`.`c`, `C`.`d`, `C`.`g` from `C` group by `C`.`b`, `C`.`c`, `C`.`d`, `C`.`g`) " +
+            "select `CTE0`.`a`, `CTE0`.`b`, `CTE0`.`c`, `CTE1`.`d`, `CTE1`.`e`, `CTE1`.`f`, `CTE2`.`g` " +
+            "from `CTE0` left join `CTE1` on `CTE0`.`b` = `CTE1`.`b` " +
+            "left join `CTE2` on (`CTE0`.`b` = `CTE2`.`b` and `CTE0`.`c` = `CTE2`.`c` and `CTE1`.`d` = `CTE2`.`d`) " +
+            "limit 12";
+    Assertions.assertThat(result.getOne()).isEqualTo(expectedSql);
+    List<String> selectElements = result.getTwo().stream().map(SqlUtils::squashqlExpression).toList();
+    Assertions.assertThat(selectElements).containsExactly("A.a", "A.b", "A.c", "B.d", "B.e", "B.f", "C.g");
+  }
+
+  @Test
+  void testNoColumnInCommonWithoutCondition() {
+    QueryDto queryA = Query
+            .from(storeA)
+            .select(List.of(A_a, A_b, A_c), List.of())
+            .build();
+
+    QueryDto queryB = Query
+            .from(storeB)
+            .select(List.of(B_b, B_d, B_e, B_f), List.of())
+            .build();
+
+    QueryJoinDto queryJoin = QueryJoinDto.from(queryA)
+            .join(queryB, JoinType.CROSS)
+            .limit(12);
+
+    Triple<String, List<TypedField>, List<CompiledMeasure>> result = ex.generateSql(queryJoin);
+    String expectedSql = "with `CTE0` as (" +
+            "select `A`.`a`, `A`.`b`, `A`.`c` from `A` group by `A`.`a`, `A`.`b`, `A`.`c`), " +
+            "`CTE1` as (" +
+            "select `B`.`b`, `B`.`d`, `B`.`e`, `B`.`f` from `B` group by `B`.`b`, `B`.`d`, `B`.`e`, `B`.`f`) " +
+            "select `CTE0`.`a`, `CTE0`.`b`, `CTE0`.`c`, `CTE1`.`b`, `CTE1`.`d`, `CTE1`.`e`, `CTE1`.`f` " +
+            "from `CTE0` cross join `CTE1` " +
+            "limit 12";
+    Assertions.assertThat(result.getOne()).isEqualTo(expectedSql);
+    List<String> selectElements = result.getTwo().stream().map(SqlUtils::squashqlExpression).toList();
+    Assertions.assertThat(selectElements).containsExactly("A.a", "A.b", "A.c", "B.b", "B.d", "B.e", "B.f");
+  }
+
+  @Test
+  void testColumnInCommonWithAliasWithoutCondition() {
+    QueryDto queryA = Query
+            .from(storeA)
+            .select(List.of(A_a, A_b.as("alias_b"), A_c), List.of())
+            .build();
+
+    QueryDto queryB = Query
+            .from(storeB)
+            .select(List.of(B_b.as("alias_b"), B_d, B_e, B_f), List.of())
+            .build();
+
+    // Should be a condition on "alias_b"
+    QueryJoinDto queryJoin = QueryJoinDto.from(queryA)
+            .join(queryB, JoinType.LEFT)
+            .limit(12);
+
+    Triple<String, List<TypedField>, List<CompiledMeasure>> result = ex.generateSql(queryJoin);
+    String expectedSql = "with `CTE0` as (" +
+            "select `A`.`a`, `A`.`b` as `alias_b`, `A`.`c` from `A` group by `A`.`a`, `alias_b`, `A`.`c`), " +
+            "`CTE1` as (" +
+            "select `B`.`b` as `alias_b`, `B`.`d`, `B`.`e`, `B`.`f` from `B` group by `alias_b`, `B`.`d`, `B`.`e`, `B`.`f`) " +
+            "select `CTE0`.`a`, `CTE0`.`alias_b` as `alias_b`, `CTE0`.`c`, `CTE1`.`d`, `CTE1`.`e`, `CTE1`.`f` " +
+            "from `CTE0` left join `CTE1` on `CTE0`.`alias_b` = `CTE1`.`alias_b` " +
+            "limit 12";
+    Assertions.assertThat(result.getOne()).isEqualTo(expectedSql);
+    List<String> selectElements = result.getTwo().stream().map(SqlUtils::squashqlExpression).toList();
+    Assertions.assertThat(selectElements).containsExactly("A.a", "alias_b", "A.c", "B.d", "B.e", "B.f");
+  }
+
+  @Test
+  void testColumnInCommonWithSameNameWithoutCondition() {
+    QueryDto queryA = Query
+            .from(storeA)
+            .select(List.of(A_a, A_b, A_c), List.of())
+            .build();
+
+    QueryDto queryB = Query
+            .from(storeA)
+            .select(List.of(A_b, A_c), List.of())
+            .build();
+
+    // Should be a condition on "alias_b"
+    QueryJoinDto queryJoin = QueryJoinDto.from(queryA)
+            .join(queryB, JoinType.LEFT)
+            .limit(12);
+
+    Triple<String, List<TypedField>, List<CompiledMeasure>> result = ex.generateSql(queryJoin);
+    String expectedSql = "with `CTE0` as (" +
+            "select `A`.`a`, `A`.`b`, `A`.`c` from `A` group by `A`.`a`, `A`.`b`, `A`.`c`), " +
+            "`CTE1` as (" +
+            "select `A`.`b`, `A`.`c` from `A` group by `A`.`b`, `A`.`c`) " +
+            "select `CTE0`.`a`, `CTE0`.`b`, `CTE0`.`c` " +
+            "from `CTE0` left join `CTE1` on (`CTE0`.`b` = `CTE1`.`b` and `CTE0`.`c` = `CTE1`.`c`) " +
+            "limit 12";
+    Assertions.assertThat(result.getOne()).isEqualTo(expectedSql);
+    List<String> selectElements = result.getTwo().stream().map(SqlUtils::squashqlExpression).toList();
+    Assertions.assertThat(selectElements).containsExactly("A.a", "A.b", "A.c");
+  }
+
+  @Test
+  void testColumnInCommonWithSameNameAndWithCondition() {
+    QueryDto queryA = Query
+            .from(storeA)
+            .select(List.of(A_a, A_b.as("alias_b"), A_c), List.of())
+            .build();
+
+    QueryDto queryB = Query
+            .from(storeB)
+            .select(List.of(B_b.as("alias_b"), B_d, B_e, B_f), List.of())
+            .build();
+
+    QueryDto queryC = Query
+            .from(storeC)
+            .select(List.of(C_b, C_c, C_d, C_g), List.of())
+            .build();
+
+    QueryJoinDto queryJoin = QueryJoinDto.from(queryA)
+            .join(queryB, JoinType.LEFT) // No condition on query, it should guess the condition on "alias_b"
+            .join(queryC, JoinType.LEFT, all(
+                    criterion(A_b, C_b, EQ),
+                    criterion(A_c, C_c, EQ),
+                    criterion(B_d, C_d, EQ)
+            ))
+            .limit(12);
+
+    Triple<String, List<TypedField>, List<CompiledMeasure>> result = ex.generateSql(queryJoin);
+    String expectedSql = "with `CTE0` as (" +
+            "select `A`.`a`, `A`.`b` as `alias_b`, `A`.`c` from `A` group by `A`.`a`, `alias_b`, `A`.`c`), " +
+            "`CTE1` as (" +
+            "select `B`.`b` as `alias_b`, `B`.`d`, `B`.`e`, `B`.`f` from `B` group by `alias_b`, `B`.`d`, `B`.`e`, `B`.`f`), " +
+            "`CTE2` as (" +
+            "select `C`.`b`, `C`.`c`, `C`.`d`, `C`.`g` from `C` group by `C`.`b`, `C`.`c`, `C`.`d`, `C`.`g`) " +
+            "select `CTE0`.`a`, `CTE0`.`alias_b` as `alias_b`, `CTE0`.`c`, `CTE1`.`d`, `CTE1`.`e`, `CTE1`.`f`, `CTE2`.`g` " +
+            "from `CTE0` " +
+            "left join `CTE1` on `CTE0`.`alias_b` = `CTE1`.`alias_b` " +
+            "left join `CTE2` on (`CTE0`.`b` = `CTE2`.`b` and `CTE0`.`c` = `CTE2`.`c` and `CTE1`.`d` = `CTE2`.`d`) " +
+            "limit 12";
+    Assertions.assertThat(result.getOne()).isEqualTo(expectedSql);
+    List<String> selectElements = result.getTwo().stream().map(SqlUtils::squashqlExpression).toList();
+    Assertions.assertThat(selectElements).containsExactly("A.a", "alias_b", "A.c", "B.d", "B.e", "B.f", "C.g");
+  }
+}
diff --git a/core/src/test/java/io/squashql/query/ATestExperimentalQueryResultMerge.java b/core/src/test/java/io/squashql/query/ATestExperimentalQueryResultMerge.java
index 5286eb3..83443fa 100644
--- a/core/src/test/java/io/squashql/query/ATestExperimentalQueryResultMerge.java
+++ b/core/src/test/java/io/squashql/query/ATestExperimentalQueryResultMerge.java
@@ -3,6 +3,7 @@ package io.squashql.query;
 import io.squashql.TestClass;
 import io.squashql.query.builder.Query;
 import io.squashql.query.dto.*;
+import io.squashql.query.dto.QueryJoinDto;
 import io.squashql.table.Table;
 import io.squashql.type.TableTypedField;
 import org.assertj.core.api.Assertions;
@@ -22,6 +23,7 @@ public abstract class ATestExperimentalQueryResultMerge extends ABaseTestQuery {
 
   String storeA = "StoreA" + getClass().getSimpleName().toLowerCase();
   String storeB = "StoreB" + getClass().getSimpleName().toLowerCase();
+  String storeC = "StoreC" + getClass().getSimpleName().toLowerCase();
   Field category = new TableField(this.storeA, "category");
   Field idA = new TableField(this.storeA, "idA");
   Field idStoreA = new TableField(this.storeA, "id");
@@ -29,8 +31,12 @@ public abstract class ATestExperimentalQueryResultMerge extends ABaseTestQuery {
   Field idB = new TableField(this.storeB, "idB");
   Field idStoreB = new TableField(this.storeB, "id");
   Field priceB = new TableField(this.storeB, "priceB");
+  Field idC = new TableField(this.storeC, "idC");
+  Field idStoreC = new TableField(this.storeC, "id");
+  Field priceC = new TableField(this.storeC, "priceC");
   Measure priceASum = Functions.sum("priceA", this.priceA);
   Measure priceBSum = Functions.sum("priceB", this.priceB);
+  Measure priceCSum = Functions.sum("priceC", this.priceC);
 
   @Override
   protected Map<String, List<TableTypedField>> getFieldsByStore() {
@@ -42,7 +48,14 @@ public abstract class ATestExperimentalQueryResultMerge extends ABaseTestQuery {
     TableTypedField idB = new TableTypedField(this.storeB, "idB", String.class);
     TableTypedField idStoreB = new TableTypedField(this.storeB, "id", String.class);
     TableTypedField priceB = new TableTypedField(this.storeB, "priceB", double.class);
-    return Map.of(this.storeA, List.of(category, idA, idStoreA, priceA), this.storeB, List.of(idB, idStoreB, priceB));
+
+    TableTypedField idC = new TableTypedField(this.storeC, "idC", String.class);
+    TableTypedField idStoreC = new TableTypedField(this.storeC, "id", String.class);
+    TableTypedField priceC = new TableTypedField(this.storeC, "priceC", double.class);
+    return Map.of(
+            this.storeA, List.of(category, idA, idStoreA, priceA),
+            this.storeB, List.of(idB, idStoreB, priceB),
+            this.storeC, List.of(idC, idStoreC, priceC));
   }
 
   @Override
@@ -58,6 +71,11 @@ public abstract class ATestExperimentalQueryResultMerge extends ABaseTestQuery {
             new Object[]{"1", "1", 20d},
             new Object[]{"2", "2", 30d}
     ));
+    this.tm.load(this.storeC, List.of(
+            new Object[]{"0", "0", 123d},
+            new Object[]{"1", "1", 42d},
+            new Object[]{"2", "2", 321d}
+    ));
   }
 
   @Test
@@ -76,11 +94,10 @@ public abstract class ATestExperimentalQueryResultMerge extends ABaseTestQuery {
     Map<Field, OrderDto> orders = new LinkedHashMap<>(); // order matters
     orders.put(this.category, asc);
     orders.put(this.idA, asc);
-    Table result = this.executor.executeExperimentalQueryMerge(
-            queryL, queryR, JoinType.LEFT,
-            criterion(this.idB, this.idA, ConditionType.EQ),
-            orders,
-            -1);
+    QueryJoinDto jq = QueryJoinDto.from(queryL).join(queryR, JoinType.LEFT, criterion(this.idB, this.idA, ConditionType.EQ))
+            .orderBy(orders)
+            .limit(-1);
+    Table result = this.executor.executeExperimentalQueryMerge(jq);
     Assertions.assertThat(result.headers().stream().map(Header::name).toList())
             .containsExactly(this.storeA + ".category", this.storeA + ".idA", "priceA", "priceB");
     Assertions.assertThat(result).containsExactly(
@@ -89,11 +106,7 @@ public abstract class ATestExperimentalQueryResultMerge extends ABaseTestQuery {
             List.of("B", "0", 3d, 10d),
             Arrays.asList("B", "3", 4d, getDoubleNullJoinValue()));
 
-    result = this.executor.executeExperimentalQueryMerge(
-            queryL, queryR, JoinType.LEFT,
-            criterion(this.idB, this.idA, ConditionType.EQ),
-            orders,
-            1); // with limit
+    result = this.executor.executeExperimentalQueryMerge(jq.limit(1)); // with limit
     Assertions.assertThat(result).containsExactly(List.of("A", "0", 1d, 10d));
   }
 
@@ -110,11 +123,9 @@ public abstract class ATestExperimentalQueryResultMerge extends ABaseTestQuery {
             .build();
 
     SimpleOrderDto asc = new SimpleOrderDto(OrderKeywordDto.ASC);
-    Table result = this.executor.executeExperimentalQueryMerge(
-            queryL, queryR, JoinType.LEFT,
-            criterion(this.idB, this.idA, ConditionType.EQ),
-            Map.of(this.idA, asc),
-            -1);
+    QueryJoinDto jq = QueryJoinDto.from(queryL).join(queryR, JoinType.LEFT, criterion(this.idB, this.idA, ConditionType.EQ))
+            .orderBy(Map.of(this.idA, asc));
+    Table result = this.executor.executeExperimentalQueryMerge(jq);
     Assertions.assertThat(result.headers().stream().map(Header::name).toList())
             .containsExactly(this.storeA + ".idA", "priceA", "priceB");
     Assertions.assertThat(result).containsExactly(
@@ -136,11 +147,10 @@ public abstract class ATestExperimentalQueryResultMerge extends ABaseTestQuery {
             .build();
 
     SimpleOrderDto asc = new SimpleOrderDto(OrderKeywordDto.ASC);
-    Table result = this.executor.executeExperimentalQueryMerge(
-            queryL, queryR, JoinType.LEFT,
-            criterion(this.idStoreB, this.idStoreA, ConditionType.EQ),
-            Map.of(this.idStoreA, asc),
-            -1);
+    QueryJoinDto jq = QueryJoinDto.from(queryL)
+            .join(queryR, JoinType.LEFT, criterion(this.idStoreB, this.idStoreA, ConditionType.EQ))
+            .orderBy(Map.of(this.idStoreA, asc));
+    Table result = this.executor.executeExperimentalQueryMerge(jq);
     Assertions.assertThat(result.headers().stream().map(Header::name).toList())
             .containsExactly(this.storeA + ".id", "priceA", "priceB");
     Assertions.assertThat(result).containsExactly(
@@ -170,12 +180,10 @@ public abstract class ATestExperimentalQueryResultMerge extends ABaseTestQuery {
     SimpleOrderDto asc = new SimpleOrderDto(OrderKeywordDto.ASC);
     Map<Field, OrderDto> orders = new LinkedHashMap<>(); // order matters
     orders.put(categoryAliased, asc);
-    orders.put(idStoreBAliased, asc);
-    Table result = this.executor.executeExperimentalQueryMerge(
-            queryL, queryR, JoinType.LEFT,
-            criterion(idStoreBAliased, this.idStoreA, ConditionType.EQ), // use the aliased in the join condition
-            orders,  // use the aliased in the order by condition
-            -1);
+    orders.put(this.idStoreA, asc);
+    QueryJoinDto jq = QueryJoinDto.from(queryL)
+            .join(queryR, JoinType.LEFT, criterion(idStoreBAliased, this.idStoreA, ConditionType.EQ)); // use the aliased in the join condition
+    Table result = this.executor.executeExperimentalQueryMerge(jq.orderBy(orders));
     Assertions.assertThat(result.headers().stream().map(Header::name).toList())
             .containsExactly("category_aliased", this.storeA + ".id", "priceA", "priceB");
     Assertions.assertThat(result).containsExactly(
@@ -198,11 +206,8 @@ public abstract class ATestExperimentalQueryResultMerge extends ABaseTestQuery {
             .build();
 
     SimpleOrderDto asc = new SimpleOrderDto(OrderKeywordDto.ASC);
-    Table result = this.executor.executeExperimentalQueryMerge(
-            queryL, queryR, JoinType.LEFT,
-            all(criterion(this.idB, this.idA, ConditionType.EQ), criterion(this.idStoreB, this.idStoreA, ConditionType.EQ)),
-            Map.of(this.idA, asc),
-            -1);
+    QueryJoinDto jq = QueryJoinDto.from(queryL).join(queryR, JoinType.LEFT, all(criterion(this.idB, this.idA, ConditionType.EQ), criterion(this.idStoreB, this.idStoreA, ConditionType.EQ)));
+    Table result = this.executor.executeExperimentalQueryMerge(jq.orderBy(Map.of(this.idA, asc)));
     Assertions.assertThat(result.headers().stream().map(Header::name).toList())
             .containsExactly(this.storeA + ".idA", this.storeA + ".id", "priceA", "priceB");
     Assertions.assertThat(result).containsExactly(
@@ -228,11 +233,8 @@ public abstract class ATestExperimentalQueryResultMerge extends ABaseTestQuery {
             .build();
 
     SimpleOrderDto asc = new SimpleOrderDto(OrderKeywordDto.ASC);
-    Table result = this.executor.executeExperimentalQueryMerge(
-            queryL, queryR, JoinType.LEFT,
-            null,
-            Map.of(idA, asc),
-            -1);
+    QueryJoinDto jq = QueryJoinDto.from(queryL).join(queryR, JoinType.LEFT);
+    Table result = this.executor.executeExperimentalQueryMerge(jq.orderBy(Map.of(idA, asc)));
     Assertions.assertThat(result.headers().stream().map(Header::name).toList())
             .containsExactly(firstKey, secondKey, "priceA", "priceB");
     Assertions.assertThat(result).containsExactly(
@@ -253,12 +255,8 @@ public abstract class ATestExperimentalQueryResultMerge extends ABaseTestQuery {
             .select(List.of(), List.of(this.priceBSum))
             .build();
 
-    SimpleOrderDto asc = new SimpleOrderDto(OrderKeywordDto.ASC);
-    Table result = this.executor.executeExperimentalQueryMerge(
-            queryL, queryR, JoinType.CROSS,
-            null,
-            null,
-            -1);
+    QueryJoinDto jq = QueryJoinDto.from(queryL).join(queryR, JoinType.CROSS);
+    Table result = this.executor.executeExperimentalQueryMerge(jq);
     Assertions.assertThat(result.headers().stream().map(Header::name).toList())
             .containsExactly("priceA", "priceB");
     Assertions.assertThat(result).containsExactly(List.of(10d, 60d));
@@ -290,11 +288,8 @@ public abstract class ATestExperimentalQueryResultMerge extends ABaseTestQuery {
 
     // In case of sub-queries, aliases must be used everywhere.
     SimpleOrderDto asc = new SimpleOrderDto(OrderKeywordDto.ASC);
-    Table result = this.executor.executeExperimentalQueryMerge(
-            queryL, queryR, JoinType.LEFT,
-            criterion(idAliasedB, idAliasedA, ConditionType.EQ),
-            Map.of(idAliasedA, asc),
-            -1);
+    QueryJoinDto jq = QueryJoinDto.from(queryL).join(queryR, JoinType.LEFT, criterion(idAliasedB, idAliasedA, ConditionType.EQ));
+    Table result = this.executor.executeExperimentalQueryMerge(jq.orderBy(Map.of(idAliasedA, asc)));
     Assertions.assertThat(result.headers().stream().map(Header::name).toList())
             .containsExactly(idAliasedA.alias(), "priceA2", "priceB2");
     Assertions.assertThat(result).containsExactly(
@@ -303,6 +298,76 @@ public abstract class ATestExperimentalQueryResultMerge extends ABaseTestQuery {
             Arrays.asList("3", 4d, getDoubleNullJoinValue()));
   }
 
+  @Test
+  void testJoinWithMultipleQueries() {
+    QueryDto queryA = Query
+            .from(this.storeA)
+            .select(List.of(this.category, this.idA), List.of(this.priceASum))
+            .build();
+
+    QueryDto queryB = Query
+            .from(this.storeB)
+            .select(List.of(this.idB), List.of(this.priceBSum))
+            .build();
+
+    QueryDto queryC = Query
+            .from(this.storeC)
+            .select(List.of(this.idC), List.of(this.priceCSum))
+            .build();
+
+    QueryJoinDto jq = QueryJoinDto.from(queryA)
+            .join(queryB, JoinType.INNER, criterion(this.idB, this.idA, ConditionType.EQ))
+            .join(queryC, JoinType.LEFT, criterion(this.idB, this.idC, ConditionType.EQ));
+    SimpleOrderDto asc = new SimpleOrderDto(OrderKeywordDto.ASC);
+    Map<Field, OrderDto> orders = new LinkedHashMap<>(); // order matters
+    orders.put(this.category, asc);
+    orders.put(this.idA, asc);
+    Table result = this.executor.executeExperimentalQueryMerge(jq.orderBy(orders));
+    Assertions.assertThat(result.headers().stream().map(Header::name).toList())
+            .containsExactly(this.storeA + ".category", this.storeA + ".idA", "priceA", "priceB", "priceC");
+    Assertions.assertThat(result).containsExactly(
+            List.of("A", "0", 1d, 10d, 123d),
+            List.of("A", "1", 2d, 20d, 42d),
+            Arrays.asList("B", "0", 3d, 10d, 123d));
+  }
+
+  @Test
+  void testLeftJoinWithoutCriteriaWithColumnsInCommonUsingSubquery() {
+    QueryDto queryL = Query
+            .from(this.storeA)
+            .select(List.of(this.idStoreA), List.of(this.priceASum))
+            .build();
+
+    // This query does not make sense, but it is to make sure there is no issue when using sub-query.
+    queryL = Query.from(queryL)
+            .select(List.of(new TableField("id")) /* We can also use AliasedField */, List.of(sum("priceA2", new AliasedField(this.priceASum.alias()))))
+            .build();
+
+    QueryDto queryR = Query
+            .from(this.storeB)
+            .select(List.of(this.idStoreB), List.of(this.priceBSum))
+            .build();
+
+    queryR = Query
+            .from(queryR)
+            .select(List.of(new TableField("id")), List.of(sum("priceB2", new AliasedField(this.priceBSum.alias()))))
+            .build();
+
+    SimpleOrderDto asc = new SimpleOrderDto(OrderKeywordDto.ASC);
+    Map<Field, OrderDto> orders = new LinkedHashMap<>(); // order matters
+    orders.put(new TableField("id"), asc);
+
+    // In case of sub-queries, aliases must be used everywhere.
+    QueryJoinDto jq = QueryJoinDto.from(queryL).join(queryR, JoinType.LEFT);
+    Table result = this.executor.executeExperimentalQueryMerge(jq.orderBy(orders));
+    Assertions.assertThat(result.headers().stream().map(Header::name).toList())
+            .containsExactly("id", "priceA2", "priceB2");
+    Assertions.assertThat(result).containsExactly(
+            List.of("0", 4d, 10d),
+            List.of("1", 2d, 20d),
+            Arrays.asList("3", 4d, getDoubleNullJoinValue()));
+  }
+
   /**
    * Returns the value of a double when the join fails.
    */
diff --git a/core/src/test/java/io/squashql/query/TestSQLTranslator.java b/core/src/test/java/io/squashql/query/TestSQLTranslator.java
index 69f1851..6da4c01 100644
--- a/core/src/test/java/io/squashql/query/TestSQLTranslator.java
+++ b/core/src/test/java/io/squashql/query/TestSQLTranslator.java
@@ -4,15 +4,15 @@ import io.squashql.query.compiled.CompiledMeasure;
 import io.squashql.query.database.*;
 import io.squashql.query.dto.*;
 import io.squashql.store.Store;
-import io.squashql.type.*;
+import io.squashql.type.TableTypedField;
+import io.squashql.type.TypedField;
 import lombok.AllArgsConstructor;
 import org.assertj.core.api.Assertions;
 import org.junit.jupiter.api.Test;
 
-import java.util.Collections;
+import java.util.HashMap;
 import java.util.List;
 import java.util.Map;
-import java.util.function.Function;
 
 import static io.squashql.query.Functions.*;
 import static io.squashql.query.TableField.tableField;
@@ -25,37 +25,53 @@ public class TestSQLTranslator {
   private static final String BASE_STORE_NAME = "baseStore";
 
   private static class TestResolver extends QueryResolver {
+    private static final Map<String, Store> stores = new HashMap<>();
+
+    static {
+      stores.put(BASE_STORE_NAME, new Store(BASE_STORE_NAME, List.of(
+              new TableTypedField(BASE_STORE_NAME, "id", String.class),
+              new TableTypedField(BASE_STORE_NAME, "type", String.class),
+              new TableTypedField(BASE_STORE_NAME, "delta", double.class),
+              new TableTypedField(BASE_STORE_NAME, "pnl", double.class),
+              new TableTypedField(BASE_STORE_NAME, "country", String.class),
+              new TableTypedField(BASE_STORE_NAME, "a", String.class),
+              new TableTypedField(BASE_STORE_NAME, "b", String.class),
+              new TableTypedField(BASE_STORE_NAME, "c", String.class),
+              new TableTypedField(BASE_STORE_NAME, "c1", String.class),
+              new TableTypedField(BASE_STORE_NAME, "c2", String.class),
+              new TableTypedField(BASE_STORE_NAME, "c3", String.class),
+              new TableTypedField(BASE_STORE_NAME, "scenario", String.class))));
+
+      String recommendation = "recommendation";
+      stores.put(recommendation, new Store(recommendation, List.of(
+              new TableTypedField(recommendation, "finalprice", double.class),
+              new TableTypedField(recommendation, "recoprice", double.class),
+              new TableTypedField(recommendation, "initial_price", double.class))));
+      String product = "product";
+      stores.put(product, new Store(product, List.of(
+              new TableTypedField(product, "tva_rate", double.class))));
+
+      stores.put("table1", new Store("table1", List.of(
+              new TableTypedField("table1", "table1_id", String.class),
+              new TableTypedField("table1", "table1_field_2", String.class),
+              new TableTypedField("table1", "table1_field_3", String.class))));
+      stores.put("table2", new Store("table2", List.of(
+              new TableTypedField("table2", "table2_id", String.class),
+              new TableTypedField("table2", "table2_field_1", String.class))));
+      stores.put("table3", new Store("table3", List.of(
+              new TableTypedField("table3", "table3_id", String.class))));
+      stores.put("table4", new Store("table4", List.of(
+              new TableTypedField("table4", "table4_id_1", String.class),
+              new TableTypedField("table4", "table4_id_2", String.class))));
+    }
+
     private final int limit;
 
     public TestResolver(QueryDto query) {
-      super(query, Collections.emptyMap());
+      super(query, stores);
       this.limit = query.limit;
     }
 
-    @Override
-    protected TypedField resolveField(Field field) {
-      Function<String, Class<?>> type = f -> switch (f) {
-        case "pnl", BASE_STORE_NAME + "." + "pnl" -> double.class;
-        case "delta", BASE_STORE_NAME + "." + "delta" -> Double.class;
-        default -> String.class;
-      };
-      if (field instanceof BinaryOperationField bf) {
-        return new BinaryOperationTypedField(bf.operator, resolveField(bf.leftOperand), resolveField(bf.rightOperand), field.alias());
-      } else if (field instanceof ConstantField cf) {
-        return new ConstantTypedField(cf.value);
-      } else if (field instanceof AliasedField) {
-        return new AliasedTypedField(field.alias());
-      }
-      String[] split = field.name().split("\\.");
-      if (split.length > 1) {
-        String tableName = split[0];
-        String fieldNameInTable = split[1];
-        return new TableTypedField(tableName, fieldNameInTable, type.apply(fieldNameInTable), field.alias());
-      } else {
-        return new TableTypedField(null, split[0], type.apply(split[0]), field.alias());
-      }
-    }
-
     DatabaseQuery toDatabaseQuery() {
       final DatabaseQuery compiled = toDatabaseQuery(getScope(), this.limit);
       // Keep the order
@@ -77,13 +93,6 @@ public class TestSQLTranslator {
   @AllArgsConstructor
   static class SQLTranslatorQueryRewriter implements QueryRewriter {
 
-    final DatabaseQuery query;
-
-    @Override
-    public DatabaseQuery query() {
-      return this.query;
-    }
-
     @Override
     public String escapeAlias(String alias) {
       return SqlUtils.backtickEscape(alias);
@@ -112,7 +121,7 @@ public class TestSQLTranslator {
   }
 
   public static String translate(DatabaseQuery query) {
-    return SQLTranslator.translate(query, new SQLTranslatorQueryRewriter(query));
+    return SQLTranslator.translate(query, new SQLTranslatorQueryRewriter());
   }
 
   private DatabaseQuery compileQuery(QueryDto query) {
@@ -371,7 +380,7 @@ public class TestSQLTranslator {
             .withMeasure(sum("sum GT", "mean"))
             .withWhereCriteria(criterion("type", eq("myType")));
     Assertions.assertThat(translate(compileQuery(query)))
-            .isEqualTo("select `c3`, sum(`alias_c1`) as `sum c1`, sum(`mean`) as `sum GT` from (select `c3`, `c1` AS `alias_c1`, avg(`c2`) as `mean` from `dataset.a` group by `c3`, `alias_c1`) where `type` = 'myType' group by `c3`");
+            .isEqualTo("select `c3`, sum(`alias_c1`) as `sum c1`, sum(`mean`) as `sum GT` from (select `c3`, `c1` as `alias_c1`, avg(`c2`) as `mean` from `dataset.a` group by `c3`, `alias_c1`) where `type` = 'myType' group by `c3`");
   }
 
   @Test
@@ -509,7 +518,7 @@ public class TestSQLTranslator {
     DefaultQueryRewriter qr = new DefaultQueryRewriter(null);
     String expression = compiledMeasure(measure).sqlExpression(qr, true);
     Assertions.assertThat(expression)
-            .isEqualTo("sum(case when (`recommendation`.`finalprice` > `recommendation`.`recoprice` and `recommendation`.`recoprice` > '0')" +
+            .isEqualTo("sum(case when (`recommendation`.`finalprice` > `recommendation`.`recoprice` and `recommendation`.`recoprice` > 0)" +
                     " then (`recommendation`.`finalprice`-`recommendation`.`recoprice`) end)" +
                     " as `increase_sum`");
 
@@ -524,15 +533,15 @@ public class TestSQLTranslator {
   @Test
   void testComplexFieldCalculation() {
     DefaultQueryRewriter qr = new DefaultQueryRewriter(null);
-    Field f1 = new TableField("f1");
-    Field f2 = new TableField("f2");
-    Field f3 = new TableField("f3");
+    Field a = new TableField("a");
+    Field b = new TableField("b");
+    Field c = new TableField("c");
 
-    BinaryOperationField f1_minus_f2 = new BinaryOperationField(BinaryOperator.MINUS, f1, f2);
-    BinaryOperationField divide = new BinaryOperationField(BinaryOperator.DIVIDE, f1_minus_f2, f1);
-    BinaryOperationField multiply = new BinaryOperationField(BinaryOperator.MULTIPLY, divide, f3);
+    BinaryOperationField f1_minus_f2 = new BinaryOperationField(BinaryOperator.MINUS, a, b);
+    BinaryOperationField divide = new BinaryOperationField(BinaryOperator.DIVIDE, f1_minus_f2, a);
+    BinaryOperationField multiply = new BinaryOperationField(BinaryOperator.MULTIPLY, divide, c);
     BinaryOperationField plus = new BinaryOperationField(BinaryOperator.PLUS, multiply, new ConstantField(2));
     Assertions.assertThat(compileField(plus).sqlExpression(qr))
-            .isEqualTo("((((`f1`-`f2`)/`f1`)*`f3`)+2)");
+            .isEqualTo("((((`a`-`b`)/`a`)*`c`)+2)");
   }
 }
diff --git a/core/src/test/java/io/squashql/table/ATestMergeTables.java b/core/src/test/java/io/squashql/table/ATestMergeTables.java
index 60da63f..12da973 100644
--- a/core/src/test/java/io/squashql/table/ATestMergeTables.java
+++ b/core/src/test/java/io/squashql/table/ATestMergeTables.java
@@ -19,7 +19,7 @@ import static io.squashql.query.agg.AggregationFunction.SUM;
 
 abstract class ATestMergeTables {
 
-  static TypedField price = new TableTypedField(null, "price", double.class, null);
+  static TypedField price = new TableTypedField(null, "price", double.class);
   static CompiledAggregatedMeasure priceSum = new CompiledAggregatedMeasure("price.sum", price, SUM, null, false);
   static CompiledAggregatedMeasure priceAvg = new CompiledAggregatedMeasure("price.avg", price, AVG, null, false);
 
diff --git a/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java b/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java
index 7fb3237..f26844a 100644
--- a/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java
+++ b/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java
@@ -4,6 +4,7 @@ import io.squashql.jackson.JacksonUtil;
 import io.squashql.query.*;
 import io.squashql.query.builder.Query;
 import io.squashql.query.dto.*;
+import io.squashql.query.dto.QueryJoinDto;
 import io.squashql.query.parameter.QueryCacheParameter;
 import io.squashql.util.TestUtil;
 import org.assertj.core.api.Assertions;
@@ -232,11 +233,22 @@ public class TestJavascriptLibrary {
             .select(List.of(b2, c2), List.of(avg("sum", new TableField("f2"))))
             .build();
 
-    var q = new QueryJoinDto(query1, query2, JoinType.LEFT,
-            Functions.all(
-                    criterion(b1, b2, ConditionType.EQ),
-                    criterion(c1, c2, ConditionType.EQ)),
-            Map.of(a, new SimpleOrderDto(OrderKeywordDto.ASC)), 10);
+    var c3 = new TableField("myTable3.c3");
+    var query3 = from("myTable3")
+            .select(List.of(c3), List.of(avg("max", new TableField("f3"))))
+            .build();
+
+    QueryJoinDto q = QueryJoinDto.from(query1)
+            .join(query2,
+                    JoinType.LEFT,
+                    all(
+                            criterion(b1, b2, ConditionType.EQ),
+                            criterion(c1, c2, ConditionType.EQ)))
+            .orderBy(Map.of(a, new SimpleOrderDto(OrderKeywordDto.ASC), c3, new SimpleOrderDto(OrderKeywordDto.DESC)))
+            .join(query3, JoinType.INNER)
+            .limit(12);
+
+    q.current = 0;
 
     String name = "build-from-query-join.json"; // The content of this file is generated by the js code.
     QueryJoinDto qjs = JacksonUtil.deserialize(TestUtil.readAllLines(name), QueryJoinDto.class);
diff --git a/server/src/test/java/io/squashql/spring/web/rest/QueryControllerTest.java b/server/src/test/java/io/squashql/spring/web/rest/QueryControllerTest.java
index f95899d..1534334 100644
--- a/server/src/test/java/io/squashql/spring/web/rest/QueryControllerTest.java
+++ b/server/src/test/java/io/squashql/spring/web/rest/QueryControllerTest.java
@@ -7,6 +7,7 @@ import io.squashql.query.database.DuckDBQueryEngine;
 import io.squashql.query.database.QueryEngine;
 import io.squashql.query.dto.*;
 import io.squashql.query.exception.LimitExceedException;
+import io.squashql.query.dto.QueryJoinDto;
 import io.squashql.spring.dataset.DatasetTestConfig;
 import org.assertj.core.api.Assertions;
 import org.junit.jupiter.api.Test;
@@ -316,7 +317,9 @@ public class QueryControllerTest {
             .select(List.of(competitorEan), List.of(sum("competitor_sum", "competitor_price")))
             .build();
 
-    QueryJoinDto queryJoinDto = new QueryJoinDto(query1, query2, JoinType.FULL, criterion(ean, competitorEan, ConditionType.EQ), Map.of(ean, new SimpleOrderDto(OrderKeywordDto.ASC)), -1);
+    QueryJoinDto queryJoinDto = QueryJoinDto.from(query1)
+            .join(query2, JoinType.FULL, criterion(ean, competitorEan, ConditionType.EQ))
+            .orderBy(Map.of(ean, new SimpleOrderDto(OrderKeywordDto.ASC)));
     this.mvc.perform(MockMvcRequestBuilders.post(QueryController.MAPPING_QUERY_JOIN_EXPERIMENTAL)
                     .content(JacksonUtil.serialize(queryJoinDto))
                     .contentType(MediaType.APPLICATION_JSON))
diff --git a/server/src/test/resources/build-from-query-join.json b/server/src/test/resources/build-from-query-join.json
index 2b7b67a..d0ab688 100644
--- a/server/src/test/resources/build-from-query-join.json
+++ b/server/src/test/resources/build-from-query-join.json
@@ -1,1 +1,1 @@
-{"first":{"table":{"name":"myTable1","joins":[]},"virtualTableDtos":[],"columns":[{"@class":"io.squashql.query.TableField","fullName":"myTable1.a","tableName":"myTable1","fieldName":"a"},{"@class":"io.squashql.query.TableField","fullName":"myTable1.b","tableName":"myTable1","fieldName":"b"},{"@class":"io.squashql.query.TableField","fullName":"myTable1.c","tableName":"myTable1","fieldName":"c"}],"rollupColumns":[],"columnSets":{},"parameters":{},"measures":[{"@class":"io.squashql.query.AggregatedMeasure","field":{"@class":"io.squashql.query.TableField","fullName":"f1","fieldName":"f1"},"aggregationFunction":"sum","alias":"sum"}],"orders":{},"limit":-1},"second":{"table":{"name":"myTable2","joins":[]},"virtualTableDtos":[],"columns":[{"@class":"io.squashql.query.TableField","fullName":"myTable2.b","tableName":"myTable2","fieldName":"b"},{"@class":"io.squashql.query.TableField","fullName":"myTable2.c","tableName":"myTable2","fieldName":"c"}],"rollupColumns":[],"columnSets":{},"parameters":{},"measures":[{"@class":"io.squashql.query.AggregatedMeasure","field":{"@class":"io.squashql.query.TableField","fullName":"f2","fieldName":"f2"},"aggregationFunction":"avg","alias":"sum"}],"orders":{},"limit":-1},"joinType":"LEFT","joinCondition":{"conditionType":"AND","children":[{"field":{"@class":"io.squashql.query.TableField","fullName":"myTable1.b","tableName":"myTable1","fieldName":"b"},"fieldOther":{"@class":"io.squashql.query.TableField","fullName":"myTable2.b","tableName":"myTable2","fieldName":"b"},"conditionType":"EQ"},{"field":{"@class":"io.squashql.query.TableField","fullName":"myTable1.c","tableName":"myTable1","fieldName":"c"},"fieldOther":{"@class":"io.squashql.query.TableField","fullName":"myTable2.c","tableName":"myTable2","fieldName":"c"},"conditionType":"EQ"}]},"orders":{"{\"@class\":\"io.squashql.query.TableField\",\"fullName\":\"myTable1.a\",\"tableName\":\"myTable1\",\"fieldName\":\"a\"}":{"@class":"io.squashql.query.dto.SimpleOrderDto","order":"ASC"}},"limit":10}
\ No newline at end of file
+{"table":{"name":"__cte0__","joins":[{"table":{"name":"__cte1__","joins":[]},"type":"LEFT","joinCriteria":{"conditionType":"AND","children":[{"field":{"@class":"io.squashql.query.TableField","fullName":"myTable1.b","tableName":"myTable1","fieldName":"b"},"fieldOther":{"@class":"io.squashql.query.TableField","fullName":"myTable2.b","tableName":"myTable2","fieldName":"b"},"conditionType":"EQ"},{"field":{"@class":"io.squashql.query.TableField","fullName":"myTable1.c","tableName":"myTable1","fieldName":"c"},"fieldOther":{"@class":"io.squashql.query.TableField","fullName":"myTable2.c","tableName":"myTable2","fieldName":"c"},"conditionType":"EQ"}]}},{"table":{"name":"__cte2__","joins":[]},"type":"INNER"}]},"queries":[{"table":{"name":"myTable1","joins":[]},"virtualTableDtos":[],"columns":[{"@class":"io.squashql.query.TableField","fullName":"myTable1.a","tableName":"myTable1","fieldName":"a"},{"@class":"io.squashql.query.TableField","fullName":"myTable1.b","tableName":"myTable1","fieldName":"b"},{"@class":"io.squashql.query.TableField","fullName":"myTable1.c","tableName":"myTable1","fieldName":"c"}],"rollupColumns":[],"columnSets":{},"parameters":{},"measures":[{"@class":"io.squashql.query.AggregatedMeasure","field":{"@class":"io.squashql.query.TableField","fullName":"f1","fieldName":"f1"},"aggregationFunction":"sum","alias":"sum"}],"orders":{},"limit":-1},{"table":{"name":"myTable2","joins":[]},"virtualTableDtos":[],"columns":[{"@class":"io.squashql.query.TableField","fullName":"myTable2.b","tableName":"myTable2","fieldName":"b"},{"@class":"io.squashql.query.TableField","fullName":"myTable2.c","tableName":"myTable2","fieldName":"c"}],"rollupColumns":[],"columnSets":{},"parameters":{},"measures":[{"@class":"io.squashql.query.AggregatedMeasure","field":{"@class":"io.squashql.query.TableField","fullName":"f2","fieldName":"f2"},"aggregationFunction":"avg","alias":"sum"}],"orders":{},"limit":-1},{"table":{"name":"myTable3","joins":[]},"virtualTableDtos":[],"columns":[{"@class":"io.squashql.query.TableField","fullName":"myTable3.c3","tableName":"myTable3","fieldName":"c3"}],"rollupColumns":[],"columnSets":{},"parameters":{},"measures":[{"@class":"io.squashql.query.AggregatedMeasure","field":{"@class":"io.squashql.query.TableField","fullName":"f3","fieldName":"f3"},"aggregationFunction":"avg","alias":"max"}],"orders":{},"limit":-1}],"orders":{"{\"@class\":\"io.squashql.query.TableField\",\"fullName\":\"myTable1.a\",\"tableName\":\"myTable1\",\"fieldName\":\"a\"}":{"@class":"io.squashql.query.dto.SimpleOrderDto","order":"ASC"},"{\"@class\":\"io.squashql.query.TableField\",\"fullName\":\"myTable3.c3\",\"tableName\":\"myTable3\",\"fieldName\":\"c3\"}":{"@class":"io.squashql.query.dto.SimpleOrderDto","order":"DESC"}},"limit":12}
\ No newline at end of file