Sql server® 2012 t-sql fundamentals

 

CHAPTER 3

 

Joins

 

 127

3

To get both customers who placed orders and customers who didn’t place orders in the result, you 

need to use an outer join, like this.

SELECT C.custid, C.companyname, O.orderid, O.orderdate 

FROM Sales.Customers AS C 

  LEFT OUTER JOIN Sales.Orders AS O 

    ON O.custid = C.custid;

This query returns 832 rows (including the customers 22 and 57, who didn’t place orders). An inner 

join between the tables would return only 830 rows, without those customers.

4

This exercise is an extension of the previous one. To return only customers who didn’t place orders, 

you need to add a WHERE clause to the query that filters only outer rows; namely, rows that represent 

customers with no orders. Outer rows have NULL marks in the attributes from the nonpreserved side 

of the join (Orders). But to make sure that the NULL is a placeholder for an outer row and not a NULL 

that originated from the table, it is recommended that you refer to an attribute that is the primary 

key, or the join column, or one defined as not allowing NULL marks. Here’s the solution query, which 

refers to the primary key of the Orders table in the WHERE clause.

SELECT C.custid, C.companyname 

FROM Sales.Customers AS C 

  LEFT OUTER JOIN Sales.Orders AS O 

    ON O.custid = C.custid 

WHERE O.orderid IS NULL;

This query returns only two rows, for customers 22 and 57, who didn’t place orders.

5

This exercise involves writing a query that performs an inner join between Customers and Orders and 

filters only rows in which the order date is February 12, 2007.

SELECT C.custid, C.companyname, O.orderid, O.orderdate 

FROM Sales.Customers AS C 

  JOIN Sales.Orders AS O 

    ON O.custid = C.custid 

WHERE O.orderdate = '20070212';

The WHERE clause filtered out customers who didn’t place orders on February 12, 2007, but that 

was the request. 

www.it-ebooks.info

---