Left Join - SQL Select

[#1]

Hi Everybody,

My question is in regards to how joins work, what gets loaded, and what gets kept.

This discussion is used quite a bit when people ask questions about joins and contatenate (Understanding Join, Keep and Concatenate). My question has to do with the wording of this sentence:

"The keyword LEFT will retain rows from the left table but only matching rows from the right table"

Does this mean when loading using SQL Select that all records from Table2 get loaded and then the records that don't have a match in Table1 get deleted? So if I had Table1 with 10 rows and I did Left Joined Table2 which has 1,000,000 rows, QlikView will first load ALL million records and then remove the extra records?

If that is correct, is there a way around this when using SQL Select? I know that when loading a table file, the MATCH function (I think) can be used, but that is not an option with SQL Select.

Thanks!

[avinashelite]

Hi Justin,

Your correct, Based on the data available in the Table1 the table2 data will be loaded . To make it clear in SQL all the records will get pulled while loading the data in to Qlikview App the data is check for the records that are present in Table1 and then the data in Table2 is loaded ....so finally that will not be present in table2

[jonathandienst]

I think its fairly clear that all the rows from Table2 need to be fetched from the data source so that the join logic can be performed. It is sometimes possible and often a good idea to perform a data reducing join in the SQL SELECT, so that only the reduced join results are fetched from the database.

[#4]

Thanks, Jonathan.

What do you mean by performing a data reducing join?

[jonathandienst]

Left Join, Right Join and Inner Join all have the potential of reducing the number of rows in one or both the joined tables. They may or may not actually reduce the number of rows.

A full outer join does not.

[Peter_Cammaert]

If you want fewer rows to be transmitted from RDBMS to QlikView (indeed a speed limiter), then you'ld better perform the JOIN in the SQL statements. Those are executed by the RDBMS engine (often a high-performance engine) before the result set is transferred to QlikView.

Of course, you can't do that when JOINing a DB table to a QlikView table.

Peter