[MSSQL,MySQL,Oracle] – Join用法

 下面代碼是在Microsoft SQL Server 2000 裡運行

-- 關於sql語句中的串連(join)關鍵字，是較為常用而又不太容易理解的關鍵字，下面這個例子給出了一個簡單的解釋(以下均在查詢分析器中執行)
-- 刪除已存在的表和資料庫
use master
if exists(select * from sysdatabases where name='Study_Join')
  begin
    if exists(select count(*) from sysobjects where id = object_id('Study_Join.dbo.table1'))
      begin
        drop table Study_Join.dbo.table1
      end
    if exists(select count(*) from sysobjects where id = object_id('Study_Join.dbo.table2'))
      begin
        drop table Study_Join.dbo.table2
      end
    drop database Study_Join;
  end

-- 建資料庫Study_Join：
create database Study_Join;
go

use Study_Join;
-- 建表table1,table2：
create table table1(id int,name varchar(10));
  insert into table1 values(1,'lee');
  insert into table1 values(2,'zhang');
  insert into table1 values(4,'wang');

create table table2(id int,score int);
  insert into table2 values(1,90);
  insert into table2 values(2,100);
  insert into table2 values(3,70);

/*
-- Oracle不支援此寫法,MSSQL與MySQL支援
create table table1(id int,name varchar(10));
  insert into table1 select 1,'lee';
  insert into table1 select 2,'zhang';
  insert into table1 select 4,'wang';

create table table2(id int,score int);
  insert into table2 select 1,90;
  insert into table2 select 2,100;
  insert into table2 select 3,70;
*/

/*
結果如下表所示：
    table1          table2
-------------------------------
 id     name   | id     score |
-------------------------------
 1      lee    | 1      90    |
 2      zhang  | 2      100   |
 4      wang   | 3      70    |
-------------------------------
*/

-- ****************************************************************************
-- 一、外串連
-- 左(外)串連(left join 等價於 left outer join)
select * from table1 left outer join table2 on table1.id=table2.id;
/*
注釋：包含table1的所有子句，根據指定條件返回table2相應的欄位，不符合的以null顯示
------------------------------
 id     name    id      score
------------------------------
 1      lee     1       90
 2      zhang   2       100
 4      wang    null    null
------------------------------
*/

-- 右(外)串連(right join 等價於 right outer join)
select * from table1 right outer join table2 on table1.id=table2.id;
/*
注釋：包含table2的所有子句，根據指定條件返回table1相應的欄位，不符合的以null顯示
------------------------------
 id     name    id      score
------------------------------
 1      lee     1       90
 2      zhang   2       100
 null   null    3       70
------------------------------
*/

-- 全(外)串連(full join 等價於 full outer join)
-- MySQL不支援此寫法,MSSQL,Oracle支援
select * from table1 full  outer join table2 on table1.id=table2.id;

-- MSSQL,MySQL,Oracle都支援
select * from table1 left  outer join table2 on table1.id=table2.id
union
select * from table1 right outer join table2 on table1.id=table2.id;

/*
-- 三個表
select * from table1 left  outer join table2 on table1.id = table2.id left  outer join table3 on table2.id = table3.id
union
select * from table1 right outer join table2 on table1.id = table2.id left  outer join table3 on table2.id = table3.id
union
select * from table1 right outer join table2 on table1.id = table2.id right outer join table3 on table2.id = table3.id
*/
/*
注釋：返回左右串連的並集(見上左、右串連)
------------------------------
 id     name    id      score
------------------------------
 1      lee     1       90
 2      zhang   2       100
 4      wang    null    null
 null   null    3       70
------------------------------
*/

-- ****************************************************************************
-- 二、內串連
-- 內串連(join 等價於 inner join)
select * from table1 inner join table2 on table1.id=table2.id;
/*
注釋：返回左右串連的交集(見上左、右串連)
------------------------------
 id     name    id      score
------------------------------
 1      lee     1       90
 2      zhang   2       100
------------------------------
等價(與下列執行效果相同)
select a.*,b.* from table1 a,table2 b where a.id=b.id;
select * from table1 cross join table2 where table1.id=table2.id; -- 註：cross join後加條件只能用where,不能用on
*/

-- ****************************************************************************
-- 三、交叉串連(完全)
select * from table1 cross join table2;
/*
注釋：返回3*3=9條記錄，即笛卡爾積
------------------------------
id      name    id      score
------------------------------
 1      lee     1       90
 2      zhang   1       90
 4      wang    1       90
 1      lee     2       100
 2      zhang   2       100
 4      wang    2       100
 1      lee     3       70
 2      zhang   3       70
 4      wang    3       70
------------------------------
*/