%SQL.StatementResult vs %Library.ResultSet

The noted difference between %SQL.StatementResult.%Get() and %Library.ResultSet.%Get() is regarding the actual column names, not the values they contain.

Given that I am using InterSystems IRIS, but here is how I reproduced your example (I don't use a left-join, but they should behave the same)

CREATE TABLE testTable("Column A" INT, "Column B" INT, "Column C" INT, "Column D" INT)
INSERT INTO testTable VALUES(1,2,3,4)
INSERT INTO testTable VALUES(1 NULL, NULL,4)

-------------------------

USER>set stmt = ##class(%SQL.Statement).%New()
USER>set query = 1, query(1) = "SELECT * FROM testTable"
USER>write stmt.%Prepare(.query)
1
USER>set rs = stmt.%Execute()
USER>write rs.%Next()
1
USER>write rs.%Get("Column B")
2
USER>write rs.%Next()
1
USER>zwrite rs.%Get("Column B")
""

USER>do $system.Status.DisplayError(rs.%Get("Column E"))
 
DO $SYSTEM.Status.DisplayError(rs.%Get("Column E"))
^
<PROPERTY DOES NOT EXIST> *Column E,%sqlcq.USER.cls15

As you can see, %SQL.StatementResult.%Get() should only throw an error if you pass in an invalid column name such as "Column E" in the code snippet above.

I recommend that you check the %SQL.StatementMetadata to check if the columns exist in the first place.

USER>set rsmd = rs.%GetMetadata()
USER>set columns = $LB()
USER>for i=0:1:rsmd.columnCount-1 set $li(columns,i+1)=rsmd.columns.GetNext(i).colName

Once you have columns populated, you can use $LISTFIND to determine whether or not the column exists.

If you cannot do the above then you can wrap the use of %SQL.StatementResult.%Get() with a try-catch block as seen below:

while(rs.%Next()) {
     try {
         set var = rs.%Get(columnName)
     } catch {
         set var = ""
     }
}

I do not recommend the try-catch fallback assignment

To answer the original question: What is the difference?

There are 3 ways to use SQL in Caché/IRIS:

Dynamic SQL: The main use case for this approach is that you, the developer, don't know the complete SQL statement at design/compile time. Instead, inside your Method, you build the complete SQL statement at run time (adding columns to the SELECT, conditions to the WHERE clause, whatever you need), prepare the finished SQL, and execute it. This approach uses %SQL.Statement and %SQL.StatementResult. As you loop through the result set, to access the data in each column, you can use rs.columnname or rs.%Get("columnname"). But neither one will work if you don't actually know the names of all the columns, which can obviously happen when the SQL is dynamic. So the only other approach is to use rs.%GetData(columnnumber).

The other two alternatives are for the case where you, the developer, know the complete SQL statement as design/compile time. Note that you can use Dynamic SQL for this case also.

Embedded SQL: In this approach, you embed the complete SELECT statement inside your Method using &sql(). This is similar to embedding SQL in procedural languages provided by other vendors, such as PL/SQL (Oracle) and T-SQL (MS).

Class queries: In this approach, you create a Query in your class that contains the complete SELECT statement, and you use that query in a Method. For this, it's recommended to use the %PrepareClassQuery() method of %SQL.Statement.

Note: the original implementation of Class queries and Dynamic SQL also used %Library.ResultSet.