Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
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:
References to this page
- Object qualification for columns
- Implicit path JOIN
- Explicit path JOIN
- Implicit path correlation
- WHERE clause
- Lexical and logical SELECT clause order
- Generated Tables
- CROSS JOIN
- The VALUES() table constructor
- Derived tables
- Array and cursor unnesting
- The DUAL table
- Data change delta tables
- Grouping
- Dynamic SQL
- Declaration vs reference
- Consecutive aggregation
- Table valued functions
- jOOQ: Referencing the Step types
Feedback
Do you have any feedback about this page? We'd love to hear it!