Join and sub query : Subquery « Subquery « SQL Server / T-SQL Tutorial






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> 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>    DECLARE @FirstDate smalldatetime
3>
4>    SELECT @FirstDate = MIN(OrderDate) FROM Orders
5>
6>    SELECT DISTINCT o.OrderDate, od.ProductID
7>    FROM Orders o
8>    JOIN OrderDetails od
9>       ON o.OrderID = od.OrderID
10>    WHERE o.OrderDate = @FirstDate
11> GO
OrderDate               ProductID
----------------------- -----------

(0 rows affected)
1>
2>    SELECT DISTINCT o.OrderDate, od.ProductID
3>    FROM Orders o
4>    JOIN OrderDetails od
5>       ON o.OrderID = od.OrderID
6>    WHERE o.OrderDate = (SELECT MIN(OrderDate) FROM Orders)
7> GO
OrderDate               ProductID
----------------------- -----------

(0 rows affected)
1>
2> drop table orders;
3> drop table OrderDetails;
4> GO
1>








8.1.Subquery
8.1.1.Building a Nested Subquery
8.1.2.Using Subqueries to Check for the Existence of Matches
8.1.3.Using Subqueries to Check for the Existence of Matches between two tables
8.1.4.Subqueries are SELECT statements that are nested within another T-SQL statement.
8.1.5.Subqueries and Comparison Operators: =
8.1.6.A subquery can be used with other comparison operators: <
8.1.7.Subqueries and IN Operator
8.1.8.A query that uses three subqueries
8.1.9.Doing calculation with subquery
8.1.10.Subqueries can be nested.
8.1.11.ID NOT IN (SELECT ID FROM Title)
8.1.12.Delete with subquery
8.1.13.Join and sub query
8.1.14.Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
8.1.15.subquery with >=
8.1.16.<> ALL with subquery