Skip to content

The JOIN Clause

Jump to bottom
Siim Kinks edited this page Mar 21, 2017 · 3 revisions

SqliteMagic supports all SQLite JOIN operations:

  • "," join (comma join)
  • LEFT [OUTER] JOIN
  • INNER JOIN
  • CROSS JOIN
  • NATURAL JOIN
  • NATURAL LEFT [OUTER] JOIN
  • NATURAL INNER JOIN
  • NATURAL CROSS JOIN

All of these JOIN methods can be called after the from method.

SQL SqliteMagic 
SELECT *
  FROM BOOK, AUTHOR
 
import static com.siimkinks.sqlitemagic.AuthorTable.AUTHOR;
import static com.siimkinks.sqlitemagic.BookTable.BOOK;

List<Book> books = Select
        .from(BOOK)
        .join(AUTHOR)
        .execute();

The JOIN ON Syntax

For specifying the JOIN .. ON clause, just call on method directly on the annotation processor generated table object.

SQL SqliteMagic 
   SELECT *
     FROM BOOK
LEFT JOIN AUTHOR
          ON AUTHOR.ID = BOOK.AUTHOR;
 
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)))
        .execute();

The JOIN USING Syntax

For JOIN .. USING clause, you supply a set of columns whose names are common to both tables to the left and right of a JOIN operation.

SQL SqliteMagic 
-- Assuming that both tables contain AUTHOR_ID column
   SELECT *
     FROM BOOK
LEFT JOIN AUTHOR USING (AUTHOR_ID);
 
import static com.siimkinks.sqlitemagic.AuthorTable.AUTHOR;
import static com.siimkinks.sqlitemagic.BookTable.BOOK;

List<Book> books = Select
        .from(BOOK)
        .leftJoin(AUTHOR
            .using(AUTHOR.AUTHOR_ID))
        .execute();

See Next

Clone this wiki locally