org.jooq.SelectUnionStep Maven / Gradle / Ivy
/*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* https://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*
* Other licenses:
* -----------------------------------------------------------------------------
* Commercial licenses for this work are available. These replace the above
* Apache-2.0 license and offer limited warranties, support, maintenance, and
* commercial database integrations.
*
* For more information, please visit: https://www.jooq.org/legal/licensing
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*/
package org.jooq;
// ...
// ...
// ...
// ...
import static org.jooq.SQLDialect.CUBRID;
// ...
import static org.jooq.SQLDialect.DERBY;
// ...
import static org.jooq.SQLDialect.H2;
// ...
import static org.jooq.SQLDialect.HSQLDB;
import static org.jooq.SQLDialect.IGNITE;
// ...
// ...
import static org.jooq.SQLDialect.MARIADB;
// ...
// ...
import static org.jooq.SQLDialect.MYSQL;
// ...
// ...
import static org.jooq.SQLDialect.POSTGRES;
// ...
// ...
import static org.jooq.SQLDialect.SQLITE;
// ...
// ...
// ...
import static org.jooq.SQLDialect.TRINO;
// ...
import static org.jooq.SQLDialect.YUGABYTEDB;
import org.jetbrains.annotations.NotNull;
/**
* This type is used for the {@link Select}'s DSL API when selecting generic
* {@link Record} types.
*
* Example:
* -- get all authors' first and last names, and the number
* -- of books they've written in German, if they have written
* -- more than five books in German in the last three years
* -- (from 2011), and sort those authors by last names
* -- limiting results to the second and third row
*
* SELECT T_AUTHOR.FIRST_NAME, T_AUTHOR.LAST_NAME, COUNT(*)
* FROM T_AUTHOR
* JOIN T_BOOK ON T_AUTHOR.ID = T_BOOK.AUTHOR_ID
* WHERE T_BOOK.LANGUAGE = 'DE'
* AND T_BOOK.PUBLISHED > '2008-01-01'
* GROUP BY T_AUTHOR.FIRST_NAME, T_AUTHOR.LAST_NAME
* HAVING COUNT(*) > 5
* ORDER BY T_AUTHOR.LAST_NAME ASC NULLS FIRST
* LIMIT 2
* OFFSET 1
* FOR UPDATE
* OF FIRST_NAME, LAST_NAME
* NO WAIT
*
Its equivalent in jOOQ
* create.select(TAuthor.FIRST_NAME, TAuthor.LAST_NAME, create.count())
* .from(T_AUTHOR)
* .join(T_BOOK).on(TBook.AUTHOR_ID.equal(TAuthor.ID))
* .where(TBook.LANGUAGE.equal("DE"))
* .and(TBook.PUBLISHED.greaterThan(parseDate('2008-01-01')))
* .groupBy(TAuthor.FIRST_NAME, TAuthor.LAST_NAME)
* .having(create.count().greaterThan(5))
* .orderBy(TAuthor.LAST_NAME.asc().nullsFirst())
* .limit(2)
* .offset(1)
* .forUpdate()
* .of(TAuthor.FIRST_NAME, TAuthor.LAST_NAME)
* .noWait();
*
Refer to the manual for more details
*
*
Referencing XYZ*Step types directly from client code
*
* It is usually not recommended to reference any XYZ*Step types
* directly from client code, or assign them to local variables. When writing
* dynamic SQL, creating a statement's components dynamically, and passing them
* to the DSL API statically is usually a better choice. See the manual's
* section about dynamic SQL for details: https://www.jooq.org/doc/latest/manual/sql-building/dynamic-sql.
*
* Drawbacks of referencing the XYZ*Step types directly:
*
* - They're operating on mutable implementations (as of jOOQ 3.x)
* - They're less composable and not easy to get right when dynamic SQL gets
* complex
* - They're less readable
* - They might have binary incompatible changes between minor releases
*
*
* @author Lukas Eder
*/
public interface SelectUnionStep extends SelectCorrelatedSubqueryStep {
/**
* Apply the UNION set operation.
*
* In SQL, a UNION is DISTINCT by default,
* meaning, duplicates are removed from the result set. So, this is the same
* as {@link #unionDistinct(Select)}. If duplicate removal isn't required,
* or already guaranteed by the data model, it is recommended to use
* {@link #unionAll(Select)}, instead.
*/
@Override
@NotNull @CheckReturnValue
@Support
SelectOrderByStep union(Select select);
/**
* Apply the UNION DISTINCT set operation.
*
* In SQL, a UNION is DISTINCT by default.
* However, it is often useful to make this explicit to express intent when
* distinct removal is really desired.
*/
@Override
@NotNull @CheckReturnValue
@Support
SelectOrderByStep unionDistinct(Select select);
/**
* Apply the UNION ALL set operation.
*/
@Override
@NotNull @CheckReturnValue
@Support
SelectOrderByStep unionAll(Select select);
/**
* Apply the EXCEPT (or MINUS) set operation.
*
* In SQL, an EXCEPT is DISTINCT by default,
* meaning, duplicates are removed from the result set. So, this is the same
* as {@link #exceptDistinct(Select)}. If duplicate removal isn't required,
* or already guaranteed by the data model, it is recommended to use
* {@link #exceptAll(Select)}, instead, if the underlying RDBMS supports it.
*/
@Override
@NotNull @CheckReturnValue
@Support({ CUBRID, DERBY, H2, HSQLDB, IGNITE, MARIADB, MYSQL, POSTGRES, SQLITE, TRINO, YUGABYTEDB })
SelectOrderByStep except(Select select);
/**
* Apply the EXCEPT (or MINUS) set operation.
*
* In SQL, an EXCEPT is DISTINCT by default.
* However, it is often useful to make this explicit to express intent when
* distinct removal is really desired.
*/
@Override
@NotNull @CheckReturnValue
@Support({ CUBRID, DERBY, H2, HSQLDB, IGNITE, MARIADB, MYSQL, POSTGRES, SQLITE, TRINO, YUGABYTEDB })
SelectOrderByStep exceptDistinct(Select select);
/**
* Apply the EXCEPT ALL set operation.
*/
@Override
@NotNull @CheckReturnValue
@Support({ CUBRID, DERBY, HSQLDB, MARIADB, MYSQL, POSTGRES, TRINO, YUGABYTEDB })
SelectOrderByStep exceptAll(Select select);
/**
* Apply the INTERSECT set operation.
*
* In SQL, an INTERSECT is DISTINCT by default,
* meaning, duplicates are removed from the result set. So, this is the same
* as {@link #intersectDistinct(Select)}. If duplicate removal isn't
* required, or already guaranteed by the data model, it is recommended to
* use {@link #intersectAll(Select)}, instead, if the underlying RDBMS
* supports it. Apply the INTERSECT set operation.
*/
@Override
@NotNull @CheckReturnValue
@Support({ CUBRID, DERBY, H2, HSQLDB, IGNITE, MARIADB, MYSQL, POSTGRES, SQLITE, TRINO, YUGABYTEDB })
SelectOrderByStep intersect(Select select);
/**
* Apply the INTERSECT set operation.
*
* In SQL, an INTERSECT is DISTINCT by default,
* meaning, duplicates are removed from the result set. So, this is the same
* as {@link #intersectDistinct(Select)}. If duplicate removal isn't
* required, or already guaranteed by the data model, it is recommended to
* use {@link #intersectAll(Select)}, instead, if the underlying RDBMS
* supports it. Apply the INTERSECT set operation.
*/
@Override
@NotNull @CheckReturnValue
@Support({ CUBRID, DERBY, H2, HSQLDB, IGNITE, MARIADB, MYSQL, POSTGRES, SQLITE, TRINO, YUGABYTEDB })
SelectOrderByStep intersectDistinct(Select select);
/**
* Apply the INTERSECT ALL set operation.
*/
@Override
@NotNull @CheckReturnValue
@Support({ CUBRID, DERBY, HSQLDB, MARIADB, MYSQL, POSTGRES, TRINO, YUGABYTEDB })
SelectOrderByStep intersectAll(Select select);
}