6

This works.

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

This does not.

SELECT *
FROM Demographics 
JOIN MeasuresofBirthandDeath
USING (rowid);

Why?

1 Answer 1

6

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.

4
  • SQLiteManager: Likely SQL syntax error: SELECT * FROM Demographics JOIN MeasuresofBirthandDeath USING (rowid); [ cannot join using column rowid - column not present in both tables ]
    – Kevin
    Commented Mar 3, 2016 at 3:21
  • rowid is not an explicitly created COL, it is the implicitly created rowid that is attached by SQLite when I import these CSV.
    – Kevin
    Commented Mar 3, 2016 at 3:22
  • I edit comment and add some info. You are right about JOIN USING implicit rowid field. You can create INTEGER PRIMARY KEY field with same name in both tables which would be just alias for rowid and use it for JOIN USING. Commented Mar 3, 2016 at 3:57
  • Yeah, I was mostly wondering why rowid worked with JOIN ... ON, but not with JOIN ... USING. It seems counterintuitive.
    – Kevin
    Commented Mar 3, 2016 at 4:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.