Skip to content

SELECT

Jump to bottom
go-jet edited this page Dec 28, 2021 · 41 revisions

Contents

SELECT statement is used to retrieve records from one or more tables in PostgreSQL.
More about SELECT statement can be found at:
PostgreSQL - https://www.postgresql.org/docs/11/sql-select.html
MySQL - https://dev.mysql.com/doc/refman/8.0/en/select.html
MariaDB - https://mariadb.com/kb/en/library/select/

Supported clauses

  • SELECT(expressions...) - expressions to form output rows of the SELECT statement.
  • DISTINCT() - remove all duplicate rows from result set
  • FROM(tableSource...) - specifies one or more source tables for the SELECT.
  • WHERE(condition) - only rows for which condition returns true will be selected.
  • GROUP BY(groupingElement, ...) - will condense into a single row all selected rows that share the same values for the grouped expressions.
  • HAVING(condition) - eliminates group rows that do not satisfy the condition
  • WINDOW(name) - starts composing name window definition
  • ORDER BY(orderBy, ...) - causes the result rows to be sorted according to the specified expression(s)
  • LIMIT(count) - specifies the maximum number of rows to return
  • OFFSET(start) - specifies the number of rows to skip before starting to return rows
  • FOR(lockMode) - how SELECT will lock rows as they are obtained from the table
    PostgreSQL lock mode can be: UPDATE(), NO_KEY_UPDATE(), SHARE() and KEY_SHARE(), with optional clauses: NOWAIT() and SKIP_LOCKED().
    MySQL lock modes can be: UPDATE() and SHARE(), with optional clauses: NOWAIT() and SKIP_LOCKED().
  • UNION(select) / UNION_ALL(select) - computes the set union of the rows returned by the involved SELECT statements
  • INTERSECT(select) / INTERSECT_ALL(select) - computes the set intersection of the rows returned by the involved SELECT statements
  • EXCEPT(select) / EXCEPT_ALL(select) - computes the set of rows that are in the result of the left SELECT statement but not in the result of the right one

This list might be extended with feature Jet releases.

Example per clause

SELECT clause

Sample SELECT clause written in Go:

   SELECT(
        Int(1).ADD(Int(12)).SUB(Int(21)),                           // arbitrary expression
        Film.Name,                                                  // column
        Customer.FirstName.CONCAT(Customer.LastName).AS("FullName") // alias
   )

*Above statement uses dot "." import

Above SQL clause in go will produce following raw SQL:

SELECT 1 + 12 - 21,
       film.name AS "film.name",  
       customer.first_name || customer.last_name AS "FullName"

film.name AS "film.name" - column names are aliased by default. Alias is used during execution to map row result to appropriate model structure.

DISTINCT clause

SELECT(Film.Name).
.DISTINCT().

Raw SQL:

SELECT DISTINCT film.name

FROM clause

The FROM clause specifies one or more source tables for the SELECT.

Go:
1) .FROM(Film)
2) .FROM(
      Film.
        INNER_JOIN(Language, Langauge.LanguageID.EQ(Film.FilmID))
   )
3) .FROM(Film, Language, Artist) // implicit CROSS JOIN
SQL:
1) FROM dvds.film
2) FROM Film 
       INNER JOIN Language ON (Language.LanguageID = Film.FilmID)
3) FROM Film, Language, Artists

WHERE clause

Go:
.WHERE(Film.Length.GT(Int(150)))
SQL:
WHERE film.length > 150

GROUP BY clause

Go:
.GROUP_BY(Film.Length)
SQL:
GROUP BY film.length

WINDOW

SELECT(
    AVG(Payment.Amount).OVER(),
    MINf(Payment.Amount).OVER(PARTITION_BY(Payment.CustomerID).ORDER_BY(Payment.PaymentDate.DESC())),
    ROW_NUMBER().OVER(Window("w1")),
    RANK().OVER(
       Window("w2").
       ORDER_BY(Payment.CustomerID).
       RANGE(PRECEDING(UNBOUNDED), FOLLOWING(UNBOUNDED)),
    ),
    AVG(Payment.Amount).OVER(Window("w3").ROWS(PRECEDING(1), FOLLOWING(2))),
).
FROM(Payment).
WINDOW("w1").AS(PARTITION_BY(Payment.PaymentDate)).
   WINDOW("w2").AS(Window("w1")).
   WINDOW("w3").AS(Window("w2").ORDER_BY(Payment.CustomerID)).
