9 Replies - 921 Views - Last Post: 03 December 2008 - 09:23 AM Rate Topic: -----

#1 Jon1001  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 31-January 08

SQL Query Help

Posted 02 December 2008 - 01:16 PM

I've been trying to write an SQL query but I can't make it work. I will give you an example of what I'm trying to do. Say I had 2 tables with the following fields:

Customers:
customer_id
name
address

Accounts:
customer_id
amount
payment

The Customers table holds customer details. The Accounts table holds the id of the customer and details of their account but not all customers will have a corresponding record in accounts. What I want to do is select all of the customers who haven't got a corresponding record in accounts, i.e. all customers who don't have an account setup. I've tried a few join statements but I'm not very good at them. Anyone have any idea how to do this? If so, I'd be grateful for your help. Please tell me if this example doesn't make sense! Thanks.

Is This A Good Question/Topic? 0
  • +

Replies To: SQL Query Help

#2 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,957
  • Joined: 21-March 08

Re: SQL Query Help

Posted 02 December 2008 - 01:25 PM

here is the simplest of queries:

SELECT Customer_ID, Name, Address
FROM Customers WHERE NOT EXISTS (SELECT * FROM Accounts WHERE Accounts.Customer_ID = Customers.Customer_ID)

This post has been edited by eclipsed4utoo: 02 December 2008 - 01:30 PM

Was This Post Helpful? 0
  • +
  • -

#3 Jon1001  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 31-January 08

Re: SQL Query Help

Posted 02 December 2008 - 01:37 PM

Ahh, that was simple. Thanks so much. Works perfectly.

Huh, how come you changed it? I used the NOT IN one and it worked fine.
Was This Post Helpful? 0
  • +
  • -

#4 jens  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 67
  • View blog
  • Posts: 430
  • Joined: 09-May 08

Re: SQL Query Help

Posted 02 December 2008 - 01:38 PM

You almost wrote it yourself. A select that selects all that do NOT exist in the second table. :) Here is all you need in one page.

Edited: Sorry, I was a little slow there...

Regards
/Jens

This post has been edited by jens: 02 December 2008 - 01:41 PM

Was This Post Helpful? 0
  • +
  • -

#5 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,957
  • Joined: 21-March 08

Re: SQL Query Help

Posted 02 December 2008 - 02:40 PM

View PostJon1001, on 2 Dec, 2008 - 03:37 PM, said:

Ahh, that was simple. Thanks so much. Works perfectly.

Huh, how come you changed it? I used the NOT IN one and it worked fine.


I normally use NOT IN and NOT EXISTS interchangeably. However, I did a quick search and found on that they don't always return the same information. NOT IN will sometimes not return the intended results. NOT EXISTS will always return the intended results.

here is the article...

http://weblogs.sqlte...5/18/60210.aspx
Was This Post Helpful? 0
  • +
  • -

#6 Jon1001  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 31-January 08

Re: SQL Query Help

Posted 02 December 2008 - 03:00 PM

Oh ok, that makes sense, I'll use NOT EXISTS. Thanks again.
Was This Post Helpful? 0
  • +
  • -

#7 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5774
  • View blog
  • Posts: 12,586
  • Joined: 16-October 07

Re: SQL Query Help

Posted 02 December 2008 - 05:01 PM

Just a quick note, both NOT IN and NOT EXISTS don't scale well. They're nice if the sub quiery is small and will stay that way.

Another way to do what you're looking for is a standard join against a sub query. In general, this will be faster; in some databases, much faster.

SELECT a.Customer_ID, a.Name, a.Address
	FROM Customers a
		LEFT OUTER JOIN (SELECT distinct customer_id FROM Accounts WHERE Accounts) b
			ON a.customer_id=b.customer_id
	WHERE b.customer_id IS NULL



This may seem complex, but join syntax is absolutely required for more complex queries. It's a lot more standard than some of the other options.
Was This Post Helpful? 0
  • +
  • -

#8 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,957
  • Joined: 21-March 08

Re: SQL Query Help

Posted 03 December 2008 - 07:18 AM

View Postbaavgai, on 2 Dec, 2008 - 07:01 PM, said:

Just a quick note, both NOT IN and NOT EXISTS don't scale well. They're nice if the sub quiery is small and will stay that way.

Another way to do what you're looking for is a standard join against a sub query. In general, this will be faster; in some databases, much faster.

SELECT a.Customer_ID, a.Name, a.Address
	FROM Customers a
		LEFT OUTER JOIN (SELECT distinct customer_id FROM Accounts WHERE Accounts) b
			ON a.customer_id=b.customer_id
	WHERE b.customer_id IS NULL



This may seem complex, but join syntax is absolutely required for more complex queries. It's a lot more standard than some of the other options.


so you would suggest using DISTINCT over using NOT EXISTS?
Was This Post Helpful? 0
  • +
  • -

#9 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5774
  • View blog
  • Posts: 12,586
  • Joined: 16-October 07

Re: SQL Query Help

Posted 03 December 2008 - 08:51 AM

View Posteclipsed4utoo, on 3 Dec, 2008 - 08:18 AM, said:

so you would suggest using DISTINCT over using NOT EXISTS?


I'd advise anything over NOT IN. ;)

NOT EXISTS, depending on the implementation, can be faster than the outer join method I offered. If there were no distinct, they're probably about even. Depending on how the database parses sub queries, it might ultimately be faster.

Conceptually, I find NOT EXISTS clumsy. Any time your sub queries have to know what the parent queries are doing you've left the happy land of result sets and gone, elsewhere. I also find them hideous to debug. Once you have established a result set you can build it pretty easily, self referencing can get ugly.

So I guess it's just personal preference. ( I just did some tests in MSSQL, NOT EXISTS preformed much better than last time it'd had issues with it. )
Was This Post Helpful? 0
  • +
  • -

#10 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,957
  • Joined: 21-March 08

Re: SQL Query Help

Posted 03 December 2008 - 09:23 AM

View Postbaavgai, on 3 Dec, 2008 - 10:51 AM, said:

View Posteclipsed4utoo, on 3 Dec, 2008 - 08:18 AM, said:

so you would suggest using DISTINCT over using NOT EXISTS?


I'd advise anything over NOT IN. ;)

NOT EXISTS, depending on the implementation, can be faster than the outer join method I offered. If there were no distinct, they're probably about even. Depending on how the database parses sub queries, it might ultimately be faster.

Conceptually, I find NOT EXISTS clumsy. Any time your sub queries have to know what the parent queries are doing you've left the happy land of result sets and gone, elsewhere. I also find them hideous to debug. Once you have established a result set you can build it pretty easily, self referencing can get ugly.

So I guess it's just personal preference. ( I just did some tests in MSSQL, NOT EXISTS preformed much better than last time it'd had issues with it. )


Thanks. In my current job, I am the "hybrid" Developer/DBA, which I believe a lot of developers are being turned into. I had some experience with databases before this job, but not enough to be the person to completely create an efficient database. So any information I can get is helpful.

This post has been edited by eclipsed4utoo: 03 December 2008 - 09:24 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1