SQL SERVER 自连接、外连接

在数据库test2中新建表fruits、suppliers，并插入几组数据：

USE test2GOBEGIN 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)GOINSERT 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 f2WHERE f1.f_id=f2.f_id AND f1.f_id=1;

--左外连接：  ---fruits表中的数据f_id都会显示，suppliers表中没有匹配的s_id时，x_city值为NULLSELECT fruits.f_id,suppliers.s_cityFROM fruits LEFT OUTER JOIN suppliers ON fruits.f_id=suppliers.s_id;

--右外连接：与左外连接相反SELECT fruits.f_id,suppliers.s_cityFROM fruits RIGHT OUTER JOIN suppliersON fruits.f_id=suppliers.s_id;

--全外连接：条件匹配时返回数据，不匹配时相应的列中填入NULL值；SELECT fruits.f_name,suppliers.s_cityFROM fruits FULL OUTER JOIN suppliersON fruits.f_id=suppliers.s_id;