SQL SERVER 自連線、外連線

在資料庫test2中新建表fruits、suppliers，並插入幾組資料：

USE test2
GO
BEGIN TRY DROP TABLE fruits END TRY BEGIN CATCH END CATCH;
BEGIN TRY DROP TABLE suppliers END TRY BEGIN CATCH END CATCH;
CREATE TABLE fruits
(
  f_id INT identity PRIMARY KEY NOT NULL,
  f_name VARCHAR(24) NULL,
  f_pricce FLOAT DEFAULT(0.0)
)

CREATE TABLE suppliers
(
 s_id INT identity PRIMARY KEY,
 s_name VARCHAR(32) NULL,
 s_city VARCHAR(32) NULL
)
GO
INSERT INTO fruits VALUES('a',1.0),('b',2.0),(NULL,3),('d',4),('e',4);
INSERT INTO suppliers VALUES('a','aa'),('b','bb'),('c','cc'),('d',NULL);

在上面建立的資料中進行操作：

--內連線：
SELECT f.f_id,s.s_id,s.s_name 
FROM fruits AS f INNER JOIN suppliers AS s 
ON f.f_id=s.s_id and f.f_id=1;

--自連線：
SELECT f1.f_id,f2.f_name 
FROM fruits AS f1,fruits AS f2
WHERE f1.f_id=f2.f_id AND f1.f_id=1;

--左外連線：
  ---fruits表中的資料f_id都會顯示，suppliers表中沒有匹配的s_id時，x_city值為NULL
SELECT fruits.f_id,suppliers.s_city
FROM fruits LEFT OUTER JOIN suppliers 
ON fruits.f_id=suppliers.s_id;

--右外連線：與左外連線相反
SELECT fruits.f_id,suppliers.s_city
FROM fruits RIGHT OUTER JOIN suppliers
ON fruits.f_id=suppliers.s_id;

--全外連線：條件匹配時返回資料，不匹配時相應的列中填入NULL值；
SELECT fruits.f_name,suppliers.s_city
FROM fruits FULL OUTER JOIN suppliers
ON fruits.f_id=suppliers.s_id;