SQLite 結合: テーブルとの自然左外側、内側、交差の例

SQLite はさまざまなタイプをサポートしています SQL INNER JOIN、LEFT OUTER JOIN、CROSS JOIN などの結合。 このチュートリアルで説明するように、JOIN の各タイプは異なる状況に使用されます。

SQLite JOIN 句の概要

複数のテーブルを含むデータベースで作業している場合、多くの場合、これらの複数のテーブルからデータを取得する必要があります。

JOIN 句を使用すると、XNUMX つ以上のテーブルまたはサブクエリを結合してリンクできます。 また、どの列をどの条件でリンクする必要があるかを定義できます。

JOIN 句には次のものが必要です。wing 構文:

SQLite JOIN 句の構文
SQLite JOIN 句の構文

各結合句には次のものが含まれます。

  • 左側のテーブルであるテーブルまたはサブクエリ。 join 句の前 (左側) のテーブルまたはサブクエリ。
  • JOIN 演算子 – 結合タイプ (INNER JOIN、LEFT OUTER JOIN、または CROSS JOIN) を指定します。
  • JOIN 制約 – 結合するテーブルまたはサブクエリを指定した後、結合制約を指定する必要があります。結合制約は、結合タイプに応じて、その条件に一致する一致行が選択される条件となります。

すべてのフォローについては、wing SQLite JOIN テーブルの例では、sqlite3.exe を実行し、flo としてサンプル データベースへの接続を開く必要があります。wing:

ステップ1) このステップでは、

  1. 「マイ コンピュータ」を開き、次のページに移動しますwing ディレクトリ「c:\ sqlite"と
  2. 次に「」を開きますsqlite3.exe"

SQLite JOIN 句

ステップ2) データベースを開きます」チュートリアルSampleDB.db” by フォローwing コマンド:

SQLite JOIN 句

これで、データベースに対してあらゆる種類のクエリを実行する準備が整いました。

SQLite の内部結合

INNER JOIN は、結合条件に一致する行のみを返し、結合条件に一致しない他の行をすべて削除します。

SQLite の内部結合
SQLite の内部結合

続きましてwing たとえば、XNUMX つのテーブルを結合します。生徒"と"部門」をDepartmentIdと組み合わせて、次のように各学生の学部名を取得します。

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

コードの説明

INNER JOIN は次のように機能します。wing:

  • Select 句では、XNUMX つの参照テーブルから選択したい列を選択できます。
  • INNER JOIN 句は、From 句で参照される最初のテーブルの後に記述されます。
  • 次に結合条件をONで指定します。
  • 参照されるテーブルには別名を指定できます。
  • INNER ワードはオプションで、単に JOIN と書くことができます。

出力

SQLite INNER JOIN の例

  • INNER JOIN は、学生と学部のテーブルの両方から、条件に一致するレコードを生成します。Students.DepartmentId = 学科.DepartmentId ”。 一致しない行は無視され、結果には含まれません。
  • このクエリから IT、数学、物理学科の学生 8 人中 10 人だけが返されたのはそのためです。 一方、学生の「Jena」と「George」は含まれませんでした。これは、彼らの部門 ID が null であり、部門テーブルの部門 ID 列と一致しないためです。 以下のようにwing:

    SQLite INNER JOIN の例

SQLite JOIN … 使用

INNER JOIN は、冗長性を避けるために「USING」句を使用して記述することができるため、「ON Students.DepartmentId =Departments.DepartmentId」と記述する代わりに、単に「USING(DepartmentID)」と記述することができます。

結合条件で比較する列が同じ名前である場合は常に、「JOIN .. USING」を使用できます。 このような場合、on 条件を使用してそれらを繰り返す必要はなく、列名を指定するだけで SQLite がそれを検出します。

INNER JOIN と JOIN の違い .. USING:

「JOIN … USING」では、結合条件を記述せず、table1 「INNER JOIN table2 ON table1.cola = table2.cola」と記述する代わりに、結合された 1 つのテーブル間で共通する結合列を記述するだけです。 「table2 JOIN tableXNUMX USING(cola)」のようになります。

