select.md

SELECT supported syntax

Check also the expressions supported syntax, used heavily in this examples

• ✅ WITH

  • Details in WITH supported syntax
  • ✅ WITH
  • ✅ WITH RECURSIVE

• ✅ Select all from table

  Sql.From<Customer>();
  //or
  Sql.From<Customer>().Select(x => x);

• ✅ DISTINCT

  Sql
  .From<Customer>()
  .Distinct()

• ✅ DISTINCT ON (expr)

  Sql
  .From<Customer>()
  .DistinctOn(x => x.LocationId)

• ✅ SELECT *, ...

  Sql
  .From<Customer>()
  .Select(x => Sql.Star().Map(new
  {
      //Extra columns after 'Star' are added using the 'Map' method
      FullName = x.Name + x.LastName
  }));

• ✅ SELECT table.*, ...

  Sql
  .From<Table>()
  .Select(x => 
      Sql.Star(x) //Star arguments can reference FROM list items
      .Map(new
      {
          //Extra columns after 'Star' are added using the 'Map' method
          FullName = x.Name + x.LastName
      }));

• 🌕 FROM

  • Details in FROM supported syntax
  • ✅ FROM table
  • ✅ FROM Subquery
  • ✅ LATERAL subquery
  • ✅ Joins: INNER, LEFT, RIGHT, CROSS
  • ✅ JOIN ... ON (expr)
  • ❌ FROM ONLY
  • ❌ TABLESAMPLE
  • ❌ Function calls
  • ❌ FROM t1, t2, t3, ... (Workaround: CROSS JOIN )
  • ❌ NATURAL JOIN
  • ❌ JOIN ... USING (...)

• ✅ WHERE (expr)

Sql
.From<Customer>()
.Where(x => x.LastName == "Kahlo")

• 🌕 GROUP BY
  • ✅ GROUP BY (expr, ...)

  Sql
  .From<Customer>()
  .Select(x => x)
  .GroupBy(x => x.Name).ThenBy(x => x.LastName)

  • ❌ GROUP BY ()
  • ❌ ROLL UP, CUBE, GROUPING SETS
• ❌ HAVING
• 🌕 WINDOW
  • Details in WINDOW supported syntax
  • ✅ RANGE, ROWS, GROUP
  • ✅ PARTITION BY
  • 🌕 ORDER BY
    • ✅ ASC | DESC
    • ✅ NULLS [FIRST | LAST]
    • ❌ USING operator
  • ✅ frame_clause
    • ✅ { RANGE | ROWS } ...
    • ✅ { RANGE | ROWS } BETWEEN ... AND ...
  • ✅ Define WINDOW based on another existing WINDOW
• ✅ UNION, INTERSECT, EXCEPT

      Sql.From<Customer>()
      .Select(x => new {
          x.Id
      })
      .UnionAll(
          //UNION compatible query
          Sql.From<Patient>()
          .Select(x => new {
              x.Id
          })
      )
      .UnionAll(
          //Multiple unions can be chained
      )

• ✅ LIMIT (expr)

   Sql
  .From<Customer>()
  .Limit(100)

• ❌ OFFSET ...
• ❌ FETCH (...)
• ❌ FOR ... OF ...