SELECT FROM (query that returns a regular result set) AS JOIN : Inline view « Subquery « SQL Server / T-SQL Tutorial






  1. SQL Server / T-SQL Tutorial
  2. Subquery
  3. Inline view
7>
8>
9>
10> CREATE TABLE Customers (
11>     CustomerID nchar (5) NOT NULL ,
12>     CompanyName nvarchar (40) NOT NULL ,
13>     ContactName nvarchar (30) NULL ,
14>     ContactTitle nvarchar (30) NULL ,
15>     Address nvarchar (60) NULL ,
16>     City nvarchar (15) NULL ,
17>     Region nvarchar (15) NULL ,
18>     PostalCode nvarchar (10) NULL ,
19>     Country nvarchar (15) NULL ,
20>     Phone nvarchar (24) NULL ,
21>     Fax nvarchar (24) NULL
22> )
23> 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>
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>
3> CREATE TABLE OrderDetails (
4>      OrderID int NOT NULL ,
5>      ProductID int NOT NULL ,
6>      UnitPrice money NOT NULL DEFAULT (0),
7>      Quantity smallint NOT NULL DEFAULT (1),
8>      Discount real NOT NULL DEFAULT (0)
9> )
10> GO
1> INSERT OrderDetails VALUES(10248,11,14,12,0)
2> INSERT OrderDetails VALUES(10248,42,9.8,10,0)
3> INSERT OrderDetails VALUES(10248,72,34.8,5,0)
4> INSERT OrderDetails VALUES(10249,14,18.6,9,0)
5> INSERT OrderDetails VALUES(10249,51,42.4,40,0)
6> INSERT OrderDetails VALUES(10250,41,7.7,10,0)
7> INSERT OrderDetails VALUES(10250,51,42.4,35,0.15)
8> INSERT OrderDetails VALUES(10250,65,16.8,15,0.15)
9> INSERT OrderDetails VALUES(10251,22,16.8,6,0.05)
10> INSERT OrderDetails VALUES(10251,57,15.6,15,0.05)
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 rows affected)
1>
2>
3>    SELECT DISTINCT c.CompanyName
4>    FROM Customers AS c
5>    JOIN
6>       (SELECT CustomerID
7>             FROM Orders o
8>             JOIN OrderDetails od
9>               ON o.OrderID = od.OrderID
10>             JOIN Products p
11>               ON od.ProductID = p.ProductID
12>             WHERE p.ProductName = 'Chocolade') AS spen
13>               ON c.CustomerID = spen.CustomerID
14>    JOIN
15>       (SELECT CustomerID
16>             FROM Orders o
17>             JOIN OrderDetails od
18>               ON o.OrderID = od.OrderID
19>             JOIN Products p
20>               ON od.ProductID = p.ProductID
21>             WHERE ProductName = 'Vegie-spread') AS spap
22>               ON c.CustomerID = spap.CustomerID
23> GO
CompanyName
----------------------------------------

(0 rows affected)
1>
2> drop table Products;
3> drop table OrderDetails;
4> drop table Orders;
5> drop table Customers;
6> GO








8.3.Inline view
8.3.1.Creating a Derived Table
8.3.2.Subqueries and Derived Tables
8.3.3.SELECT FROM (query that returns a regular result set) AS JOIN
8.3.4.A query that required a derived table.
8.3.5.In-Line Views (Derived Tables)
8.3.6.A query that uses a correlated subquery in its SELECT clause to retrieve the most recent Billing for each Banker
8.3.7.Queries in the FROM Clause
8.3.8.Select from nested select statements
8.3.9.Join with (inline)Table Expressions
8.3.10.Finding First Employee Using a Derived Table