• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

SQL Question

Which orders contain widgets?
      Columns to display: ORDERS.orderid, ORDERS.salesdate
      The word 'widget' may not be the only word in the part description (use a wildcard).

so without using exist I get 14 records with this query:

SELECT Orders.orderid, Orders.salesdate
FROM  Inventory INNER JOIN
               OrderItems ON Inventory.partid = OrderItems.partid INNER JOIN
               Orders ON OrderItems.orderid = Orders.orderid
WHERE (Inventory.description LIKE 'WIDGET%')

But when I use EXISTS  with the query below  I get 21 records    why?

SELECT  Orders.orderid, Orders.salesdate
FROM  Inventory INNER JOIN
               OrderItems ON Inventory.partid = OrderItems.partid INNER JOIN
               Orders ON OrderItems.orderid = Orders.orderid
WHERE EXISTS
                   (SELECT description
                    FROM   Inventory AS Inventory_1
                    WHERE (description LIKE 'WIDGET%'))
GROUP BY Orders.orderid, Orders.salesdate
ORDER BY Orders.salesdate
0
ocdc
Asked:
ocdc
1 Solution
 
Pratima PharandeCommented:
Exists will consider the NULL descriptions also some extra rows
I think better to use first query
here you will get some details
http://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/
0
 
ocdcAuthor Commented:
my question is   How can I get the same result by using EXISTS as the first one which gives 14 records? Thanks
0
 
Scott PletcherSenior DBACommented:
The second EXISTS will always be true, so it does not screen out any orders.

Btw,  you need to use '%WIDGET%' for a full wildcard effect.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now