SQL SERVER 自连接、外连接

梦里梦外; 2022-07-24 12:28 286阅读 0赞

在数据库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);

![这里写图片描述][20160523210044087]  
在上面创建的数据中进行操作：

--内连接：
    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;

![这里写图片描述][20160523210244386]

--自连接：
    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;

![这里写图片描述][20160523210343559]

--左外连接：
      ---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;

![这里写图片描述][20160523210417450]

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

![这里写图片描述][20160523210448185]

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

![这里写图片描述][20160523210527967]

[20160523210044087]: /images/20220722/754ad98dfbaa44199f901af3947e132f.png
[20160523210244386]: /images/20220722/07308fb8bd9141a3bb195cfe274c972d.png
[20160523210343559]: /images/20220722/1bb42700cd764d9c8e17ed88bec149f1.png
[20160523210417450]: /images/20220722/99878e44f122464cbd9f12cb50e9abd2.png
[20160523210448185]: /images/20220722/8bfbdefd19f74829b924ad2679f227d0.png
[20160523210527967]: /images/20220722/8d61f1413b6749278e6550491d638727.png