advanced-query.md

Advanced Query

中文版请见这里

We have learned basic query and using SQL functions in query condition. Let's learn some query’s advanced skills.

Unions

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

In SQL, 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 UNION {...} block. There must be at least two SELECT statements inside the UNION {...} block, if not, you will get a IllegalStateException when 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 yet support subqueries, 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 a other table. And the Student represents the join query 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 of CROSS JOIN owns feature in SQL. If the columns that be queried by SELECT statement that with CROSS JOIN clause include the same name column in the two tables, this will causing the query to fail. Because of a class isn't allowed to have multiple properties those have same name, 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 in runtime.

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

Note, the behavior of INNER_JOIN clause with ON clause is same to CROSS_JOIN, you can't select the 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. That's liked you can bypass the INNER keyword in SQL's inner join query.

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 to INNER_JOIN, the difference just is their API names.

Finally

You have learned all usage with SQLlin, enjoy it and stay concerned about SQLlin's update :)