Sql server® 2012 t-sql fundamentals



Download 10,93 Mb.
Pdf ko'rish
bet182/443
Sana26.01.2022
Hajmi10,93 Mb.
#411755
1   ...   178   179   180   181   182   183   184   185   ...   443
Bog'liq
BookSQL

 

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




Download 10,93 Mb.

Do'stlaringiz bilan baham:
1   ...   178   179   180   181   182   183   184   185   ...   443




Ma'lumotlar bazasi mualliflik huquqi bilan himoyalangan ©hozir.org 2024
ma'muriyatiga murojaat qiling

kiriting | ro'yxatdan o'tish
    Bosh sahifa
юртда тантана
Боғда битган
Бугун юртда
Эшитганлар жилманглар
Эшитмадим деманглар
битган бодомлар
Yangiariq tumani
qitish marakazi
Raqamli texnologiyalar
ilishida muhokamadan
tasdiqqa tavsiya
tavsiya etilgan
iqtisodiyot kafedrasi
steiermarkischen landesregierung
asarlaringizni yuboring
o'zingizning asarlaringizni
Iltimos faqat
faqat o'zingizning
steierm rkischen
landesregierung fachabteilung
rkischen landesregierung
hamshira loyihasi
loyihasi mavsum
faolyatining oqibatlari
asosiy adabiyotlar
fakulteti ahborot
ahborot havfsizligi
havfsizligi kafedrasi
fanidan bo’yicha
fakulteti iqtisodiyot
boshqaruv fakulteti
chiqarishda boshqaruv
ishlab chiqarishda
iqtisodiyot fakultet
multiservis tarmoqlari
fanidan asosiy
Uzbek fanidan
mavzulari potok
asosidagi multiservis
'aliyyil a'ziym
billahil 'aliyyil
illaa billahil
quvvata illaa
falah' deganida
Kompyuter savodxonligi
bo’yicha mustaqil
'alal falah'
Hayya 'alal
'alas soloh
Hayya 'alas
mavsum boyicha


yuklab olish