SQL Server/T-SQL Tutorial/Table Join/LEFT OUTER JOIN — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 13:25, 26 мая 2010
Содержание
Employees and Their Managers
<source lang="sql">
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></source>
LEFT OUTER JOIN with condition
<source lang="sql">
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</source>
Selecting titles that have not been sold.
<source lang="sql">
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></source>
Simple OUTER JOIN
<source lang="sql">
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