続きましてwing たとえば、XNUMX つのテーブルを結合します。生徒"と"部門」をDepartmentIdと組み合わせて、次のように各学生の学部名を取得します。

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
INNER JOIN Departments USING(DepartmentId);

説明

  • 前の例とは異なり、「」とは書きませんでした。ON Students.DepartmentId = 学科.DepartmentId”。 先ほど書いた「USING(部門ID)"
  • SQLiteの 結合条件を自動的に推測して補完しますares 両方のテーブル (Students とDepartment) からのDepartmentId。
  • この構文は、比較する XNUMX つの列が同じ名前である場合にはいつでも使用できます。

出力

  • これにより、前の例とまったく同じ結果が得られます。

SQLite JOIN の例

SQLiteの自然結合

NATURAL JOIN は JOIN…USING に似ていますが、両方のテーブルに存在するすべての列の値が等しいかどうかを自動的にテストする点が異なります。

INNER JOIN と NATURAL JOIN の違い:

  • INNER JOIN では、内部結合が XNUMX つのテーブルを結合するために使用する結合条件を指定する必要があります。 一方、自然結合では、結合条件を記述しません。 XNUMX つのテーブルの名前を無条件に書き込むだけです。 その後、自然結合により、両方のテーブルに存在するすべての列の値が等しいかどうかが自動的にテストされます。 自然結合では、結合条件が自動的に推測されます。
  • NATURAL JOIN では、両方のテーブルの同じ名前を持つすべての列が相互に照合されます。 たとえば、共通の XNUMX つの列名を持つ XNUMX つのテーブルがある場合 (XNUMX つのテーブルに同じ名前の XNUMX つの列が存在します)、自然結合では、一方の列の値だけでなく、両方の列の値を比較することによって XNUMX つのテーブルが結合されます。カラム。

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
Natural JOIN Departments;

説明

  • (INNER JOIN で行ったように) 列名を使用して結合条件を記述する必要はありません。 (JOIN USING で行ったように) 列名を一度も記述する必要はありませんでした。
  • 自然結合では、XNUMX つのテーブルの両方の列がスキャンされます。 この条件は、Students とDepartment という XNUMX つのテーブルの両方からのDepartmentId を比較することで構成されている必要があることが検出されます。

出力

SQLite NATURAL JOIN の例

  • Natural JOIN では、INNER JOIN および JOIN USING の例から得た出力とまったく同じ出力が得られます。この例では、XNUMX つのクエリはすべて同等であるためです。ただし、場合によっては、出力は内部結合と自然結合とは異なります。たとえば、同じ名前のテーブルがさらにある場合、自然結合はすべての列を相互に照合します。ただし、内部結合は結合条件内の列のみに一致します (詳細は、tails 次のセクションで。内部結合と自然結合の違い)。

SQLiteの左外部結合

SQL 標準では、LEFT、RIGHT、FULL の XNUMX 種類の OUTER JOIN が定義されていますが、SQLite は自然な LEFT OUTER JOIN のみをサポートします。

LEFT OUTER JOIN では、左側のテーブルから選択した列のすべての値が結果に含まれます。 クエリーしたがって、値が結合条件に一致するかどうかに関係なく、結果に含まれます。

したがって、左側のテーブルに「n」行がある場合、クエリの結果には「n」行が含まれます。 ただし、右側のテーブルからの列の値については、結合条件に一致しない値がある場合は、「null」値が含まれます。

したがって、左結合の行数と同じ行数が得られます。 これにより、両方のテーブルから一致する行 (INNER JOIN の結果など) と、左側のテーブルから一致しない行が取得されます。

続きましてwing たとえば、「LEFT JOIN」を使用して XNUMX つのテーブル「Students」と「Departments」を結合してみます。

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students             -- this is the left table
LEFT JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

