EXISTS ,IN usage without SELECT ,INSERT and UPDATE queries

Question

I often get stuck when i want to write someting like

IF v_shortname IN (SELECT   sname FROM table)

OR

 IF EXISTS (SELECT   sname FROM mmstrephdr
 WHERE  sname= v_short)

I know EXISTS and IN not work in that way .Also i can always tweak my code to get such results, but i always wonder if there is an programming construct in SQL which would do the job for me !

similar question: http://stackoverflow.com/questions/24929/difference-between-exists-and-in-in-sql — Verma, Jul 27 '13 at 14:34
@Verma How is this a similar question . Try reading my question again — HalfWebDev, Jul 27 '13 at 14:46
@kushal Answers posted against the linked question appear to address the differences and usage of EXISTS and IN quite effectively. If you are not looking at general usage then please post specific details on what exactly you are trying to code. — Verma, Jul 27 '13 at 15:00

score 0 · Answer 1 · answered Jul 27 '13 at 14:31

0

If you get rid of the IF prefix, IN() and EXISTS() do work that way.

First, the version using IN() is generally a Bad Idea™ - performance-wise, you are much better off using a JOIN or a derived table rather than a subquery in the WHERE clause.

-- instead of:
SELECT * FROM tbl1 WHERE tbl1.v_short IN (SELECT tbl2.sname FROM tbl2)

-- you would want something like:
SELECT tbl1.* FROM tbl1 JOIN tbl2 ON (tbl2.sname = tbl1.v_short)

EXISTS() on the other hand is very useful. It's optimized to be identical to an INNER JOIN, while NOT EXISTS() is identical to a LEFT JOIN with an IS NULL check. The [NOT] EXISTS() version is just easier to read as though it were English. Note that there is no point in passing a column list to the SELECT within an EXISTS - it is not used, it is optimized away as a SELECT * every time.

-- easy to read:
SELECT * FROM tbl1 WHERE EXISTS (SELECT * FROM tbl2 WHERE tbl2.sname = tbl1.v_short)

-- same thing, just more SQL-like than English-like:
SELECT tbl1.* FROM tbl1 JOIN tbl2 ON (tbl2.sname = tbl1.v_short)

answered Jul 27 '13 at 14:31

In your first line itself you removed `IF` . That is what i don't want to do . i know i could have tweaked and used select easily to get results . But i can't figure why shouldn't this work . I mean a value being compared to a SELECT statement results directly -not in SELECT query -but in an IF condition . OR if i talk of my second query above ,what if i wanted to only know if my select query returned something . Why do i have to write `select...into` statement for checking the results ? i just wanted to know if that's possible anyhow ? – HalfWebDev Jul 27 '13 at 14:43
Your original question and follow-up questions aren't detailed enough to provide a complete answer - still don't know what you are really trying to accomplish. Perhaps you're trying to write these `IF` tests in a stored procedure as conditional logic rather than in a regular `SELECT` statement? In the case of using `IN` and `EXISTS` within a `SELECT` statement, both of those already have an "implicit `IF`" attached to them. Checking for the inclusion or existence of a value in another table already *is* a conditional logic test... you don't need an additional `IF`. – Jul 27 '13 at 15:48
Oh, if you're not trying to select any actual table data, and just want a simple yes/no answer as to whether the data exists, you still have to do a SELECT (you can't retrieve any value from the database without the SELECT). Something like: `SELECT IF(EXISTS(SELECT * FROM tbl1 WHERE ), 'yes', 'no')` – Jul 27 '13 at 15:51

score 0 · Answer 2 · answered Jul 27 '13 at 19:56

Why do you get stuck? PL/SQL provides constructs which allow us to implement that logic.

declare
    .....
    function name_exists (p_sname table_a.sname%type)
       return boolean
    is
        l_shortname table_a.sname%type;
    begin
            SELECT   sname 
            FROM table_a
            where sname = p_sname;
            return true;
        exception
            when no_data_found then
                return false;
    end name_exists;
....
begin
    .... 
    if name_exists ('KNOX') then

There are other ways of doing this, but I like the encapsulation of the nested function.

EXISTS ,IN usage without SELECT ,INSERT and UPDATE queries

2 Answers2