-
Notifications
You must be signed in to change notification settings - Fork 125
SELECT
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/
Following clauses are supported:
-
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()
andKEY_SHARE()
, with optional clauses:NOWAIT()
andSKIP_LOCKED()
.
MySQL lock modes can be:UPDATE()
andSHARE()
, with optional clauses:NOWAIT()
andSKIP_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.
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.
SELECT(Film.Name).
.DISTINCT().
Raw SQL:
SELECT DISTINCT film.name
Go:
1) .FROM(Film)
2) .FROM(
Film.
INNER_JOIN(Language, Langauge.LanguageID.EQ(Film.FilmID))
)
SQL:
1) FROM dvds.film
2) FROM Film
INNER JOIN Language ON (Language.LanguageID = Film.FilmID)
Go:
.WHERE(Film.Length.GT(Int(150)))
SQL:
WHERE film.length > 150
Go:
.GROUP_BY(Film.Length)
SQL:
GROUP BY film.length
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);
Go:
.HAVING(SUMi(Film.Length).GT(Int(150))
SQL:
HAVING SUM(film.length) > 150
Go:
.ORDER_BY(Film.Length)
SQL:
ORDER BY film.length
Go:
.LIMIT(11)
SQL:
LIMIT 11
Go:
.OFFSET(11)
SQL:
OFFSET 11
Go:
.FOR(NO_KEY_UPDATE().SKIP_LOCKED())
SQL:
FOR NO KEY UPDATE SKIP LOCKED
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
);
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)
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.
How to write SELECT statement with sub-queries?
Sub-queries are composed first:
// select film_id and title from film table that have 'R' rating.
rRatingFilms := Film.
SELECT(
Film.FilmID,
Film.Title,
).
WHERE(Film.Rating.EQ(enum.MpaaRating.R)).
AsTable("rFilms")
AsTable("rFilms")
- allows SELECT statements to be seen as source table.
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 := Actor.
INNER_JOIN(FilmActor, Actor.ActorID.EQ(FilmActor.FilmID)).
INNER_JOIN(rRatingFilms, FilmActor.FilmID.EQ(rFilmId)).
SELECT(
Actor.AllColumns,
FilmActor.AllColumns,
rRatingFilms.AllColumns(),
)
rRatingFilms.AllColumns(),
- sub-query columns needed for projection can be exported with AllColumns() method.
No need to export each one of them with From
.
Debug SQL of above example:
SELECT 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",
"rFilms"."film.film_id" AS "film.film_id",
"rFilms"."film.title" AS "film.title",
"rFilms"."film.rating" AS "film.rating"
FROM dvds.actor
INNER JOIN dvds.film_actor ON (actor.actor_id = film_actor.film_id)
INNER JOIN (
SELECT film.film_id AS "film.film_id",
film.title AS "film.title",
film.rating AS "film.rating"
FROM dvds.film
WHERE film.rating = 'R'
) AS "rFilms" ON (film_actor.film_id = "rFilms"."film.film_id");
Lateral queries have similar syntax as sub-queries and can be used in FROM clause 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))
)
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)
- Home
- Generator
- Model
- SQL Builder
- Query Result Mapping (QRM)
-
FAQ
- How to execute jet statement in SQL transaction?
- How to construct dynamic projection list?
- How to construct dynamic condition?
- How to use jet in multi-tenant environment?
- How to change model field type?
- How to use custom(or currently unsupported) functions and operators?
- How to use IN/NOT_IN with dynamic list of values?
- Scan stopped working after naming a destination type