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
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
Inner join two table with column in common
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
Inner join with where clause
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
Once you decide to alias a table, you must use that alias in every part of the query.
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
Select distinct value from inner join
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
The explicit syntax for an inner join
SELECT select_list
FROM table_1
[INNER] JOIN table_2
ON join_condition_1
[[INNER] JOIN table_3
ON join_condition_2]...
The syntax for an inner join that uses correlation names
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]...
works with the qualified * operator
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