Inner join with where clause : Inner join « Table Join « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Table Join
3. Inner join

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.CompanyName
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