Oracleテーブルの接続方法
原文:
http://database.51cto.com/art/201009/228094.htm
1.内部接続(自然接続)
2.外部接続
(1)左外接続(左の表に制限なし)
(2)右外部ジョイン(右側のテーブルに制限なし)
(3)全外接続(左右両表とも制限なし)
3.自己接続(同一テーブル内の接続)
SQLの標準構文:
inner joinは内部接続を表す.
left joinは左外接続を表す.
right joinは右外接続を表す.
full joinは完全な外部接続を表す.
on句は接続条件を指定するために使用されます.
注意:
from句を使用して内、外の接続を指定する場合は、on句を使用して接続条件を指定する必要があります.
(+)オペレータを使用して外部接続を指定する場合は、where句を使用して接続条件を指定する必要があります.
一.内部接続(Inner Join/Join)
1.1 Inner Join
Inner join論理演算子は、1番目(先頭)入力と2番目(下端)入力の結合を満たす各行を返します.これはselectでマルチテーブルをクエリーするのと同じ効果なので、内部接続用は少ないです.
もう一つ説明したいのは、Joinのデフォルトがinner joinです.だから私たちは内接続を書くときにinnerというキーワードを省略することができます.
1.2次に、内部接続の例を示す.
1.2.1まず2枚のテスト表を作成し、データを挿入する:
1.2.3内部リンクで検索する:
この3つのSQLの結果からも、彼らの役割は同じであることがわかります.
1.3自然接続(Natural join)
ナチュラル接続は、2つのテーブルでデータ型とカラム名が同じフィールドを探し、自動的に接続し、条件に合致するすべての結果を返します.
まず自然接続の例を見てみましょう.
ここでは接続の条件を指定していませんが、実際にoracleは、daveテーブルのidフィールドとnameフィールドをblテーブルのidフィールドとnameフィールドに接続します.つまり実際には
したがって,自然接続を内部接続の一種として理解することもできる.
自然接続に関する注意事項:
(1).自然接続を行う2つのテーブルの複数のフィールドが同じ名前とタイプを満たす場合、自然接続の条件として使用されます.
(2).自然に接続された2つのテーブルがフィールド名のみで同じであるが、データ型が異なる場合、エラーが返されます.
二.外部接続(Outer Join)
outer joinは、最初の(先端)入力と2番目の(下端)入力を満たす各結合の行を返します.また、2番目の入力に一致するローがない最初の入力のローも返します.外部接続には、左外部接続、右外部接続、全外部接続の3種類があります.SQL:LEFT/RIGHT/FUL OUTER JOIN対応.通常outerというキーワードは省略します.書き方:LEFT/RIGHT/FUL JOIN.
左外部ジョインと右外部ジョインでは、いずれも1つのテーブルに基づいて表示され、そのテーブルの内容はすべて表示され、2つのテーブルが一致する内容が追加されます.ベーステーブルのデータが別のテーブルに記録されていない場合.すると、関連付けられた結果セット行の列はNULLとして表示されます.
外部ジョインの場合は「(+)」で表すこともできます.使用(+)に関する注意事項:
1.(+)オペレータはwhere句にのみ表示され、outer join構文と同時に使用できません.
2.()オペレータを使用して外部接続を実行する場合、where句に複数の条件が含まれている場合は、すべての条件に(+)オペレータを含める必要があります.
3.(+)オペレータはカラムにのみ適用され、式では使用できません.
4.(+)オペレータはorとinオペレータと一緒に使用できません.
5.(+)オペレータは、左外部接続と右外部接続を実現するためにのみ使用でき、完全外部接続を実現するためには使用できません.
実験をする前に、daveテーブルとblにいくつかの異なるデータを追加します.テストを容易にします.
2.1左外部接続(Left outer join/left join)
left joinは左テーブルの記録に基づいており、例ではDaveは左テーブル、BLは右テーブルと見なすことができ、その結果セットはDaveテーブルのデータであり、DaveテーブルとBLテーブルが一致するデータを加える.すなわち,左テーブル(Dave)のレコードはすべて表示され,右テーブル(BL)は検索条件を満たすレコードのみが表示される.BLテーブルの記録不足はNULLである.
例:
[url]
SQL> select * from dave a left join bl b on a.id = b.id;
ID NAME ID NAME
--------- ---------- ---------- ----------
1 bl 1 dave
1 dave 1 dave
2 dave 2 bl
2 bl 2 bl
3 dba 3 big bird
4 sf-express 4 exc
5 dmm -- ここでBテーブルはnullです.
8安慶 -- ここでBテーブルはnullです.
SQL> select * from dave a left outer join bl b on a.id = b.id;
ID NAME ID NAME
---------- ---------- ---------- ----------
1 bl 1 dave
1 dave 1 dave
2 dave 2 bl
2 bl 2 bl
3 dba 3 big bird
4 sf-express 4 exc
5 dmm
8安慶
[/url]
(+)で実現すると、この+番号は、+が補足を表す、すなわち、どのテーブルにプラス記号があるか、このテーブルがマッチングテーブルであることを理解することができる.だからプラス記号は右の表に書いて、左の表はすべて表示して、だから左の接続です.
2.2右外部接続(right outer join/right join)
left joinの結果とは正反対に、右の表(BL)に基づいてBL表を表示しているので、DaveとBLがマッチした結果を加えて記録します.Daveテーブルが不足している箇所はNULLで埋める.
例:
7行を選択しました.
7行を選択しました.
(+)で実現すると、この+番号は、+が補足を表す、すなわち、どのテーブルにプラス記号があるか、このテーブルがマッチングテーブルであることを理解することができる.だからプラス記号は左の表に書いて、右の表はすべて表示して、だから右の接続です.
2.3全外部接続(full outer join/full join)
左の表も右の表も制限せず、すべてのレコードが表示され、2つの表が足りないところはnullで埋められます.全外接続では(+)という書き方はサポートされていません.
例:
9行を選択しました.
9行を選択しました.
三.自己接続
セルフジョイン(self join)はSQL文でよく使われるジョインです.セルフジョインを使用すると、自分のテーブルのミラーを別のテーブルとして扱うことができ、特殊なデータを得ることができます.
例:
Oracleのscottのschemaにはempというテーブルがあります.empのすべての従業員は自分のmgr(マネージャー)を持っていて、すべてのマネージャー自身も会社の従業員で、自身も自分のマネージャーを持っています.
次に、従業員一人一人の名前とマネージャーの名前を見つける必要があります.この時私たちはどうすればいいですか?
このような表を2枚持ってworkerとmgrをそれぞれ教えると、SQL文を書くのが上手になります.
Select worker.name,
Mgr.name
From worker,mgr
Where worker.id = mgr.id;
しかし、今はempテーブルが1枚しかありません.だから私たちは自己接続を採用することができます.自己接続の本意は,1枚のテーブルを複数のテーブルと見なして接続することである.SQL文を書くには、次のようにします.
14行を選択しました.
http://database.51cto.com/art/201009/228094.htm
1.内部接続(自然接続)
2.外部接続
(1)左外接続(左の表に制限なし)
(2)右外部ジョイン(右側のテーブルに制限なし)
(3)全外接続(左右両表とも制限なし)
3.自己接続(同一テーブル内の接続)
SQLの標準構文:
select table1.column,table2.column
from table1 [inner | left | right | full ] join table2 on table1.column1 = table2.column2;
inner joinは内部接続を表す.
left joinは左外接続を表す.
right joinは右外接続を表す.
full joinは完全な外部接続を表す.
on句は接続条件を指定するために使用されます.
注意:
from句を使用して内、外の接続を指定する場合は、on句を使用して接続条件を指定する必要があります.
(+)オペレータを使用して外部接続を指定する場合は、where句を使用して接続条件を指定する必要があります.
一.内部接続(Inner Join/Join)
1.1 Inner Join
Inner join論理演算子は、1番目(先頭)入力と2番目(下端)入力の結合を満たす各行を返します.これはselectでマルチテーブルをクエリーするのと同じ効果なので、内部接続用は少ないです.
もう一つ説明したいのは、Joinのデフォルトがinner joinです.だから私たちは内接続を書くときにinnerというキーワードを省略することができます.
1.2次に、内部接続の例を示す.
1.2.1まず2枚のテスト表を作成し、データを挿入する:
SQL> select * from dave;
ID NAME
---------- ----------
1 dave
2 bl
1 bl
2 dave
SQL> select * from bl;
ID NAME
---------- ----------
1 dave
2 bl
1.2.3内部リンクで検索する:
SQL> Select a.id,a.name,b.name from dave a inner join bl b on a.id=b.id; --
ID NAME NAME
---------- ---------- ----------
1 dave dave
2 bl bl
1 bl dave
2 dave bl
SQL> Select a.id,a.name,b.name from dave a join bl b on a.id=b.id; -- inner
ID NAME NAME
---------- ---------- ----------
1 dave dave
2 bl bl
1 bl dave
2 dave bl
SQL> Select a.id,a.name,b.name from dave a,bl b where a.id=b.id; -- select
ID NAME NAME
---------- ---------- ----------
1 dave dave
2 bl bl
1 bl dave
2 dave bl
この3つのSQLの結果からも、彼らの役割は同じであることがわかります.
1.3自然接続(Natural join)
ナチュラル接続は、2つのテーブルでデータ型とカラム名が同じフィールドを探し、自動的に接続し、条件に合致するすべての結果を返します.
まず自然接続の例を見てみましょう.
SQL> Select id,name from dave a natural join bl b;
ID NAME
---------- ----------
1 dave
2 bl
ここでは接続の条件を指定していませんが、実際にoracleは、daveテーブルのidフィールドとnameフィールドをblテーブルのidフィールドとnameフィールドに接続します.つまり実際には
SQL> Select dave.id,bl.name
From dave join bl on dave.id = bl.id and dave.name=bl.name;
ID NAME
---------- ----------
1 dave
2 bl
したがって,自然接続を内部接続の一種として理解することもできる.
自然接続に関する注意事項:
(1).自然接続を行う2つのテーブルの複数のフィールドが同じ名前とタイプを満たす場合、自然接続の条件として使用されます.
(2).自然に接続された2つのテーブルがフィールド名のみで同じであるが、データ型が異なる場合、エラーが返されます.
二.外部接続(Outer Join)
outer joinは、最初の(先端)入力と2番目の(下端)入力を満たす各結合の行を返します.また、2番目の入力に一致するローがない最初の入力のローも返します.外部接続には、左外部接続、右外部接続、全外部接続の3種類があります.SQL:LEFT/RIGHT/FUL OUTER JOIN対応.通常outerというキーワードは省略します.書き方:LEFT/RIGHT/FUL JOIN.
左外部ジョインと右外部ジョインでは、いずれも1つのテーブルに基づいて表示され、そのテーブルの内容はすべて表示され、2つのテーブルが一致する内容が追加されます.ベーステーブルのデータが別のテーブルに記録されていない場合.すると、関連付けられた結果セット行の列はNULLとして表示されます.
外部ジョインの場合は「(+)」で表すこともできます.使用(+)に関する注意事項:
1.(+)オペレータはwhere句にのみ表示され、outer join構文と同時に使用できません.
2.()オペレータを使用して外部接続を実行する場合、where句に複数の条件が含まれている場合は、すべての条件に(+)オペレータを含める必要があります.
3.(+)オペレータはカラムにのみ適用され、式では使用できません.
4.(+)オペレータはorとinオペレータと一緒に使用できません.
5.(+)オペレータは、左外部接続と右外部接続を実現するためにのみ使用でき、完全外部接続を実現するためには使用できません.
実験をする前に、daveテーブルとblにいくつかの異なるデータを追加します.テストを容易にします.
SQL> select * from bl;
ID NAME
---------- ----------
1 dave
2 bl
3 big bird
4 exc
9
SQL> select * from dave;
ID NAME
---------- ----------
8
1 dave
2 bl
1 bl
2 dave
3 dba
4 sf-express
5 dmm
2.1左外部接続(Left outer join/left join)
left joinは左テーブルの記録に基づいており、例ではDaveは左テーブル、BLは右テーブルと見なすことができ、その結果セットはDaveテーブルのデータであり、DaveテーブルとBLテーブルが一致するデータを加える.すなわち,左テーブル(Dave)のレコードはすべて表示され,右テーブル(BL)は検索条件を満たすレコードのみが表示される.BLテーブルの記録不足はNULLである.
例:
[url]
SQL> select * from dave a left join bl b on a.id = b.id;
ID NAME ID NAME
--------- ---------- ---------- ----------
1 bl 1 dave
1 dave 1 dave
2 dave 2 bl
2 bl 2 bl
3 dba 3 big bird
4 sf-express 4 exc
5 dmm -- ここでBテーブルはnullです.
8安慶 -- ここでBテーブルはnullです.
SQL> select * from dave a left outer join bl b on a.id = b.id;
ID NAME ID NAME
---------- ---------- ---------- ----------
1 bl 1 dave
1 dave 1 dave
2 dave 2 bl
2 bl 2 bl
3 dba 3 big bird
4 sf-express 4 exc
5 dmm
8安慶
[/url]
(+)で実現すると、この+番号は、+が補足を表す、すなわち、どのテーブルにプラス記号があるか、このテーブルがマッチングテーブルであることを理解することができる.だからプラス記号は右の表に書いて、左の表はすべて表示して、だから左の接続です.
SQL> Select * from dave a,bl b where a.id=b.id(+); -- ： （+） where
ID NAME ID NAME
---------- ---------- ---------- ----------
1 bl 1 dave
1 dave 1 dave
2 dave 2 bl
2 bl 2 bl
3 dba 3 big bird
4 sf-express 4 exc
5 dmm
8
2.2右外部接続(right outer join/right join)
left joinの結果とは正反対に、右の表(BL)に基づいてBL表を表示しているので、DaveとBLがマッチした結果を加えて記録します.Daveテーブルが不足している箇所はNULLで埋める.
例:
SQL> select * from dave a right join bl b on a.id = b.id;
ID NAME ID NAME
---------- ---------- ---------- ----------
1 dave 1 dave
2 bl 2 bl
1 bl 1 dave
2 dave 2 bl
3 dba 3 big bird
4 sf-express 4 exc
9 -- Null
7行を選択しました.
SQL> select * from dave a right outer join bl b on a.id = b.id;
ID NAME ID NAME
---------- ---------- ---------- ----------
1 dave 1 dave
2 bl 2 bl
1 bl 1 dave
2 dave 2 bl
3 dba 3 big bird
4 sf-express 4 exc
9 -- Null
7行を選択しました.
(+)で実現すると、この+番号は、+が補足を表す、すなわち、どのテーブルにプラス記号があるか、このテーブルがマッチングテーブルであることを理解することができる.だからプラス記号は左の表に書いて、右の表はすべて表示して、だから右の接続です.
SQL> Select * from dave a,bl b where a.id(+)=b.id;
ID NAME ID NAME
---------- ---------- ---------- ----------
1 dave 1 dave
2 bl 2 bl
1 bl 1 dave
2 dave 2 bl
3 dba 3 big bird
4 sf-express 4 exc
9
2.3全外部接続(full outer join/full join)
左の表も右の表も制限せず、すべてのレコードが表示され、2つの表が足りないところはnullで埋められます.全外接続では(+)という書き方はサポートされていません.
例:
SQL> select * from dave a full join bl b on a.id = b.id;
ID NAME ID NAME
---------- ---------- ---------- ----------
8
1 dave 1 dave
2 bl 2 bl
1 bl 1 dave
2 dave 2 bl
3 dba 3 big bird
4 sf-express 4 exc
5 dmm
9
9行を選択しました.
SQL> select * from dave a full outer join bl b on a.id = b.id;
ID NAME ID NAME
---------- ---------- ---------- ----------
8
1 dave 1 dave
2 bl 2 bl
1 bl 1 dave
2 dave 2 bl
3 dba 3 big bird
4 sf-express 4 exc
5 dmm
9
9行を選択しました.
三.自己接続
セルフジョイン(self join)はSQL文でよく使われるジョインです.セルフジョインを使用すると、自分のテーブルのミラーを別のテーブルとして扱うことができ、特殊なデータを得ることができます.
例:
Oracleのscottのschemaにはempというテーブルがあります.empのすべての従業員は自分のmgr(マネージャー)を持っていて、すべてのマネージャー自身も会社の従業員で、自身も自分のマネージャーを持っています.
次に、従業員一人一人の名前とマネージャーの名前を見つける必要があります.この時私たちはどうすればいいですか?
このような表を2枚持ってworkerとmgrをそれぞれ教えると、SQL文を書くのが上手になります.
Select worker.name,
Mgr.name
From worker,mgr
Where worker.id = mgr.id;
しかし、今はempテーブルが1枚しかありません.だから私たちは自己接続を採用することができます.自己接続の本意は,1枚のテーブルを複数のテーブルと見なして接続することである.SQL文を書くには、次のようにします.
SQL> select work.ename worker,mgr.ename manager from scott.emp work, scott.emp mgr
2 where work.mgr = mgr.empno(+)
3 order by work.ename;
WORKER MANAGER
---------- ----------
ADAMS SCOTT
ALLEN BLAKE
BLAKE KING
CLARK KING
FORD JONES
JAMES BLAKE
JONES KING
KING -- Null
MARTIN BLAKE
MILLER CLARK
SCOTT JONES
SMITH FORD
TURNER BLAKE
WARD BLAKE
14行を選択しました.