SQL Server/T-SQL Tutorial/Table Join/Inner join
Содержание
- 1 An inner join with correlation names that make the query more difficult to read
- 2 Inner join two table with column in common
- 3 Inner join with where clause
- 4 Once you decide to alias a table, you must use that alias in every part of the query.
- 5 Select distinct value from inner join
- 6 The explicit syntax for an inner join
- 7 The syntax for an inner join that uses correlation names
- 8 works with the qualified * operator
An inner join with correlation names that make the query more difficult to read
<source lang="sql">
4> 5> create table Billings ( 6> BankerID INTEGER, 7> BillingNumber INTEGER, 8> BillingDate datetime, 9> BillingTotal INTEGER, 10> TermsID INTEGER, 11> BillingDueDate datetime , 12> PaymentTotal INTEGER, 13> CreditTotal INTEGER 14> 15> ); 16> 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 BillingNumber, BankerName, BillingDueDate, 5> BillingTotal - PaymentTotal - CreditTotal AS BalanceDue 6> FROM Bankers AS v JOIN Billings AS i 7> ON v.BankerID = i.BankerID 8> WHERE BillingTotal - PaymentTotal - CreditTotal > 0 9> ORDER BY BillingDueDate DESC 10> GO BillingNumber BankerName BillingDueDate BalanceDue
-------------------- ----------------------- -----------
(0 rows affected) 1> 2> drop table Bankers; 3> drop table Billings; 4> GO</source>
Inner join two table with column in common
<source lang="sql">
5> CREATE TABLE Suppliers ( 6> SupplierID int NOT NULL , 7> CompanyName nvarchar (40) NOT NULL , 8> ContactName nvarchar (30) NULL , 9> ContactTitle nvarchar (30) NULL , 10> Address nvarchar (60) NULL , 11> City nvarchar (15) NULL , 12> Region nvarchar (15) NULL , 13> PostalCode nvarchar (10) NULL , 14> Country nvarchar (15) NULL , 15> Phone nvarchar (24) NULL , 16> Fax nvarchar (24) NULL , 17> HomePage ntext NULL 18> ) 19> GO 1> 2> 3> INSERT Suppliers VALUES(1,"L","N","Manager","L 10","Van",NULL,"2800","Paris","1114108","43844115",NULL) 4> INSERT Suppliers VALUES(2,"Z","D","Manager","V 22","Zaa",NULL,"9999 ZZ","USA"," 1212","(12345) 1210",NULL) 5> INSERT Suppliers VALUES(3,"K","A","Manager","V 12","Lap",NULL,"53120","Finland"," 10956",NULL,NULL) 6> INSERT Suppliers VALUES(4,"G","W","Tester", "1 Hill","Sydney","NSW","2042","Australia","(02) 555-5914","(021) 555-2222",null) 7> INSERT Suppliers VALUES(5,"M","J","Manager","2 St.","Mon","BC","H1J 1C3","Canada","(514) 555-9022",NULL,NULL) 8> INSERT Suppliers VALUES(6,"P","G","Administrator","V 153","Sal",NULL,"84100","Italy","(089) 6547665","(089) 1111111",NULL) 9> INSERT Suppliers VALUES(7,"E","M","Sales","22 Str","Mont",NULL,"71300","France","85.57.00.07",NULL,NULL) 10> INSERT Suppliers VALUES(8,"G","E","Sales","B Ave","Ann",NULL,"74000","France","38.76.98.06","38.76.98.58",NULL) 11> INSERT Suppliers VALUES(9,"F","C","Manager","1 Str","Ste","Calgary","J2S 7S8","Canada","(514) 555-2955","(514) 555-2921",NULL) 12> 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> 2> 3> CREATE TABLE Products ( 4> ProductID int NOT NULL , 5> ProductName nvarchar (40) NOT NULL , 6> SupplierID int NULL , 7> CategoryID int NULL , 8> QuantityPerUnit nvarchar (20) NULL , 9> UnitPrice money NULL, 10> UnitsInStock smallint NULL, 11> UnitsOnOrder smallint NULL, 12> ReorderLevel smallint NULL, 13> Discontinued bit NOT NULL 14> ) 15> GO 1> INSERT Products VALUES(1,"F",15,4,"10 - 999 g pkgs.",61.5,66,6,6,6) 2> INSERT Products VALUES(2,"M",14,4,"24 - 888 g pkgs.",34.8,74,7,7,7) 3> INSERT Products VALUES(3,"R",17,8,"24 - 777 g jars",17,171,0,5,0) 4> INSERT Products VALUES(4,"L",4,7,"5 kg pkg.",10,4,20,5,0) 5> INSERT Products VALUES(5,"R",12,1,"24 - 0.5 l bottles",1.23,445,0,25,0) 6> INSERT Products VALUES(6,"L",23,1,"500 ml",18,57,1,20,0) 7> INSERT Products VALUES(7,"O",12,2,"12 boxes",13,23,0,15,0) 8> go (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> FROM Products 4> INNER JOIN Suppliers 5> ON Products.SupplierID = Suppliers.SupplierID 6> GO ProductID ProductName SupplierID CategoryID QuantityPerUnit UnitPrice UnitsInStock UnitsOnOrder ReorderLevel Discontinued SupplierID CompanyName
ContactName ContactTitle Address City Region PostalCode Country
Phone Fax HomePage
---------------------------------------- ----------- ----------- -------------------- --------------------- ------------ ------------ ------------ ------------ ----------- ----------------
------------------------------ ------------------------------ ------------------------------------------------------------ --------------- --------------- ---------- ---------
------------------------ ------------------------ -----------------------------------------------------------------------------------------------------------------------------------------------
4 L 4 7 5 kg pkg. 10.0000 4 20 5 0 4 G
W Tester 1 Hill Sydney NSW 2042 Australia
(02) 555-5914 (021) 555-2222 NULL
(1 rows affected) 1> 2> drop table Products; 3> drop table Suppliers; 4> GO</source>
Inner join with where clause
<source lang="sql">
4> 5> 6> CREATE TABLE Products ( 7> ProductID int NOT NULL , 8> ProductName nvarchar (40) NOT NULL , 9> SupplierID int NULL , 10> CategoryID int NULL , 11> QuantityPerUnit nvarchar (20) NULL , 12> UnitPrice money NULL, 13> UnitsInStock smallint NULL, 14> UnitsOnOrder smallint NULL, 15> ReorderLevel smallint NULL, 16> Discontinued bit NOT NULL 17> ) 18> GO 1> INSERT Products VALUES(1,"F",15,4,"10 - 999 g pkgs.",61.5,66,6,6,6) 2> INSERT Products VALUES(2,"M",14,4,"24 - 888 g pkgs.",34.8,74,7,7,7) 3> INSERT Products VALUES(3,"R",17,8,"24 - 777 g jars",17,171,0,5,0) 4> INSERT Products VALUES(4,"L",4,7,"5 kg pkg.",10,4,20,5,0) 5> INSERT Products VALUES(5,"R",12,1,"24 - 0.5 l bottles",1.23,445,0,25,0) 6> INSERT Products VALUES(6,"L",23,1,"500 ml",18,57,1,20,0) 7> INSERT Products VALUES(7,"O",12,2,"12 boxes",13,23,0,15,0) 8> go (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> CREATE TABLE Suppliers ( 3> SupplierID int NOT NULL , 4> CompanyName nvarchar (40) NOT NULL , 5> ContactName nvarchar (30) NULL , 6> ContactTitle nvarchar (30) NULL , 7> Address nvarchar (60) NULL , 8> City nvarchar (15) NULL , 9> Region nvarchar (15) NULL , 10> PostalCode nvarchar (10) NULL , 11> Country nvarchar (15) NULL , 12> Phone nvarchar (24) NULL , 13> Fax nvarchar (24) NULL , 14> HomePage ntext NULL 15> ) 16> GO 1> 2> 3> INSERT Suppliers VALUES(1,"L","N","Manager","L 10","Van",NULL,"2800","Paris","1114108","43844115",NULL) 4> INSERT Suppliers VALUES(2,"Z","D","Manager","V 22","Zaa",NULL,"9999 ZZ","USA"," 1212","(12345) 1210",NULL) 5> INSERT Suppliers VALUES(3,"K","A","Manager","V 12","Lap",NULL,"53120","Finland"," 10956",NULL,NULL) 6> INSERT Suppliers VALUES(4,"G","W","Tester", "1 Hill","Sydney","NSW","2042","Australia","(02) 555-5914","(021) 555-2222",null) 7> INSERT Suppliers VALUES(5,"M","J","Manager","2 St.","Mon","BC","H1J 1C3","Canada","(514) 555-9022",NULL,NULL) 8> INSERT Suppliers VALUES(6,"P","G","Administrator","V 153","Sal",NULL,"84100","Italy","(089) 6547665","(089) 1111111",NULL) 9> INSERT Suppliers VALUES(7,"E","M","Sales","22 Str","Mont",NULL,"71300","France","85.57.00.07",NULL,NULL) 10> INSERT Suppliers VALUES(8,"G","E","Sales","B Ave","Ann",NULL,"74000","France","38.76.98.06","38.76.98.58",NULL) 11> INSERT Suppliers VALUES(9,"F","C","Manager","1 Str","Ste","Calgary","J2S 7S8","Canada","(514) 555-2955","(514) 555-2921",NULL) 12> 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> 2> 3> 4> 5> SELECT p.ProductID, s.SupplierID, p.ProductName, s.rupanyName 6> FROM Products p 7> INNER JOIN Suppliers s 8> ON p.SupplierID = s.SupplierID 9> WHERE p.ProductID < 4 10> GO ProductID SupplierID ProductName CompanyName
----------- ---------------------------------------- ----------------------------------------
(0 rows affected) 1> 2> drop table Products; 3> GO 1> 2> drop table Suppliers; 3> GO</source>
Once you decide to alias a table, you must use that alias in every part of the query.
<source lang="sql">
4> 5> 6> CREATE TABLE Products ( 7> ProductID int NOT NULL , 8> ProductName nvarchar (40) NOT NULL , 9> SupplierID int NULL , 10> CategoryID int NULL , 11> QuantityPerUnit nvarchar (20) NULL , 12> UnitPrice money NULL, 13> UnitsInStock smallint NULL, 14> UnitsOnOrder smallint NULL, 15> ReorderLevel smallint NULL, 16> Discontinued bit NOT NULL 17> ) 18> GO 1> INSERT Products VALUES(1,"F",15,4,"10 - 999 g pkgs.",61.5,66,6,6,6) 2> INSERT Products VALUES(2,"M",14,4,"24 - 888 g pkgs.",34.8,74,7,7,7) 3> INSERT Products VALUES(3,"R",17,8,"24 - 777 g jars",17,171,0,5,0) 4> INSERT Products VALUES(4,"L",4,7,"5 kg pkg.",10,4,20,5,0) 5> INSERT Products VALUES(5,"R",12,1,"24 - 0.5 l bottles",1.23,445,0,25,0) 6> INSERT Products VALUES(6,"L",23,1,"500 ml",18,57,1,20,0) 7> INSERT Products VALUES(7,"O",12,2,"12 boxes",13,23,0,15,0) 8> go (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> CREATE TABLE Suppliers ( 3> SupplierID int NOT NULL , 4> CompanyName nvarchar (40) NOT NULL , 5> ContactName nvarchar (30) NULL , 6> ContactTitle nvarchar (30) NULL , 7> Address nvarchar (60) NULL , 8> City nvarchar (15) NULL , 9> Region nvarchar (15) NULL , 10> PostalCode nvarchar (10) NULL , 11> Country nvarchar (15) NULL , 12> Phone nvarchar (24) NULL , 13> Fax nvarchar (24) NULL , 14> HomePage ntext NULL 15> ) 16> GO 1> 2> 3> INSERT Suppliers VALUES(1,"L","N","Manager","L 10","Van",NULL,"2800","Paris","1114108","43844115",NULL) 4> INSERT Suppliers VALUES(2,"Z","D","Manager","V 22","Zaa",NULL,"9999 ZZ","USA"," 1212","(12345) 1210",NULL) 5> INSERT Suppliers VALUES(3,"K","A","Manager","V 12","Lap",NULL,"53120","Finland"," 10956",NULL,NULL) 6> INSERT Suppliers VALUES(4,"G","W","Tester", "1 Hill","Sydney","NSW","2042","Australia","(02) 555-5914","(021) 555-2222",null) 7> INSERT Suppliers VALUES(5,"M","J","Manager","2 St.","Mon","BC","H1J 1C3","Canada","(514) 555-9022",NULL,NULL) 8> INSERT Suppliers VALUES(6,"P","G","Administrator","V 153","Sal",NULL,"84100","Italy","(089) 6547665","(089) 1111111",NULL) 9> INSERT Suppliers VALUES(7,"E","M","Sales","22 Str","Mont",NULL,"71300","France","85.57.00.07",NULL,NULL) 10> INSERT Suppliers VALUES(8,"G","E","Sales","B Ave","Ann",NULL,"74000","France","38.76.98.06","38.76.98.58",NULL) 11> INSERT Suppliers VALUES(9,"F","C","Manager","1 Str","Ste","Calgary","J2S 7S8","Canada","(514) 555-2955","(514) 555-2921",NULL) 12> 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> 2> 3> 4> SELECT p.*, Suppliers.SupplierID 5> FROM Products p 6> INNER JOIN Suppliers s 7> ON p.SupplierID = s.SupplierID 8> GO Msg 4104, Level 16, State 1, Server J\SQLEXPRESS, Line 4 The multi-part identifier "Suppliers.SupplierID" could not be bound. 1> 2> drop table Products; 3> GO 1> 2> drop table Suppliers; 3> GO</source>
Select distinct value from inner join
<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> 2> 3> CREATE TABLE Orders ( 4> OrderID int NOT NULL , 5> CustomerID nchar (5) NULL , 6> EmployeeID int NULL , 7> OrderDate datetime NULL , 8> RequiredDate datetime NULL , 9> ShippedDate datetime NULL , 10> ShipVia int NULL , 11> Freight money NULL DEFAULT (0), 12> ShipName nvarchar (40) NULL , 13> ShipAddress nvarchar (60) NULL , 14> ShipCity nvarchar (15) NULL , 15> ShipRegion nvarchar (15) NULL , 16> ShipPostalCode nvarchar (10) NULL , 17> ShipCountry nvarchar (15) NULL 18> ) 19> GO 1> 2> 3> INSERT INTO Orders VALUES (10248,"1",5,"7/4/1996","8/1/2001","7/16/2001",3,32.38,"V","A","R", NULL,N"51100","France") 7> go 1> 2> 3> SELECT DISTINCT c.CustomerID, c.rupanyName 4> FROM Customers c 5> INNER JOIN Orders o 6> ON c.CustomerID = o.CustomerID 7> GO CustomerID CompanyName
----------------------------------------
(0 rows affected) 1> 2> drop table orders; 3> drop table customers; 4> GO</source>
The explicit syntax for an inner join
<source lang="sql">
SELECT select_list FROM table_1
[INNER] JOIN table_2
ON join_condition_1
[[INNER] JOIN table_3
ON join_condition_2]...</source>
The syntax for an inner join that uses correlation names
<source lang="sql">
SELECT select_list FROM table_1 [AS] n1
[INNER] JOIN table_2 [AS] n2
ON n1.column_name operator n2.column_name
[[INNER] JOIN table_3 [AS] n3
ON n2.column_name operator n3.column_name]...</source>
works with the qualified * operator
<source lang="sql">
6> CREATE TABLE Products ( 7> ProductID int NOT NULL , 8> ProductName nvarchar (40) NOT NULL , 9> SupplierID int NULL , 10> CategoryID int NULL , 11> QuantityPerUnit nvarchar (20) NULL , 12> UnitPrice money NULL, 13> UnitsInStock smallint NULL, 14> UnitsOnOrder smallint NULL, 15> ReorderLevel smallint NULL, 16> Discontinued bit NOT NULL 17> ) 18> GO 1> INSERT Products VALUES(1,"F",15,4,"10 - 999 g pkgs.",61.5,66,6,6,6) 2> INSERT Products VALUES(2,"M",14,4,"24 - 888 g pkgs.",34.8,74,7,7,7) 3> INSERT Products VALUES(3,"R",17,8,"24 - 777 g jars",17,171,0,5,0) 4> INSERT Products VALUES(4,"L",4,7,"5 kg pkg.",10,4,20,5,0) 5> INSERT Products VALUES(5,"R",12,1,"24 - 0.5 l bottles",1.23,445,0,25,0) 6> INSERT Products VALUES(6,"L",23,1,"500 ml",18,57,1,20,0) 7> INSERT Products VALUES(7,"O",12,2,"12 boxes",13,23,0,15,0) 8> go (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> CREATE TABLE Suppliers ( 3> SupplierID int NOT NULL , 4> CompanyName nvarchar (40) NOT NULL , 5> ContactName nvarchar (30) NULL , 6> ContactTitle nvarchar (30) NULL , 7> Address nvarchar (60) NULL , 8> City nvarchar (15) NULL , 9> Region nvarchar (15) NULL , 10> PostalCode nvarchar (10) NULL , 11> Country nvarchar (15) NULL , 12> Phone nvarchar (24) NULL , 13> Fax nvarchar (24) NULL , 14> HomePage ntext NULL 15> ) 16> GO 1> 2> 3> INSERT Suppliers VALUES(1,"L","N","Manager","L 10","Van",NULL,"2800","Paris","1114108","43844115",NULL) 4> INSERT Suppliers VALUES(2,"Z","D","Manager","V 22","Zaa",NULL,"9999 ZZ","USA"," 1212","(12345) 1210",NULL) 5> INSERT Suppliers VALUES(3,"K","A","Manager","V 12","Lap",NULL,"53120","Finland"," 10956",NULL,NULL) 6> INSERT Suppliers VALUES(4,"G","W","Tester", "1 Hill","Sydney","NSW","2042","Australia","(02) 555-5914","(021) 555-2222",null) 7> INSERT Suppliers VALUES(5,"M","J","Manager","2 St.","Mon","BC","H1J 1C3","Canada","(514) 555-9022",NULL,NULL) 8> INSERT Suppliers VALUES(6,"P","G","Administrator","V 153","Sal",NULL,"84100","Italy","(089) 6547665","(089) 1111111",NULL) 9> INSERT Suppliers VALUES(7,"E","M","Sales","22 Str","Mont",NULL,"71300","France","85.57.00.07",NULL,NULL) 10> INSERT Suppliers VALUES(8,"G","E","Sales","B Ave","Ann",NULL,"74000","France","38.76.98.06","38.76.98.58",NULL) 11> INSERT Suppliers VALUES(9,"F","C","Manager","1 Str","Ste","Calgary","J2S 7S8","Canada","(514) 555-2955","(514) 555-2921",NULL) 12> 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> 2> SELECT Products.*, Suppliers.SupplierID 3> FROM Products 4> INNER JOIN Suppliers 5> ON Products.SupplierID = Suppliers.SupplierID 6> 7> GO ProductID ProductName SupplierID CategoryID QuantityPerUnit UnitPrice UnitsInStock UnitsOnOrder ReorderLevel Discontinued SupplierID
---------------------------------------- ----------- ----------- -------------------- --------------------- ------------ ------------ ------------ ------------ -----------
4 L 4 7 5 kg pkg. 10.0000 4 20 5 0 4
(1 rows affected) 1> 2> drop table Products; 3> GO 1> 2> drop table Suppliers; 3> GO</source>
