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

Question

This works.

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

This does not.

SELECT *
FROM Demographics 
JOIN MeasuresofBirthandDeath
USING (rowid);

Why?

Answer 1

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.