SQLite JOIN ... USING - rowid works, but then it doesn't

This works.

SELECT *
FROM Demographics
JOIN MeasuresofBirthAndDeath
ON Demographics.rowid = MeasuresofBirthAndDeath.rowid;

This does not.

SELECT *
FROM Demographics 
JOIN MeasuresofBirthandDeath
USING (rowid);

Why?

It seems that such query works fine for explicit primary keys.
I run this code:

CREATE TABLE Demographics (rowid int PRIMARY KEY);
CREATE TABLE MeasuresofBirthAndDeath (rowid int PRIMARY KEY);

insert into Demographics VALUES (1);
insert into MeasuresofBirthAndDeath VALUES (1);
insert into MeasuresofBirthAndDeath VALUES (2);

SELECT * FROM Demographics JOIN MeasuresofBirthandDeath USING (rowid);

And get correct result: 1

In case of using implicit rowid it really doesn't work. Let's see code:

CREATE TABLE Demographics (name text);
CREATE TABLE MeasuresofBirthAndDeath (name text);

insert into Demographics VALUES ('demo');
insert into MeasuresofBirthAndDeath VALUES ('birth');
insert into MeasuresofBirthAndDeath VALUES ('death');

SELECT * FROM Demographics JOIN MeasuresofBirthandDeath USING (rowid);
Error: cannot join using column rowid - column not present in both tables

We get error because rowid is not real column (https://www.sqlite.org/lang_createtable.html#rowid):


Except for WITHOUT ROWID tables, all rows within SQLite tables have a
64-bit signed integer key that uniquely identifies the row within its
table. This integer is usually called the "rowid". The rowid value can
be accessed using one of the special case-independent names "rowid",
"oid", or "rowid" in place of a column name. If a table contains a
user defined column named "rowid", "oid" or "rowid", then that name
always refers the explicitly declared column and cannot be used to
retrieve the integer rowid value.

In first select we explicit add condition to join and it works

ON Demographics.rowid = MeasuresofBirthAndDeath.rowid

But in second select there are no column in both table to apply USING to it.

You can ALTER your tables and create INTEGER PRIMARY KEY columns which would be alias for rowid and use JOIN USING on these new column:


if a rowid table has a primary key that consists of a single column
and the declared type of that column is "INTEGER" in any mixture of
upper and lower case, then the column becomes an alias for the rowid.
Such a column is usually referred to as an "integer primary key". A
PRIMARY KEY column only becomes an integer primary key if the declared
type name is exactly "INTEGER". Other integer type names like "INT" or
"BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary
key column to behave as an ordinary table column with integer affinity
and a unique index, not as an alias for the rowid.

StackExchange Database Administrators Q#131103, answer score: 5