SQLite UNION ALL statement

The SQLite UNION ALL statement is similar to the SQLite UNION statement. The difference is that the SQLite UNION statement does not return duplicates, while the UNION ALL statement does.

The SQLite UNION ALL statement combines two datasets into one, maintaining duplicate entries. An example of relevant syntax is provided below:

SELECT col1
FROM table1
UNION ALL
SELECT col1
FROM table2;

A concrete database example can be seen here:

CREATE TABLE table1(
    name TEXT
);
 
INSERT INTO table1(name)
VALUES("Frida"),("Sam"),("Eric");
 
CREATE TABLE table2(
    name TEXT
);
INSERT INTO table2(name)
VALUES("Luis"),("Lenny"),("Sam");

SELECT name
  FROM table1
UNION
SELECT name
  FROM table2;

This returns the following names: Frida, Sam, Eric, Luis, Lenny, Sam.

Duplicates are marked with bold. The SQLite UNION ALL statement returns duplicates.

Close

Meta