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;