advanced-query.md

Advanced Query

中文版请见这里

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

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 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 in 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) <= 5
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 a table now.

We need other two DBEntitys:

@DBRow("transcript")
@Serializable
data class Transcript(
    val name: String?,
    val math: Int,
    val english: Int,
): DBEntity<Transcript> {
    override fun kSerializer(): KSerializer<Transcript> = serializer()
}

@Serializable
data class Student(
    val name: String?,
    val age: Int?,
    val math: Int,
    val english: Int,
): DBEntity<Student> {
    override fun kSerializer(): KSerializer<Student> = serializer()
}

The Transcript represent a other table. And the Student represent the join query results' type(so Student doesn't need to be annotated @DBRow), it have all column name that belong to Person and Transcript.

Cross Join

fun joinSample() {
    db {
        PersonTable { table ->
            table SELECT CROSS_JOIN<Student>(TABLE<Transcript>(tableTranscript))
        }
    }
}

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

Inner Join

fun joinSample() {
    db {
        PersonTable { table ->
            table SELECT INNER_JOIN<Student>(TABLE<Transcript>(tableTranscript)) USING name
            table SELECT NATURAL_INNER_JOIN<Student>(TABLE<Transcript>(tableTranscript))
        }
    }
}

The INNER_JOIN is similar to CROSS_JOIN, the deference is INNER_JOIN need connect a USING clause. If a INNER JOIN statement without the USING clause, it is incomplete, but your code still compiles and will do nothing in runtime. Now, SQLlin just supports USING clause, and doesn't support ON clause, it will be supported In future versions.

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

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>(TABLE<Transcript>(tableTranscript)) USING name
            table SELECT NATURAL_LEFT_OUTER_JOIN<Student>(TABLE<Transcript>(tableTranscript))
        }
    }
}

The LEFT_OUTER_JOIN's usage is very similar to INNER_JOIN, but you should very careful to use it. Because of LEFT OUTER JOIN in SQL own behavior, the queries results will produce some row include null value. If your DBEntity that you expect deserialized have some non-null properties, you might get a crash while deserializing.

Finally

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