概要
テーブルのカラムの型の指定は、多くのSQLでは必須ですが、SQLiteは指定しなくても定義可能です
しかしながら値による型の決定や、型有りと型無しカラムの結合のルールがややこしく
ハマりかけたのでまとめました
※今回扱う型はTEXT型のみです、他の型の挙動は試してません
１．型指定しなくてもテーブル定義できる
表題にある通りSQLiteはカラムに型を指定しなくてもテーブル定義が可能です
実際に試してみます
TEXT型指定カラム（COL_TEXT）、型指定無しカラム（COL_NON）を定義したテーブルを生成
CREATE TABLE TEST_TBL(
COL_TEXT TEXT
, COL_NON
);
上記作成テーブルに値を設定
--型無しカラムに英数字をシングルクォートありで設定
INSERT INTO TEST_TBL VALUES ('TEST1','TEST2');
--型無しカラムに英数字をシングルクォートなしで設定（エラーになる）
--INSERT INTO TEST_TBL VALUES ('TEST3',TEST4);
--型無しカラムに数値をシングルクォートありで設定
INSERT INTO TEST_TBL VALUES ('1','2');
--型無しカラムに数値をシングルクォート無しで設定
INSERT INTO TEST_TBL VALUES ('3',4);
登録内容を確認
select * from TEST_TBL;
型無しカラムにも値が設定されていることを確認
COL_TEXT COL_NON
---------------------
TEST1 TEST2
1 2
3 4
２．カラムの設定値の型の確認
カラムの値の型はTYPEOF関数を用い確認することができます
型指定されているカラムは、設定値にかかわらず、指定されている型が返されます
型指定無しのカラムについては登録時にシングルクォート付加の有無で型が決定します
（シングルクォート 有：TEXT、無し：INTEGER）
プレースフォルダを使用した場合は、プレースフォルダ側で型が判定されます
上記で作成したテーブルを例に試してみます
TYPEOF関数を用い、各カラムの型を取得
SELECT COL_TEXT,TYPEOF(COL_TEXT),COL_NON,TYPEOF(COL_NON) FROM TEST_TBL;
TEXT型で定義したカラムの場合、数値が入っていてもTEXT型と判定される：下記値「COL_TEXT:1」「COL_TEXT:3」
登録時にシングルクォートで囲んだ場合、数値が入っていてもTEXT型と判定される：下記値「COL_NON:2」
型指定無しのカラムで、且つ、登録時にシングルクォートで囲わない場合のみがINTEGERと判定：下記値「COL_NON:4」
COL_TEXT TYPEOF(COL_TEXT) COL_NON TYPEOF(COL_NON)
----------------------------------------------------------------
COL_TEXT TYPEOF(COL_TEXT) COL_NON TYPEOF(COL_NON)
TEST1 text TEST2 text
1 text 2 text
3 text 4 integer
３．型が同一でないと値が同一でも結合できない
型指定有りのカラムと型指定なしのカラム同士の場合、カラムの型と値の型が同じでないと値が同じでも結合できません
これも、上記で作成したテーブルに加え結合用のテーブルを作成し試してみます
上記１．と同じ型で構成されているテーブルを作成
CREATE TABLE TEST_TBL2(
COL_TEXT TEXT
, COL_NON
);
型指定カラムと同一値の値を型指定無しカラムにも設定した値を登録
--型無しカラムに文字をシングルクォートありで設定
INSERT INTO TEST_TBL2 VALUES ('TEST1','TEST1');
INSERT INTO TEST_TBL2 VALUES ('TEST2','TEST2');
--型無しカラムに数値をシングルクォートありで設定
INSERT INTO TEST_TBL2 VALUES ('1','1');
INSERT INTO TEST_TBL2 VALUES ('2','2');
--型無しカラムに数値をシングルクォート無しで設定
INSERT INTO TEST_TBL2 VALUES ('3',3);
INSERT INTO TEST_TBL2 VALUES ('4',4);
登録した値と型を確認
SELECT COL_TEXT,TYPEOF(COL_TEXT),COL_NON,TYPEOF(COL_NON) FROM TEST_TBL2;
COL_TEXT TYPEOF(COL_TEXT) COL_NON TYPEOF(COL_NON)
----------------------------------------------------------------
TEST1 text TEST1 text
TEST2 text TEST2 text
1 text 1 text
2 text 2 text
3 text 3 integer
4 text 4 integer
型指定あり同士で結合
まずは通常パターン、型指定ありのカラム同士で結合した場合を確認します
SELECT
TEST_TBL.COL_TEXT
, TEST_TBL2.COL_TEXT
FROM
TEST_TBL
, TEST_TBL2
WHERE
TEST_TBL.COL_TEXT = TEST_TBL2.COL_TEXT;
正しく結合された値が取得されることを確認
COL_TEXT COL_TEXT_1
--------------------
TEST1 TEST1
1 1
3 3
型指定あり、型指定なしで結合
次に本題の、型指定あり、型指定なしのカラム同士で結合した場合を確認
SELECT
TEST_TBL.COL_TEXT
, TEST_TBL2.COL_NON
FROM
TEST_TBL
, TEST_TBL2
WHERE
TEST_TBL.COL_TEXT = TEST_TBL2.COL_NON;
設定値が同一であっても同一の型同士でないと結合されません
値だけの一致では「TEXT1」「1」「3」が結合される想定ですが「3」が結合されていません
原因は、型指定ありのカラムの型がTEXTであるのに対し、型指定無しの値の型がINTEGERであるためです
COL_TEXT COL_NON
--------------------
TEST1 TEST1
1 1
型指定無し同士で結合
最後に、参考としてカラムの型指定無し同士での結合も試してみます
値の型が同じであれば、カラムの型指定がなくとも結合されます
※今回の例にはありませんが、型指定無しの場合も設定値が同一であっても同一の型同士でないと結合されません
SELECT
TEST_TBL.COL_NON
, TEST_TBL2.COL_NON
FROM
TEST_TBL
, TEST_TBL2
WHERE
TEST_TBL.COL_NON = TEST_TBL2.COL_NON;
COL_NON COL_NON_1
--------------------
TEST2 TEST2
2 2
4 4