×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

sql statement

sql statement

sql statement

(OP)
hi there;
 
DDL: SELECT nbr, callnumber, author, title, category FROM invmarc where nbr IN (SELECT DISTINCT nbr FROM keyword where keyword LIKE '%BIN%') or nbr IN (SELECT DISTINCT nbr FROM keyword where keyword LIKE '%MONEY%')
 
regarding the sql above, i would like to know, when i search more than 2 fields, the oracle database take about 60 seconds to return the result to me. I am wondering why is it so slow? is it because my sql structure not proper?

RE: sql statement

Why not:

SELECT nbr, callnumber, author, title, category
FROM invmarc
WHERE nbr IN (
   SELECT DISTINCT nbr
     FROM keyword
    WHERE keyword LIKE '%BIN%' or keyword LIKE '%MONEY%')

RE: sql statement

My understanding is that EXISTS is faster....

SELECT    a.nbr
    , a.callnumber
    , a.author
    , a.title
    , a.category
FROM    invmarc a
WHERE EXISTS
    ( SELECT 1
      FROM keyword b
      WHERE  b.nbr = a.nbr
      AND (  b.keyword LIKE ‘%BIN%’
        OR b.keyword LIKE ‘%MONEY%’ )
    )

RE: sql statement

(OP)
i am glad to receive different types of answer and i am now trying each method to determine which way is better / faster. thanks a lot!

RE: sql statement

What was faster / better ?

RE: sql statement

(OP)
hi there;

this statement is faster:

SELECT nbr, callnumber, author, title, category
FROM invmarc
WHERE nbr IN (
   SELECT DISTINCT nbr
     FROM keyword
    WHERE keyword LIKE '%BIN%' or keyword LIKE '%MONEY%')

anyway, i don't understand about the select 1 from this statement, it works but not that fast:

SELECT    a.nbr
    , a.callnumber
    , a.author
    , a.title
    , a.category
FROM    invmarc a
WHERE EXISTS
    ( SELECT 1
      FROM keyword b
      WHERE  b.nbr = a.nbr
      AND (  b.keyword LIKE ‘%BIN%’
        OR b.keyword LIKE ‘%MONEY%’ )
    )

can anyone explain to me what is select 1 from the inner select statement??

RE: sql statement

If you use EXISTS(statement), all you need to know is if the statement returns a value or null. If the statement returns a value, EXISTS(statement) returns true, if the stament returns null, EXISTS(statement) returns false. So it doesn't matter which value the statement selects, it can be 1 or any other value.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close