关于内、外链接的示例
时间:2011-04-30 来源:[守岁]
表如下
SET NOCOUNT ON;USE tempdb;
IF OBJECT_ID('dbo.Orders') IS NOT NULL DROP TABLE dbo.Orders;
IF OBJECT_ID('dbo.Customers') IS NOT NULL DROP TABLE dbo.Customers;
GO
CREATE TABLE dbo.Customers
(
customerid CHAR(5) NOT NULL PRIMARY KEY,
city VARCHAR(10) NOT NULL
);
CREATE TABLE dbo.Orders
(
orderid INT NOT NULL PRIMARY KEY,
customerid CHAR(5) NULL REFERENCES Customers(customerid)
);
GO
INSERT INTO dbo.Customers(customerid, city) VALUES('FISSA', 'Madrid');
INSERT INTO dbo.Customers(customerid, city) VALUES('FRNDO', 'Madrid');
INSERT INTO dbo.Customers(customerid, city) VALUES('KRLOS', 'Madrid');
INSERT INTO dbo.Customers(customerid, city) VALUES('MRPHS', 'Zion');
INSERT INTO dbo.Orders(orderid, customerid) VALUES(1, 'FRNDO');
INSERT INTO dbo.Orders(orderid, customerid) VALUES(2, 'FRNDO');
INSERT INTO dbo.Orders(orderid, customerid) VALUES(3, 'KRLOS');
INSERT INTO dbo.Orders(orderid, customerid) VALUES(4, 'KRLOS');
INSERT INTO dbo.Orders(orderid, customerid) VALUES(5, 'KRLOS');
INSERT INTO dbo.Orders(orderid, customerid) VALUES(6, 'MRPHS');
INSERT INTO dbo.Orders(orderid, customerid) VALUES(7, NULL);
这2张表可以很清晰的看出外链接
SELECT * FROM Customers JOIN Orders ON Customers.customerid = Orders.customeridSELECT * FROM Customers LEFT OUTER JOIN Orders ON Customers.customerid = Orders.customerid
SELECT * FROM Customers RIGHT OUTER JOIN Orders ON Customers.customerid = Orders.customerid
SELECT * FROM Customers FULL OUTER JOIN Orders ON Customers.customerid = Orders.customerid
其结果如下
customerid city orderid customerid
---------- ---------- ----------- ----------
FRNDO Madrid 1 FRNDO
FRNDO Madrid 2 FRNDO
KRLOS Madrid 3 KRLOS
KRLOS Madrid 4 KRLOS
KRLOS Madrid 5 KRLOS
MRPHS Zion 6 MRPHS
(6 行受影响)
customerid city orderid customerid
---------- ---------- ----------- ----------
FISSA Madrid NULL NULL
FRNDO Madrid 1 FRNDO
FRNDO Madrid 2 FRNDO
KRLOS Madrid 3 KRLOS
KRLOS Madrid 4 KRLOS
KRLOS Madrid 5 KRLOS
MRPHS Zion 6 MRPHS
(7 行受影响)
customerid city orderid customerid
---------- ---------- ----------- ----------
FRNDO Madrid 1 FRNDO
FRNDO Madrid 2 FRNDO
KRLOS Madrid 3 KRLOS
KRLOS Madrid 4 KRLOS
KRLOS Madrid 5 KRLOS
MRPHS Zion 6 MRPHS
NULL NULL 7 NULL
(7 行受影响)
customerid city orderid customerid
---------- ---------- ----------- ----------
FISSA Madrid NULL NULL
FRNDO Madrid 1 FRNDO
FRNDO Madrid 2 FRNDO
KRLOS Madrid 3 KRLOS
KRLOS Madrid 4 KRLOS
KRLOS Madrid 5 KRLOS
MRPHS Zion 6 MRPHS
NULL NULL 7 NULL
(8 行受影响)