ORDERS and PAYMENT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

ORDERS and PAYMENT

Author

Topic

frank88
Starting Member

4 Posts

Posted - 2008-12-02 : 10:23:51

Hi,

I've got two tables in my database, a table where orders are stored (Cust_Orders) and a table for payments (payment).

What is the code to show orders that have been made but do not appear in the payment table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 10:26:19

[code]SELECT o.*
FROM Cust_Orders o
LEFT JOIN payment p
ON p.OrderID=o.OrderID
WHERE p.OrderID IS NULL [/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 10:27:43

Also


SELECT o.*
FROM Cust_Orders o
WHERE NOT EXISTS (SELECT *
FROM payment
WHERE OrderID=o.OrderID)

jholovacs
Posting Yak Master

163 Posts

Posted - 2008-12-02 : 16:16:47

I would qualify viakh16's reply as follows:


SELECT o.*
FROM Cust_Orders o
WHERE NOT EXISTS (SELECT 1
FROM payment
WHERE OrderID=o.OrderID)

The difference being there's no need to do a SELECT * on an EXISTS clause, and in some cases that might give a performance hit.

___________________________
Geek At Large

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-02 : 16:26:30

quote:
Originally posted by jholovacs

I would qualify viakh16's reply as follows:

SELECT o.*
FROM Cust_Orders o
WHERE NOT EXISTS (SELECT 1
FROM payment
WHERE OrderID=o.OrderID)

The difference being there's no need to do a SELECT * on an EXISTS clause, and in some cases that might give a performance hit.

___________________________
Geek At Large

In this case I disagree.
EXISTS returns boolean hence * doesn't return any columns.

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

jholovacs
Posting Yak Master

163 Posts

Posted - 2008-12-02 : 16:52:46

EXISTS returns boolean, but in order for the EXISTS function to evaluate as TRUE,the SELECT inside must return a result. In the case of SELECT *, it returns a complete row, which is higher IO. In a table with many columns and many rows, I think there would be a noticeable performance hit; obviously, I have not tested this theory, and in this example it may be moot, but as a general rule, SELECTing more than you need is bad practice, wouldn't you agree?

___________________________
Geek At Large

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-03 : 07:22:07

quote:
SELECTing more than you need is bad practice, wouldn't you agree?

In general i agree but in our example my theory differs.

The Query Logical Flow (query processor) is:
1. Identify the data sources in FROM (and maybe join) clauses.
MS writes: "This produces a logical superset of all the columns and rows in all of the tables that participate in the query"

2. Filter the rows in superset by using the conditions in where clause.
My theorie: At this time the EXISTS operator can do its work and set the boolean result with no need to consider the select list.

3. Reduce the the columns set to the expressions that are actually used in the select list.

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

jholovacs
Posting Yak Master

163 Posts

Posted - 2008-12-03 : 09:13:29

hmm...

This has interested me enough that I tink I'm going to experiment with it. If I can get a working example going, I'll post the results.

___________________________
Geek At Large

frank88
Starting Member

4 Posts

Posted - 2008-12-07 : 16:59:59

with the first example... what is the difference between inner join and left join?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-07 : 17:05:44

INNER JOIN gives only the matching records from both tables.
LEFT JOIN gives all records and returns null for columns of missing records in the right table (payment).

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-07 : 20:32:36

quote:
Originally posted by frank88

with the first example... what is the difference between inner join and left join?

In first example i used left join as we want those which exist in orders but not in payment. this is same as taking left join of orders with payments and look for those that doesnt have a matching payment record.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources