# derived table

Published articles for derived table.

This is one page of public article previews, not the complete archive. Follow Next page to continue. Summaries are not the original full articles.

## An Efficient Way to Check for Existence of Multiple Values in SQL

DevFeed: [An Efficient Way to Check for Existence of Multiple Values in SQL](<https://devfeed.tech/articles/an-efficient-way-to-check-for-existence-of-multiple-values-in-sql-28931.md>)

Original publisher: [Read original article](<https://blog.jooq.org/an-efficient-way-to-check-for-existence-of-multiple-values-in-sql/>)

Author: lukaseder

Published: 2024-02-16T14:16:45Z

Content type: tutorial

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [SQL](<https://devfeed.tech/topics/sql.md>), [Benchmark](<https://devfeed.tech/topics/benchmark.md>), [Oracle Database](<https://devfeed.tech/topics/oracle-database.md>), [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>)

Tags: [benchmark](<https://devfeed.tech/tags/benchmark.md>), [benchmarking](<https://devfeed.tech/tags/benchmarking.md>), [compare](<https://devfeed.tech/tags/compare.md>), [count](<https://devfeed.tech/tags/count.md>), [database](<https://devfeed.tech/tags/database.md>), [derived-table](<https://devfeed.tech/tags/derived-table.md>), [execution](<https://devfeed.tech/tags/execution.md>), [exists](<https://devfeed.tech/tags/exists.md>), [join](<https://devfeed.tech/tags/join.md>), [limit](<https://devfeed.tech/tags/limit.md>), [mysql](<https://devfeed.tech/tags/mysql.md>), [oracle](<https://devfeed.tech/tags/oracle.md>), [oracle-23c](<https://devfeed.tech/tags/oracle-23c.md>), [performance](<https://devfeed.tech/tags/performance.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [rdbms](<https://devfeed.tech/tags/rdbms.md>), [sql](<https://devfeed.tech/tags/sql.md>), [sql-performance](<https://devfeed.tech/tags/sql-performance.md>), [sql-server](<https://devfeed.tech/tags/sql-server.md>), [sql-syntax](<https://devfeed.tech/tags/sql-syntax.md>), [top](<https://devfeed.tech/tags/top.md>)

### AI overview

This article explains how to check whether multiple matching rows exist in SQL. It recommends limiting rows in a derived table before counting them, then compares execution plans and benchmark results for PostgreSQL 15 and Oracle 23c.

### Source excerpt

In a previous blog post, we've advertised the use of SQL EXISTS rather than COUNT(*) to check for existence of a value in SQL. I.e. to check if in the Sakila database, actors called WAHLBERG have played in any films, instead of: Do this: (Depending on your dialect you may require a FROM DUAL clause, ... Continue reading An Efficient Way to Check for Existence of Multiple Values in SQL ->

## How to Write a Derived Table in jOOQ

DevFeed: [How to Write a Derived Table in jOOQ](<https://devfeed.tech/articles/how-to-write-a-derived-table-in-jooq-28948.md>)

Original publisher: [Read original article](<https://blog.jooq.org/how-to-write-a-derived-table-in-jooq/>)

Author: lukaseder

Published: 2023-02-24T09:18:48Z

Content type: tutorial

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [SQL](<https://devfeed.tech/topics/sql.md>), [Java](<https://devfeed.tech/topics/java.md>), [Databases](<https://devfeed.tech/topics/databases.md>)

Tags: [cte](<https://devfeed.tech/tags/cte.md>), [derived-table](<https://devfeed.tech/tags/derived-table.md>), [how-to](<https://devfeed.tech/tags/how-to.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [jooq-in-use](<https://devfeed.tech/tags/jooq-in-use.md>), [sql](<https://devfeed.tech/tags/sql.md>), [type-safety](<https://devfeed.tech/tags/type-safety.md>)

### AI overview

This tutorial explains how to write derived tables and common table expressions in jOOQ. It notes that derived-table columns are not fully type-safe in Java, and shows that simplifying an SQL query can sometimes eliminate the derived table and allow greater use of generated code.

### Source excerpt

One of the more frequent questions about jOOQ is how to write a derived table (or a CTE). The jOOQ manual shows a simple example of a derived table: In SQL: In jOOQ: And that's pretty much it. The question usually arises from the fact that there's a surprising lack of type safety when working ... Continue reading How to Write a Derived Table in jOOQ ->

## LATERAL is Your Friend to Create Local Column Variables in SQL

DevFeed: [LATERAL is Your Friend to Create Local Column Variables in SQL](<https://devfeed.tech/articles/lateral-is-your-friend-to-create-local-column-variables-in-sql-28955.md>)

Original publisher: [Read original article](<https://blog.jooq.org/lateral-is-your-friend-to-create-local-column-variables-in-sql/>)

Author: lukaseder

Published: 2022-11-04T08:56:17Z

Content type: tutorial

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [SQL](<https://devfeed.tech/topics/sql.md>), [Databases](<https://devfeed.tech/topics/databases.md>)

Tags: [apply](<https://devfeed.tech/tags/apply.md>), [cross-apply](<https://devfeed.tech/tags/cross-apply.md>), [cross-join-lateral](<https://devfeed.tech/tags/cross-join-lateral.md>), [derived-table](<https://devfeed.tech/tags/derived-table.md>), [examples](<https://devfeed.tech/tags/examples.md>), [lateral](<https://devfeed.tech/tags/lateral.md>), [lateral-derived-table](<https://devfeed.tech/tags/lateral-derived-table.md>), [local-variables](<https://devfeed.tech/tags/local-variables.md>), [oracle](<https://devfeed.tech/tags/oracle.md>), [outer-apply](<https://devfeed.tech/tags/outer-apply.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [sql](<https://devfeed.tech/tags/sql.md>), [sql-server](<https://devfeed.tech/tags/sql-server.md>), [variables](<https://devfeed.tech/tags/variables.md>)

### AI overview

This article explains how SQL's LATERAL clause can be used to define local column variables next to the tables that provide their values. It compares this approach with derived tables and notes database-specific syntax, including SQL Server's CROSS APPLY.

### Source excerpt

The standard SQL WITH clause has been tremendously helpful in structuring SQL queries. Instead of nesting everything in unreadable derived tables like this: People have started moving the logic up front, just like in any other programming language, where we declare things first, lexically, then use them: Both queries will produce the 5 actors with ... Continue reading LATERAL is Your Friend to Create Local Column Variables in SQL ->

## How to Filter a SQL Nested Collection by a Value

DevFeed: [How to Filter a SQL Nested Collection by a Value](<https://devfeed.tech/articles/how-to-filter-a-sql-nested-collection-by-a-value-28940.md>)

Original publisher: [Read original article](<https://blog.jooq.org/how-to-filter-a-sql-nested-collection-by-a-value/>)

Author: lukaseder

Published: 2022-06-10T14:32:38Z

Content type: tutorial

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [SQL](<https://devfeed.tech/topics/sql.md>), [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [Stack Overflow](<https://devfeed.tech/topics/stackoverflow.md>)

Tags: [array](<https://devfeed.tech/tags/array.md>), [array-agg](<https://devfeed.tech/tags/array-agg.md>), [derived-table](<https://devfeed.tech/tags/derived-table.md>), [filter](<https://devfeed.tech/tags/filter.md>), [how-to](<https://devfeed.tech/tags/how-to.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [multiset](<https://devfeed.tech/tags/multiset.md>), [multiset-agg](<https://devfeed.tech/tags/multiset-agg.md>), [nested-collections](<https://devfeed.tech/tags/nested-collections.md>), [order-of-operations](<https://devfeed.tech/tags/order-of-operations.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [predicate](<https://devfeed.tech/tags/predicate.md>), [sql](<https://devfeed.tech/tags/sql.md>)

### AI overview

This tutorial explains how to filter SQL nested collections by a value. It compares filtering arrays through a derived table with using PostgreSQL's ARRAY_AGG and group filtering, then shows a jOOQ version intended to work across supported relational database systems.

### Source excerpt

I stumbled upon a very interesting question on Stack Overflow about how to use jOOQ's MULTISET operator to nest a collection, and then filter the result by whether that nested collection contains a value. The question is jOOQ specific, but imagine, you have a query that nests collections using JSON in PostgreSQL. Assuming, as always, ... Continue reading How to Filter a SQL Nested Collection by a Value ->