説明

  • SQLite LEFT JOIN 構文は INNER JOIN と同じです。 XNUMX つのテーブル間に LEFT JOIN を記述すると、結合条件が ON 句の後に来ます。
  • from 句の後の最初のテーブルが左側のテーブルです。 一方、自然な LEFT JOIN の後に指定された XNUMX 番目のテーブルが右側のテーブルになります。
  • OUTER 句はオプションです。 LEFT の自然な OUTER JOIN は LEFT JOIN と同じです。

出力

SQLite LEFT OUTER JOIN の例

  • ご覧のとおり、students テーブルのすべての行が含まれており、合計 10 人の学生が含まれています。 XNUMX 番目で最後の学生である Jena と George の部門 ID がDepartments テーブルに存在しない場合でも、それらも同様に含まれます。
  • このような場合、DepartmentId 値に一致する部門名が部門テーブルにないため、Jena と George の両方の部門名値は「null」になります。

左結合を使用した前のクエリを、Van 図を使用してさらに詳しく説明してみましょう。

SQLiteの左外部結合

SQLiteの左外部結合

LEFT JOIN では、学生の部門 ID が部門テーブルに存在しない場合でも、学生テーブルのすべての学生の名前が与えられます。 したがって、クエリは INNER JOIN として一致する行だけを取得するのではなく、学生テーブルである左側のテーブルから一致しない行を含む追加部分を取得します。

一致する学部がない学生名は、学部名に「null」値を持つことに注意してください。これは、それに一致する値がなく、それらの値が一致しない行の値であるためです。

SQLite クロスジョイン

CROSS JOIN は、最初のテーブルのすべての値を XNUMX 番目のテーブルのすべての値と照合することにより、結合された XNUMX つのテーブルの選択された列のデカルト積を求めます。

したがって、最初のテーブルのすべての値について、XNUMX 番目のテーブルから「n」個の一致が得られます。ここで、n は XNUMX 番目のテーブルの行数です。

INNER JOIN や LEFT OUTER JOIN とは異なり、CROSS JOIN では、SQLite が CROSS JOIN に結合条件を必要としないため、結合条件を指定する必要はありません。

SQLite は、最初のテーブルのすべての値と XNUMX 番目のテーブルのすべての値を組み合わせて、論理的な結果セットを生成します。

たとえば、最初のテーブルから列 (colA) を選択し、1,2 番目のテーブルから別の列 (colB) を選択したとします。 ColA には 3,4 つの値 (XNUMX) が含まれており、colB にも XNUMX つの値 (XNUMX) が含まれています。

CROSS JOIN の結果は XNUMX 行になります。

  • ColA の最初の値 1 と、colB (3,4) の 1,3 つの値 (1,4)、(XNUMX) を組み合わせて XNUMX 行にします。
  • いいねwise、colA の 2 番目の値 3,4 と、colB (2,3) の 2,4 つの値 (XNUMX)、(XNUMX) を組み合わせて XNUMX 行を作成します。

続きましてwing クエリでは、Students テーブルとDepartments テーブルの間で CROSS JOIN を試します。

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
CROSS JOIN Departments;

説明

  • SQLite の複数テーブルからの選択では、students テーブルから「studentname」という XNUMX つの列を選択し、部門テーブルから「DepartmentName」を選択しただけです。
  • クロス結合については、結合条件を指定せず、XNUMX つのテーブルを中央で CROSS JOIN で結合するだけです。

出力

SQLite CROSS JOIN の例

ご覧のとおり、結果は 40 行になります。 学生テーブルの 10 個の値が、部門テーブルの 4 つの部門と一致しました。 以下のようにwing:

  • 学部テーブルの XNUMX つの学部の XNUMX つの値が、最初の学生のミシェルと一致しました。
  • 学部テーブルの XNUMX つの学部の XNUMX つの値が XNUMX 番目の学生 John と一致しました。
  • 学科テーブルの XNUMX つの学科の XNUMX つの値が XNUMX 番目の学生 Jack と一致しました…というように続きます。

まとめ

SQLite JOIN クエリを使用すると、XNUMX つ以上のテーブルまたはサブクエリをリンクして、両方のテーブルまたはサブクエリから列を選択できます。