SELECT AVG(payment.amount) OVER (),
     MIN(payment.amount) OVER (PARTITION BY payment.customer_id ORDER BY payment.payment_date DESC),
     ROW_NUMBER() OVER (w1),
     RANK() OVER (w2 ORDER BY payment.customer_id RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
     AVG(payment.amount) OVER (w3 ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING)
FROM dvds.payment
WINDOW w1 AS (PARTITION BY payment.payment_date), w2 AS (w1), w3 AS (w2 ORDER BY payment.customer_id);

HAVING clause

Go:
.HAVING(SUMi(Film.Length).GT(Int(150))
SQL:
HAVING SUM(film.length) > 150

ORDER BY clause

Go:
.ORDER_BY(Film.Length)
SQL:
ORDER BY film.length

LIMIT clause

Go:
.LIMIT(11)
SQL:
LIMIT 11

OFFSET clause

Go:
.OFFSET(11)
SQL:
OFFSET 11

FOR clause

Go:
.FOR(NO_KEY_UPDATE().SKIP_LOCKED())
SQL:
FOR NO KEY UPDATE SKIP LOCKED

10. Set clauses (UNION, UNION_ALL, INTERSECT, INTERSECT_ALL, EXCEPT, EXCEPT_ALL)

Go:
SELECT(Payment.Amount).FROM(Payment)
UNION_ALL(SELECT(Payment.Amount).FROM(Payment))
Sql:
(
     SELECT payment.amount AS "payment.amount"
     FROM dvds.payment
)
UNION
(
     SELECT payment.amount AS "payment.amount"
     FROM dvds.payment
);

Two forms of select statements

1. Classical select statement

Columns selected are before table sources(FROM clause)

SELECT(
    Payment.AllColumns,
    Customer.AllColumns,
).
FROM(Payment.
    INNER_JOIN(Customer, Payment.CustomerID.EQ(Customer.CustomerID))).
ORDER_BY(Payment.PaymentID.ASC()).
LIMIT(30)

2. Jet select statement

Table sources are before columns selected. There is no FROM clause.

Payment.
    INNER_JOIN(Customer, Payment.CustomerID.EQ(Customer.CustomerID))
SELECT(
    Payment.AllColumns,
    Customer.AllColumns,
).
ORDER_BY(Payment.PaymentID.ASC()).
LIMIT(30)

Second form is added, because sometimes feels more natural to first think about the tables of interest, and than about the columns.
Both forms produces exactly the same raw SQL.

Sub-query

How to write sub-queries?

Sub-queries are composed first:

// select film_id, title and rating from film table that have 'R' rating.
rRatingFilms := 
	SELECT(
		Film.FilmID,
		Film.Title,
		Film.Rating,
	).FROM(
		Film,
	).WHERE(
		Film.Rating.EQ(enum.MpaaRating.R),
	).AsTable("rFilms")

AsTable("rFilms") - allows SELECT statements to be used as a source table from FROM clause.

To use sub-query columns in SELECT statement expressions we have to export column from sub-query, using From method.

rFilmId := Film.FilmID.From(rRatingFilms)  // <- used for join condition

Now we can write:

query := SELECT(
		rRatingFilms.AllColumns(),
		Actor.AllColumns,
		FilmActor.AllColumns,
	).FROM(
		rRatingFilms.
			INNER_JOIN(FilmActor, FilmActor.FilmID.EQ(rFilmID)).
			INNER_JOIN(Actor, FilmActor.ActorID.EQ(Actor.ActorID)),
	)

rRatingFilms.AllColumns(), - all sub-query columns required for projection can be exported with AllColumns() method. The effect is the same as each of the sub-query columns are exported one by one using the From method.

Debug SQL of above example:

SELECT "rFilms"."film.film_id" AS "film.film_id",                -- <- the same alias names from sub-query
     "rFilms"."film.title" AS "film.title",
     "rFilms"."film.rating" AS "film.rating",
     actor.actor_id AS "actor.actor_id",
     actor.first_name AS "actor.first_name",
     actor.last_name AS "actor.last_name",
     actor.last_update AS "actor.last_update",
     film_actor.actor_id AS "film_actor.actor_id",
     film_actor.film_id AS "film_actor.film_id",
     film_actor.last_update AS "film_actor.last_update" 
FROM (
          SELECT film.film_id AS "film.film_id",                -- <- the same alias names will appear in surrounding(main) query
               film.title AS "film.title",
               film.rating AS "film.rating"
          FROM dvds.film
          WHERE film.rating = 'R'
     ) AS "rFilms"
     INNER JOIN dvds.film_actor ON (film_actor.film_id = "rFilms"."film.film_id")
     INNER JOIN dvds.actor ON (film_actor.actor_id = actor.actor_id);

AS "film.film_id", - note that column aliases are the same in main query as they are in the sub-query. This is because default column aliases are just passed from the sub-query to surrounding query, or in this case main query. Default column aliases are NOT affected with a sub-query alias - rFilms.

Since the sub-query column aliases, are default table alias in the main query, we can use the generated model types as the scan destination, without any modifications:

var dest []struct {
	model.Film             // <- data from sub-query

	Actors []model.Actor
}

err := stmt.Query(db, &dest)

The same logic applies as well for common table expression queries used in WITH statement or in other common table expression queries.

Lateral queries

Lateral queries have a similar syntax as sub-queries and can be used in a FROM clause in the same way as tables or sub-queries.

languages := LATERAL(
	SELECT(
		Language.AllColumns,
	).FROM(
		Language,
	).WHERE(
		Language.Name.NOT_IN(String("spanish")).
			AND(Film.LanguageID.EQ(Language.LanguageID)),   // Film.Language is from main statement (bellow)
	),
).AS("languages")

stmt := SELECT(
	Film.FilmID,
	Film.Title,
	languages.AllColumns(),
).FROM(
	Film, 
        languages,
).WHERE(
	Film.FilmID.EQ(Int(1))
)

What if sub-query projection is not a column?

For instance:

customersPayments := Payment.
    SELECT(
        Payment.CustomerID,
        SUMf(Payment.Amount).AS("amount_sum"),
    ).
    GROUP_BY(Payment.CustomerID).
    AsTable("customer_payment_sum")

customerId := Payment.CustomerID.From(customersPayments)

To export "amount_sum" from customersPayments sub-query we have to create column first with appropriate type and a name. Because SUMf produces float expression we will create FloatColumn with name of the column alias "amount_sum"

amountSum := FloatColumn("amount_sum").From(customersPayments)
Clone this wiki locally