Exists and inner Join Function in SQL

[Ajax]

Hey, i am new to qlik and i am trying to convert this qlik query to SQL equivalent.

Table1: 

NoConcatenate

LOAD,

col1,

col2,

col3,

foo1,

foo2

From "path_of_qvd"

Where Exists(col2)   //this col2 has been loaded previously in another table//

inner join (Table1)

col1,

col2,

col3

resident Table1

where foo1=1 and Match(foo2, '020', '011')

Now this is the SQL query that i think is equivalent to this

SQL:

SELECT  col1, col2, col3, foo1, foo2 

FROM 

"Path of Table"

NATURAL JOIN "Previously loaded with column Col1 so that it will  do inner join automatically on Col1"

Where  foo1=1 and foo2 LIKE '020' OR foo LIKE  '011'

What i don't understand is that why in qlik it needs to be inner join after the exists function. Can a person just not use the 

where clause normally? I am little bit confused here. Can someone elaborate on that? Thanks!!

[rubenmarin1 · Accepted solution]

Hi, I don't know if it "needs to be inner join", the inner join will only keep rows where all field values on the second table matches the values of the first table.

So if in the first table there is a row with col1=1, col2=2, col3=3 and foo1=2; when doing the inner join, if in the second table there is a row with col1=1, col2=2, col3=3; the row with foo1=2 will be kept.

If this works is more of a coincidence that the combination for col1, col2 and col3 are only related to the filtered values for foo1 or foo2.

If the 2nd table was

inner join (Table1)
foo1,
foo2
resident Table1
where foo1=1 and Match(foo2, '020', '011')

Then you can be sure that only those values for foo1 and foo2 will be kept.

The 'where' can be applied to the first table, but I think it does an exists() just to do an optimized load of the qvd, wich is a lot faster than adding the 'where foo1=1 and Match(foo2, '020', '011')' condition when reading from the qvd. And after the data has been reducen to the col2 needed values, it applies the other conditions in the next step.

[Ajax]

@rubenmarin1 Indeed. I tested this in Qlik. When i add the "Where" filter in the first table, it takes forever to Load the data. But when i use inner join it gets loaded much faster. So basically its the Filtering. Using Where in my SQL query will be sufficient becuase i don't need to do inner join again. 

What about exists() though. Suppose if i have loaded Table0 with a single column col2 then the exists function will filter the table1 automatically according to Table0 'col2' column. Will it do the same to Table3 if i use exists(col2). Then it is the col2 of Table0 or the Table1.

[rubenmarin1 · Accepted solution]

The way the data loads in Qlik is that one field is the same field for all the tables, so when Table3 uses Exists() it uses the values loaded in Table 0 and Table1.

So there is no field col2 for Table0 and another col2 for Table1, there is only col2 field.

https://community.qlik.com/t5/Qlik-Design-Blog/Symbol-Tables-and-Bit-Stuffed-Pointers/ba-p/1475369