Available in versions: Dev (3.21) | Latest (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11
FROM clause
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The SQL FROM clause allows for specifying any number of table expressions to select data from. The following are examples of how to form normal FROM clauses:
SELECT 1 FROM BOOK SELECT 1 FROM BOOK, AUTHOR SELECT 1 FROM BOOK "b", AUTHOR "a"
create.selectOne().from(BOOK).fetch();
create.selectOne().from(BOOK, AUTHOR).fetch();
create.selectOne().from(BOOK.as("b"), AUTHOR.as("a")).fetch();
Read more about aliasing in the manual's section about aliased tables.
More advanced table expressions
Apart from simple tables, you can pass any arbitrary table expression to the jOOQ FROM clause. This may include unnested cursors in Oracle:
SELECT *
FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'ALLSTATS')
);
create.select()
.from(table(
DbmsXplan.displayCursor(null, null, "ALLSTATS")
).fetch();
Note, in order to access the DbmsXplan package, you can use the code generator to generate Oracle's SYS schema.
Selecting FROM DUAL with jOOQ
In many SQL dialects, FROM is a mandatory clause, in some it isn't. jOOQ allows you to omit the FROM clause, returning just one record. An example:
SELECT 1 FROM DUAL SELECT 1
DSL.using(SQLDialect.ORACLE).selectOne().fetch(); DSL.using(SQLDialect.POSTGRES).selectOne().fetch();
Read more about dual or dummy tables in the manual's section about the DUAL table. The following are examples of how to form normal FROM clauses:
Table of contents
- 3.5.3.2.1.
- JOIN operator
- 3.5.3.2.2.
- Implicit path JOIN
- 3.5.3.2.3.
- Implicit to-many path JOIN
- 3.5.3.2.4.
- Explicit path JOIN
- 3.5.3.2.5.
- Implicit path correlation
| previous : next |
References to this page
- Settings: object qualification for columns
- The implicit path JOIN notation for to-one joining
- Using the path notation for explicit joins
- Using join path notations in correlated subqueries
- The WHERE clause of the SELECT statement
- Lexical and logical SELECT clause order
- Table references generated by the code generator
- The CROSS JOIN operator
- The VALUES() table constructor
- Derived tables
- Array and cursor unnesting
- The DUAL dummy table
- Data change delta tables
- How aggregate functions interact with GROUP BY
- Dynamic SQL
- QueryPart declaration vs reference
- Consecutive aggregation
- Table valued functions
- Don't do this with jOOQ: referencing the step types
Feedback
Do you have any feedback about this page? We'd love to hear it!