Skip to content

Nested Selects

Jump to bottom
Siim Kinks edited this page Nov 25, 2016 · 4 revisions

A SELECT statement can appear almost anywhere a column can. Apart from many convenience methods accepting Select objects directly, a SELECT statement can always be transformed into a column object using the asColumn(String) method.

Example: Scalar Subquery

SQL SqliteMagic 
SELECT *
FROM BOOK
LEFT JOIN AUHTOR ON AUTHOR.ID = BOOK.AUTHOR
WHERE BOOK.AUTHOR = (
SELECT AUTHOR.ID
FROM AUTHOR
WHERE AUTHOR.LAST_NAME = 'Foo')
 
import static com.siimkinks.sqlitemagic.AuthorTable.AUTHOR;
import static com.siimkinks.sqlitemagic.BookTable.BOOK;
List<Book> books = Select
.from(BOOK)
.leftJoin(AUTHOR.on(AUTHOR.ID.is(BOOK.AUTHOR)))
.where(BOOK.AUTHOR.is(Select
.column(AUTHOR.ID)
.from(AUTHOR)
.where(AUTHOR.LAST_NAME.is("Foo"))))
.execute();

Example: Correlated subquery

SQL SqliteMagic 
SELECT AUTHOR.LAST_NAME, (
SELECT COUNT(*)
FROM BOOK
WHERE BOOK.AUTHOR = AUTHOR.ID)
AS books
FROM AUTHOR
  </code></pre>
</td>
<td style="padding:0; margin:0; border:none; width:50%;">
  <pre lang="java"><code class="language-java">import static com.siimkinks.sqlitemagic.AuthorTable.AUTHOR;

import static com.siimkinks.sqlitemagic.BookTable.BOOK;
import static com.siimkinks.sqlitemagic.Select.count;
CompiledSelect compiledSelect = Select
.columns(AUTHOR.NAME, Select
.column(count())
.from(BOOK)
.where(BOOK.AUTHOR.is(AUTHOR.ID))
.asColumn("books"))
.from(AUTHOR)
.compile();
_Query created above can be used to define a [SQL view](https://github.com/SiimKinks/sqlitemagic/wiki/Views), for example._

For more detailed information about subqueries see SQLite documentation.

See Next

Clone this wiki locally