The UNION virtual table (hereafter: "union-vtab") is a virtual table that makes multiple independent rowid tables tables look like a single large table. The tables that participate in a union-vtab can be in the same database file, or ... 

 ... However, if the example had used UNION instead of UNION ALL, then SQLite would have had to keep around all previously generated content in order to check for duplicates. For this reason, programmers should strive to use UNION ALL instead ... 

 ... This involved making NULLs indistinct for the purposes of the SELECT DISTINCT statement and for the UNION operator in a SELECT. NULLs are still distinct in a UNIQUE column. This seems somewhat arbitrary, but the desire to be compatible with ... 

 ... ACID transactions using BEGIN, COMMIT, and ROLLBACK Nested transactions using SAVEPOINT, RELEASE, and ROLLBACK TO Subqueries, including correlated subqueries Up to 64-way joins LEFT, RIGHT, and FULL OUTER JOINs DISTINCT, ORDER BY, GROUP BY, HAVING, LIMIT, and OFFSET UNION ... 

1.4. Compound Queries Each component query of a compound query (UNION, UNION ALL, EXCEPT or INTERSECT) is assigned computed separately and is given its own line in the EXPLAIN QUERY PLAN output. sqlite> EXPLAIN QUERY PLAN SELECT a FROM t1 UNION SELECT ... 

1.1. Equivalent Recursive Common Table Expression  ... WITH RECURSIVE generate_series(value) AS ( SELECT $start UNION ALL SELECT value+$step FROM generate_series WHERE value+$step<=$end ) ... The common table expression works without having to load an extension. On the other hand, the extension is easier to program ... 

2.8. Transient Indices  ... Note that the UNION ALL operator for compound queries does not use transient indices by itself (though of course the right and left subqueries of the UNION ALL might use transient indices depending on how they are composed.)

 ... A compound SELECT created using the UNION ALL operator returns all the rows from the SELECT to the left of the UNION ALL operator, and all the rows from the SELECT to the right of it. The UNION operator works ... 

1.8. OR-Connected Terms In The WHERE Clause  ... One can see how the OR-by-UNION technique could also be leveraged to use multiple indices on queries where the WHERE clause has terms connected by AND, by using an intersect operator in place of union. Many SQL database ... 

 ... REPLACE RESTRICT RETURNING RIGHT ROLLBACK ROW ROWS SAVEPOINT SELECT SET TABLE TEMP TEMPORARY THEN TIES TO TRANSACTION TRIGGER UNBOUNDED UNION UNIQUE UPDATE USING VACUUM VALUES VIEW VIRTUAL WHEN WHERE WINDOW WITH WITHOUT This page last modified on 2022-11-26 ...