Message Board


Message Board > Others > Not really a game dev issue but...

July 13, 2007, 14:10
Dennis
どこかにいる
2092 posts

I hope there are some sql gurus here.

I have this query

select blah, bleh, bluh
from tbl t
where
t.first in (select one from second table) and
t.second in (select two from second table) and
t.third in (select threefrom second table);

I want to avoid three subselects, and keep only one subselect. How is this possible? I heard joining the subslect as correlated query with the outer select statement with an "exists" clause... but is that really much more performant? And why would it? in my case the table tbl and second_table are gigantic, thus joining would only slow things down...

I doubt anyone here can help me (not many of you are into sql programming I think, since it is not that gamedev related; except for php people creating a php game online), but if someone can, I'd appreciate it.
____________
Kwakkel
#
July 13, 2007, 18:36
PEader
お前はもう死んでいる
1486 posts

Can you state a bit more clearly what you want to do?

I use SQL a bit but your jargon is confusing me as to what exactly you are trying out.

I mean for
Code:
t.first in (select one from second table) 

Do you mean there is only one of t.first in the second table or that there could potentially be millions?
____________
I see 57,005 people.
#
July 13, 2007, 18:56
Eckolin
Quite Whiskered
388 posts

Why do the subselects have to go?
____________
Maker of Games...
Wisdom is supreme; therefore get wisdom.
Need help with coding? I probably wrote something similar.
#
July 14, 2007, 14:18
Dennis
どこかにいる
2092 posts

the jargon is oracle. I already fiound it, you can use brackets

select col1
from tab
where (tab.col1, tab.col2, ...) in (select tab2.col1, tab2.col2, ... from tab tab2)

the in operator is like the OR operator

select 1
from tab t1
where t1.col1 in (select 1 from tab t2)

other ex:

select 1
from tab t
where t.col1 in (1,2,5,9,1000)

=

select 1
from tab t
where t.col1 = 1 or t.col1 = 2 or t.col1 = 5 or t.col1=9 or t.col1 = 1000.

if there are 5 rows in the subselect it will use an OR.

if you say 'not in' it uses AND.

So there could be millions, but this is quickly written, isntead of a million "or" statements.
____________
Kwakkel
#
October 31, 2008, 01:13
Dennis
どこかにいる
2092 posts

It's more efficient to use exists and do a corelated join into the subselect. You win some performance... sigh... If I chose for HR instead of IT I'd see more women daily... :cry:
____________
Kwakkel
#
October 15, 2010, 12:33
Dennis
どこかにいる
2092 posts

adding 2 years SQL experience:

SQL code:
SELECT blah, bleh, bluh
FROM tbl t JOIN second_table s ON (
  t.first = s.one AND
  t.second = s.two AND
  t.third = s.three
);


Most performant solution

[Edited on October 15, 2010 by Dennis]
____________
Kwakkel
#

Message Board > Others > Not really a game dev issue but...

Quick reply


You must log in or register to post.
Copyright © 2005 Booleansoup.com
Questions? Comments? Bug reports? Contact us!