Skip to content

Nested Selects

Jump to bottom
Siim Kinks edited this page Mar 21, 2017 · 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

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