SQLのUNIONを使ってみる (sqlite3)

$ bundle exec ruby --version
ruby 2.7.5p203 (2021-11-24 revision f69aeb8314) [x86_64-darwin19]

$ bundle exec rails --version
Rails 6.0.4.6

$ rails db
SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
sqlite> .schema sample_users
CREATE TABLE IF NOT EXISTS "sample_users" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar, "created_at" datetime(6) NOT NULL, "updated_at" datetime(6) NOT NULL);
sqlite> .schema sample_user_addresses
CREATE TABLE IF NOT EXISTS "sample_user_addresses" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "sample_user_id" integer, "city" varchar, "created_at" datetime(6) NOT NULL, "updated_at" datetime(6) NOT NULL);

sqlite> select name as sort_key from sample_users;
new name
sqlite> select city as sort_key from sample_user_addresses;
Tokyo
sqlite> select name as sort_key from sample_users
   ...> union
   ...> select city as sort_key from sample_user_addresses
   ...> order by sort_key asc
   ...> ;
Tokyo
new name
sqlite> select name as sort_key from sample_users
   ...> union
   ...> select city as sort_key from sample_user_addresses
   ...> order by sort_key desc
   ...> ;
new name
Tokyo