advanced-query.md

Advanced Query

中文版请见这里

We have learned basic querying and using SQL functions in querying conditions. Let's learn some advanced skills of querying.

Unions

The UNION operator is used for merge two SELECT statements' results and these results must be of the same type.

In SQL, the UNION operator between with the two SELECT statements, but in SQLlin, we use a higher-order function to implement UNION:

fun sample() {
    lateinit var selectStatement: SelectStatement<Person>
    database {
        PersonTable { table ->
            selectStatement = UNION {
                table SELECT WHERE (age GTE 5)
                table SELECT WHERE (length(name) LTE 8)
            }
        }
    }
}

You just need to write your SELECT statements in the UNION {...} block. There must be at least two SELECT statements inside the UNION {...} block, if not, you will get a IllegalStateException at runtime.

If you want to use UNION and UNION ALL interchangeably, just use UNION {...} or UNION_ALL {...} block nesting:

fun sample() {
    lateinit var selectStatement: SelectStatement<Person>
    database {
        PersonTable { table ->
            selectStatement = UNION {
                table SELECT WHERE (age GTE 5)
                UNION_ALL {
                    table SELECT WHERE (length(name) LTE 8)
                    table SELECT WHERE (name EQ "Tom")
                }
            }
        }
    }
}

Above code equals the SQL:

SELECT * FROM person WHERE age >= 5
UNION
SELECT * FROM person WHERE length(name) <= 8
UNION ALL
SELECT * FROM person WHERE name = "Tom"

Subqueries

SQLlin doesn't support subqueries yet, we will develop as soon as possible.

Join

SQLlin supports joining tables now.

We need other two database entities:

@DBRow("transcript")
@Serializable
data class Transcript(
    val name: String?,
    val math: Int,
    val english: Int,
)

@Serializable
data class Student(
    val name: String?,
    val age: Int?,
    val math: Int,
    val english: Int,
)

@Serializable
data class CrossJoinStudent(
    val age: Int?,
    val math: Int,
    val english: Int,
)

The Transcript represents an other table. And the Student represents the join querying results' type (so Student doesn't need to be annotated @DBRow), it owns all column names that belong to Person and Transcript.

Cross Join

fun joinSample() {
    db {
        PersonTable { table ->
            table SELECT CROSS_JOIN<CrossJoinStudent>(TranscriptTable)
        }
    }
}

The CROSS_JOIN function receives one or multiple Tables as parameters. In normal SELECT statements, the statements' querying results' type is depended on the Table that be generated by sqllin-processor, but JOIN operator will change it to specific type. In above sample, CROSS_JOIN changes the type to CrossJoinStudent.

Note, because CROSS JOIN owns feature in SQL. If the columns that be queried by a SELECT statement that with CROSS JOIN clause include the same name columns in the two tables, this will cause the querying to fail. Because a class isn't allowed to have multiple properties those have same names, sqllin-dsl doesn't support the CROSS JOIN with columns of the same name.

Inner Join

fun joinSample() {
    db {
        PersonTable { table ->
            table SELECT INNER_JOIN<Student>(TranscriptTable) USING name
            table SELECT NATURAL_INNER_JOIN<Student>(TranscriptTable)
            table SELECT INNER_JOIN<CrossJoinStudent>(TranscriptTable) ON (name EQ TranscriptTable.name)
        }
    }
}

The INNER_JOIN is similar to CROSS_JOIN, the deference is INNER_JOIN need to connect a USING or ON clause. If a INNER JOIN statement without the USING or ON clause, it is incomplete, but your code still be compiled and will do nothing at runtime.

The NATURAL_INNER_JOIN will produce a complete SELECT statement (same with CROSS_JOIN). So, you can't add a USING or ON clause behind it, this is guaranteed by Kotlin compiler.

Note, the behavior of a INNER_JOIN clause with a ON clause is same with CROSS_JOIN, you can't select a column that has same name in two tables.

The INNER_JOIN have an alias that named JOIN, and NATURAL_INNER_JOIN also have an alias that named NATURAL_JOIN. This is just like you can bypass a INNER keyword in SQL's inner join querying.

Left Outer Join

fun joinSample() {
    db {
        PersonTable { table ->
            table SELECT LEFT_OUTER_JOIN<Student>(TranscriptTable) USING name
            table SELECT NATURAL_LEFT_OUTER_JOIN<Student>(TranscriptTable)
            table SELECT LEFT_OUTER_JOIN<CrossJoinStudent>(TranscriptTable) ON (name EQ TranscriptTable.name)
        }
    }
}

The LEFT_OUTER_JOIN's usage is very similar with INNER_JOIN, the difference just is their API names.

Finally

You have learned all usages with SQLlin, enjoy it and stay Stay tuned for SQLlin's updates :)