SQLite 結合：ナチュラルレフトアウター、インナー、クロスウィズテーブル

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

はじめに SQLite JOIN句

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

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

JOIN 句は次の構文でなければなりません。

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

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

以下のすべてについて、 SQLite JOIN テーブルの例では、sqlite3.exe を実行し、サンプル データベースへの接続を次のように開く必要があります。

ステップ1） この手順では、「マイコンピュータ」を開き、「C:\sqlite」ディレクトリに移動して、「sqlite3.exe」を開きます。

ステップ2） 以下のコマンドを使用して、「TutorialsSampleDB.db」データベースを開きます。

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

SQLite INNER JOINは

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

例:

次の例では、「Students」テーブルと「Departments」テーブルをDepartmentIdで結合し、各学生の所属部署名を取得します。手順は以下のとおりです。

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

コードの説明

INNER JOIN は次のように動作します。

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

出力

INNER JOINは、学生テーブルと学科テーブルの両方から、「Students.DepartmentId = Departments.DepartmentId」という条件に一致するレコードを生成します。一致しない行は無視され、結果には含まれません。

そのため、IT、数学、物理学の各学科に所属する10名の学生のうち、このクエリでは8名のみが返されました。一方、「Jena」と「George」は、学科IDがnullであり、学科テーブルのdepartmentId列と一致しないため、含まれませんでした。以下に詳細を示します。

SQLite 参加…使用

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

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

INNER JOIN と JOIN の違い...使用方法:

「JOIN … USING」では、結合条件を記述する必要はなく、結合する 2 つのテーブルに共通する結合列を記述するだけです。例えば、table1 “INNER JOIN table2 ON table1.cola = table2.cola” と記述する代わりに、table1 JOIN table2 USING(cola) と記述します。

例:

次の例では、「Students」テーブルと「Departments」テーブルをDepartmentIdで結合し、各学生の所属部署名を取得します。手順は以下のとおりです。

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

説明

• 前の例とは異なり、「ON Students.DepartmentId = Departments.DepartmentId」とは書きませんでした。単に「USING(DepartmentId)」と書きました。
• SQLite 結合条件を自動的に推測し、両方のテーブル (Students と Departments) の DepartmentId を比較します。
• この構文は、比較する XNUMX つの列が同じ名前である場合にはいつでも使用できます。

出力

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

SQLite 自然結合

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

INNER JOIN と NATURAL JOIN の違い:

• INNER JOINでは、2つのテーブルを結合するために使用する結合条件を指定する必要があります。一方、Natural JOINでは、結合条件を記述する必要はありません。条件を指定せずに2つのテーブル名を記述するだけです。すると、Natural JOINは両方のテーブルに存在するすべての列の値の等価性を自動的にテストします。Natural JOINは結合条件を自動的に推測します。
• 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 を比較することで構成されている必要があることが検出されます。

出力

NATURAL JOIN は、INNER JOIN および JOIN USING の例で得られた出力とまったく同じ出力を返します。これは、この例では 3 つのクエリがすべて同等であるためです。ただし、場合によっては、INNER JOIN と Natural JOIN の出力が異なる場合があります。たとえば、同じ名前のテーブルが複数ある場合、Natural JOIN はすべての列を相互に照合します。一方、INNER JOIN は結合条件内の列のみを照合します。

SQLite 左外部結合

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

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

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

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

例:

次の例では、「LEFT JOIN」を使用して 2 つのテーブル「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 と同じです。 2 つのテーブル間に LEFT JOIN を記述すると、結合条件が ON 句の後に来ます。
• from 句の後の最初のテーブルが左側のテーブルです。 一方、自然な LEFT JOIN の後に指定された XNUMX 番目のテーブルが右側のテーブルになります。
• OUTER 句はオプションです。 LEFT の自然な OUTER JOIN は LEFT JOIN と同じです。

出力

ご覧のとおり、学生テーブルのすべての行（合計10名）が含まれています。4番目と最後の学生であるジェナとジョージの部署IDは、部署テーブルには存在しませんが、それでも含まれています。

そしてこれらの場合、部門テーブルに彼らの部門ID値に一致する部門名が存在しないため、ジェナとジョージの両方の部門名の値は「null」になります。

左結合を使用した前述のクエリについて、ベン図を用いてより詳細な説明をしてみましょう。

LEFT JOIN では、学生の部署 ID が departments テーブルに存在しない場合でも、students テーブルからすべての学生名が取得されます。そのため、このクエリは INNER JOIN のように一致する行だけを返すのではなく、左側のテーブル（students テーブル）から一致しない行を含む余分な部分も返します。

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

SQLite クロスジョイン

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

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

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

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

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

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

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

例:

次のクエリでは、Students テーブルと Departments テーブル間の CROSS JOIN を試みます。

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

説明

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

出力

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

• 学部テーブルの XNUMX つの学部の XNUMX つの値が、最初の学生のミシェルと一致しました。
• 部門表の4つの部門の4つの値が、2番目の学生であるジョンと一致した。
• 学科表の4つの学科の4つの値が3番目の学生ジャックと一致しました…以下同様です。

よくあるご質問