SQL Server/T-SQL Tutorial/Table Join/LEFT OUTER JOIN
Содержание
Employees and Their Managers
5>
6> CREATE TABLE Employees
7> (
8> empid int NOT NULL,
9> mgrid int NULL,
10> empname varchar(25) NOT NULL,
11> salary money NOT NULL)
12> GO
1> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(1, NULL, "Nancy", $10000.00)
2> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(2, 1, "Andrew", $5000.00)
3> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(3, 1, "Janet", $5000.00)
4> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(4, 1, "Margaret", $5000.00)
5> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(5, 2, "Steven", $2500.00)
6> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(6, 2, "Michael", $2500.00)
7> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(7, 3, "Robert", $2500.00)
8> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(8, 3, "Laura", $2500.00)
9> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(9, 3, "Ann", $2500.00)
10> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(10, 4, "Ina", $2500.00)
11> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(11, 7, "David", $2000.00)
12> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(12, 7, "Ron", $2000.00)
13> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(13, 7, "Dan", $2000.00)
14> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(14, 11, "James", $1500.00)
15> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> SELECT
3> E.empname AS EmployeeName,
4> M.empname AS ManagerName
5> FROM
6> Employees AS E
7> LEFT OUTER JOIN
8> Employees AS M ON E.mgrid = M.empid;
9> GO
EmployeeName ManagerName
------------------------- -------------------------
Nancy NULL
Andrew Nancy
Janet Nancy
Margaret Nancy
Steven Andrew
Michael Andrew
Robert Janet
Laura Janet
Ann Janet
Ina Margaret
David Robert
Ron Robert
Dan Robert
James David
(14 rows affected)
1>
2>
3> drop table Employees;
4> GO
1>
2>
LEFT OUTER JOIN with condition
3> CREATE TABLE Customers (
4> CustomerID nchar (5) NOT NULL ,
5> CompanyName nvarchar (40) NOT NULL ,
6> ContactName nvarchar (30) NULL ,
7> ContactTitle nvarchar (30) NULL ,
8> Address nvarchar (60) NULL ,
9> City nvarchar (15) NULL ,
10> Region nvarchar (15) NULL ,
11> PostalCode nvarchar (10) NULL ,
12> Country nvarchar (15) NULL ,
13> Phone nvarchar (24) NULL ,
14> Fax nvarchar (24) NULL
15> )
16> GO
1>
2> INSERT Customers VALUES("1","A","Maria", "Sales", "Str. 57", "Berlin" ,NULL,"12209", "Germany","111-1111111","111-1111111")
3> INSERT Customers VALUES("2","M","Joe", "Owner", "Ave. 231","Vancouver" ,NULL,"05023", "Mexico", "(222) 222-3332",NULL)
4> INSERT Customers VALUES("3","H","Thomas", "Sales", "Sq. 111","London" ,NULL,"1D00P", "UK", "(444) 444-4444","(444) 444-4444")
5> INSERT Customers VALUES("4","B","Berg", "Order", "Blv 8","Toronto" ,NULL,"00222", "Sweden", "4444-55 55 65","5555-55 55 55")
6> INSERT Customers VALUES("5","S","Moos", "Sales", "Fort 57","New York" ,NULL,"68306", "Germany","6666-66666","6666-77777")
7> INSERT Customers VALUES("6","F","Cite", "Manager","24 ","Dalles" ,NULL,"67000", "France", "88.60.15.31","88.60.15.32")
8> INSERT Customers VALUES("7","C","Sommer", "Owner", "Araq, 67","Paris" ,NULL,"28023", "Spain", "(91) 555 22 82","(91) 555 91 99")
9> INSERT Customers VALUES("8","P","Leb", "Owner", "12 ","Beijing" ,NULL,"13008", "France", "91.24.45.40","91.24.45.41")
10> INSERT Customers VALUES("9","D","Elizabeth","Manager","23 Blvd.","Tsawassen","BC", "T2F8M4","Canada", "(604) 555-4729","(604) 555-3745")
11> go
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1> CREATE TABLE Orders (
2> OrderID int NOT NULL ,
3> CustomerID nchar (5) NULL ,
4> EmployeeID int NULL ,
5> OrderDate datetime NULL ,
6> RequiredDate datetime NULL ,
7> ShippedDate datetime NULL ,
8> ShipVia int NULL ,
9> Freight money NULL DEFAULT (0),
10> ShipName nvarchar (40) NULL ,
11> ShipAddress nvarchar (60) NULL ,
12> ShipCity nvarchar (15) NULL ,
13> ShipRegion nvarchar (15) NULL ,
14> ShipPostalCode nvarchar (10) NULL ,
15> ShipCountry nvarchar (15) NULL
16> )
17> GO
1>
2> SELECT c.CustomerID, CompanyName
3> FROM Customers c
4> LEFT OUTER JOIN Orders o
5> ON c.CustomerID = o.CustomerID
6> WHERE o.CustomerID IS NULL
7> GO
CustomerID CompanyName
---------- ----------------------------------------
1 A
2 M
3 H
4 B
5 S
6 F
7 C
8 P
9 D
(9 rows affected)
1>
2>
3> drop table Orders;
4> drop table Customers;
5> GO
Selecting titles that have not been sold.
7> CREATE TABLE sales(
8> stor_id char(4) NOT NULL,
9> ord_num varchar(20) NOT NULL,
10> ord_date datetime NOT NULL,
11> qty smallint NOT NULL,
12> payterms varchar(12) NOT NULL,
13> title_id varchar(80)
14> )
15> GO
1> insert sales values("1", "QA7442.3", "09/13/94", 75, "ON Billing","1")
2> insert sales values("2", "D4482", "09/14/94", 10, "Net 60", "1")
3> insert sales values("3", "N914008", "09/14/94", 20, "Net 30", "2")
4> insert sales values("4", "N914014", "09/14/94", 25, "Net 30", "3")
5> insert sales values("5", "423LL922", "09/14/94", 15, "ON Billing","3")
6> insert sales values("6", "423LL930", "09/14/94", 10, "ON Billing","2")
7> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> CREATE TABLE titles(
3> title_id varchar(20),
4> title varchar(80) NOT NULL,
5> type char(12) NOT NULL,
6> pub_id char(4) NULL,
7> price money NULL,
8> advance money NULL,
9> royalty int NULL,
10> ytd_sales int NULL,
11> notes varchar(200) NULL,
12> pubdate datetime NOT NULL
13> )
14> GO
1>
2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94")
3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91")
4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91")
5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91")
6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91")
7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91")
8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91")
9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91")
10> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> SELECT sales.qty, titles.title
3> FROM titles LEFT OUTER JOIN sales
4> ON titles.title_id = sales.title_id
5> WHERE sales.title_id IS NULL
6> GO
qty title
------ --------------------------------------------------------------------------------
NULL Prolonged
NULL With
NULL Valley
NULL Any?
NULL Fifty
(5 rows affected)
1> drop table sales;
2> drop table titles;
3> GO
1>
1>
Simple OUTER JOIN
The first syntax situation is the easy part - most people get this part just fine.
SELECT <SELECT list>
FROM <the table you want to be the "LEFT" table>
<LEFT|RIGHT> [OUTER] JOIN <table you want to be the "RIGHT" table>
ON <join condition>
The query uses non-ANSI outer join operators (*= or =*)
17>
18> create table Billings (
19> BankerID INTEGER,
20> BillingNumber INTEGER,
21> BillingDate datetime,
22> BillingTotal INTEGER,
23> TermsID INTEGER,
24> BillingDueDate datetime ,
25> PaymentTotal INTEGER,
26> CreditTotal INTEGER
27>
28> );
29> GO
1>
2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321);
3> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321);
2> GO
(1 rows affected)
1>
2>
3> create table Bankers(
4> BankerID Integer,
5> BankerName VARCHAR(20),
6> BankerContactLName VARCHAR(20),
7> BankerContactFName VARCHAR(20),
8> BankerCity VARCHAR(20),
9> BankerState VARCHAR(20),
10> BankerZipCode VARCHAR(20),
11> BankerPhone VARCHAR(20)
12> )
13> GO
1>
2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111");
3> GO
(1 rows affected)
1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222");
2> GO
(1 rows affected)
1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333");
2> GO
(1 rows affected)
1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444");
2> GO
(1 rows affected)
1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555");
2> GO
(1 rows affected)
1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666");
2> GO
(1 rows affected)
1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777");
2> GO
(1 rows affected)
1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888");
2> GO
(1 rows affected)
1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999");
2> GO
(1 rows affected)
1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000");
2> GO
(1 rows affected)
1>
2>
3>
4> SELECT BankerName, BillingNumber, BillingTotal
5> FROM Bankers, Billings
6> WHERE Bankers.BankerID *= Billings.BankerID
7> ORDER BY BankerName
8> GO
Msg 4147, Level 15, State 1, Server J\SQLEXPRESS, Line 7
The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_d
bcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not
be supported even in backward-compatibility modes.
1>
2> drop table Bankers;
3> drop table Billings;
4> GO