What is the difference between IN clause in SQL and exists in QlikView?

[#1]

Hi

What is the difference between IN clause in SQL and exists in QlikView?

Thanks,

Attitude

[#2]

The IN clause in SQL is more like wildmatch in qlikview than exists.

For example:

SQL:

Where Month IN ('Jan','Feb','Mar')

QV:

Where wildmatch(Month, 'Jan','Feb','Mar')

Exists is actually used more between tables in QlikView.

From the help section of QlikView about exists:

exists(Month, 'Jan')
returns -1 (true) if the field value 'Jan' is found in the current content of the field Month.

exists(IDnr, IDnr)
returns -1 (true) if the value of the field IDnr in the current record already exists in any previously read record containing that field.

exists (IDnr)
is identical to the previous example.

I most commonly use this for using calculations for Joins.

For instance you want every ID that is in table2 that is not in table1

Table1:

LOAD * INLINE [

ID

1

2

];

Table2:

NoConcatenate

LOAD * INLINE [

ID

2

3

4

] WHERE NOT EXISTS(ID);

drop table Table1;

Normally in SQL you would do something to the extent of Table1.ID <> Table2.ID

But in Qlikview you would have to include a where not exists clause as shown in the above example.

This would give you ID's 3 and 4 only from Table 2.

Hope this helps!

[#3]

Hi Bapperson

Thanks for responding to me. I am able to understand the what is IN and what is EXISTS. But still I am not able to to understand the difference between. Also I would like to know when we should be using IN and when we should be using EXISTS. Please explain!

Thanks,

Attitude

[perumal_41]

Hi At titude,

if U are using IN Function in Sql ,reloading slow ,but using Exists function in sql or Qlikview Reloading Fast

Regards

Perumal A

[#5]

Hi Perumal

Thanks for responding me! Do you mean say that other than speed there is no other difference between them?

Thanks,

Attitude

[stevedark]

Hi there,

Both functions work in basically the same way, limiting the rows returned from a query.  With a SQL 'IN' statement it is either a defined list (comma separated in single quotes) or a sub query (another SQL statement in brackets). With a QlikView EXISTS statement it is comparing existing QlikView data with the data being loaded.

The biggest difference is that the SQL IN can only work on source data wheres the QlikView EXISTS can only work on QVD or Resident loads.

So, from a performance perspective if you are loading from a QVD then EXISTS is the best route (and I often load from an INLINE table to then limit with an EXISTS) - a single EXISTS statement on a QVD load still allows it to load in an optimised fashion.  However, to use a QlikView EXISTS on a SQL table you would need to first load all rows into a temporary table and then do an EXISTS on a RESIDENT load.  This is obviously hugely inefficient as you are pulling rows from SQL that you do not require in your data model.

The best way to deal with this scenario is to use a SQL WHERE statement, perhaps using an IN and a comma separated list.  You could create the comma separate list in a variable by looping through all possible values in a QlikView field (using a loop and a PEEK statement) and building a string that can then be used in the SQL statement.

The golden rule for performance is not to pull more across the pipe from your database into QlikView to then be parsed.  The most common way that I see this happening is from using a SELECT * in SQL which is then limited down by a field list on the QlikView side.

Hope that is helpful.

Steve

http://www.quickintelligence.co.uk/