Select Keywords¶
This page lists the SQL keywords and clauses used in SELECT statements, along with their corresponding methods in ts-sql-query. It includes support for projections, filtering, joins, ordering, pagination, aggregation, recursive queries, and compound set operations.
| SQL Keyword | ts-sql-query Equivalent | Notes | Link |
|---|---|---|---|
| AS (in select) | Set the alias as the propery's name in the projection | Alias for fields. | Basic query structure, Complex projections |
| AS (in from) | table/view .as(...), in case of left join .forUseInLeftJoinAs(...) |
Alias for tables. | Select with joins and order by, Select with left join |
| ASC | query .orderBy(col, 'asc') or .orderBy(col) (default) |
Ascending order (default). | Select with joins and order by, Select API |
| CONNECT BY | query .connectBy(...) |
Defines the parent-child relationship between rows in a hierarchical query; Oracle-specific. | Recursive connect by, Select API |
| CONNECT BY NOCYCLE | query .connectByNoCycle(...) |
Prevents infinite loops by avoiding cycles in hierarchical queries; Oracle-specific. | Recursive connect by, Select API |
| DESC | query .orderBy(col, 'desc') |
Descending order. | Select with joins and order by, Select API |
| EXCEPT | query .except(...) |
Set difference of result sets (Removing duplicates). | Select with a compound operator (union, intersect, except), Select API |
| EXCEPT ALL | query .exceptAll(...) |
Set difference of result sets. | Select with a compound operator (union, intersect, except), Select API |
| FETCH FIRST | query .limit(...) |
Limits the number of returned rows. | Select page, Select API |
| FETCH NEXT | query .limit(...) |
Limits the number of returned rows. | Select page, Select API |
| FOR UPDATE | You can inject your custom SQL fragment | Locks selected rows to prevent concurrent updates from other transactions. | How can I implement select for update? |
| FULL JOIN | Not available, restructure your query using union. | Full outer join between tables. | |
| FULL OUTER JOIN | Not available, restructure your query using union. | Full outer join between tables. | |
| GROUP BY | query .groupBy(...) |
Groups rows for aggregation. | Select with aggregate functions and group by, Select API |
| HAVING | query .having(...) |
Filters aggregated rows. | Select API |
| INNER JOIN | query .innerJoin(...) |
Inner join between tables. | Select with joins and order by, Select API |
| INTERSECT | query .intersect(...) |
Result intersection (Removing duplicates). | Select with a compound operator (union, intersect, except), Select API |
| INTERSECT ALL | query .intersectAll(...) |
Result intersection. | Select with a compound operator (union, intersect, except), Select API |
| JOIN | query .join(...) |
Inner join between tables. | Select with joins and order by, Select API |
| LEFT JOIN | query .leftJoin(...) |
Left outer join between tables. | Select with left join, Select API |
| LEFT OUTER JOIN | query .leftOuterJoin(...) |
Left outer join between tables. | Select with left join, Select API |
| LIMIT | query .limit(...) |
Limits the number of returned rows. | Select page, Select API |
| MINUS | query .minus(...) |
Set difference of result sets (Removing duplicates). | Select with a compound operator (union, intersect, except), Select API |
| MINUS ALL | query .minusAll(...) |
Set difference of result sets. | Select with a compound operator (union, intersect, except), Select API |
| NULLS FIRST | query .orderBy(col, 'asc nulls first') / .orderBy(col, 'desc nulls first') |
Specifies that null values should appear before non-null values when sorting. | Select with joins and order by, Select API |
| NULLS LAST | query .orderBy(col, 'asc nulls last') / .orderBy(col, 'desc nulls last') |
Specifies that null values should appear after non-null values when sorting. | Select with joins and order by, Select API |
| OFFSET | query .offset(...) |
Skips a number of rows. | Select page, Select API |
| ORDER BY | query .orderBy(...) |
Orders the result set. | Select with joins and order by, Select API |
| PRIOR | Provided as function in .connectBy(...) and .connectByNoCycle(...) |
Indicates the parent row in the hierarchy; handled internally by the API; Oracle-specific. | Recursive connect by, Select API |
| RECURSIVE | query .recursiveUnion(...) / .recursiveUnionAll(...) |
Recursive Common Table Expression (CTE). | Recursive select, Select API |
| RIGHT JOIN | Not available, reestructure the query to use left join. | Right outer join between tables. | |
| RIGHT OUTER JOIN | Not available, reestructure the query to use left join. | Right outer join between tables. | |
| SELECT | connection .selectFrom(...) |
Selects specific columns. | Basic query structure, Connection API |
| SELECT * | Not supported. Use explicit fields. | Avoid SELECT *; use explicit fields. | How can I use select * in my queries? |
| SELECT DISTINCT | connection .selectDistinctFrom(...) |
Removes duplicates. | Basic query structure, Connection API |
| START WITH | query .startWith(...) |
Specifies the root rows for a hierarchical query; Oracle-specific. | Recursive connect by, Select API |
| TOP | query .limit(...) |
Limits the number of returned rows. | Select page, Select API |
| UNION | query .union(...) |
Combines result sets (Removing duplicates). | Select with a compound operator (union, intersect, except), Select API |
| UNION ALL | query .unionAll(...) |
Combines result sets. | Select with a compound operator (union, intersect, except), Select API |
| WHERE | query .where(...) |
Filters rows by condition. | Basic query structure, Select API |
| WITH | query .forUseInQueryAs(...) |
Common Table Expression (CTE). | Using a select as a view in another select query, Does ts-sql-query support common table expressions (CTE)?, Select API |
| WITH RECURSIVE | query .forUseInQueryAs(...) |
Common Table Expression (CTE). | Using a select as a view in another select query, Does ts-sql-query support common table expressions (CTE)?, Select API |