sql statement 1

[yytan]

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?

 

[spenglerr]

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%')

 

[TheDunce]

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%’ )
)

 

[yytan]

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!

 

[aestrada]

What was faster / better ?

 

[yytan]

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??

 

[dwentink]

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.