WHERE and JOIN order of execution in PROC SQL

[hcstritz]

Hi all,

I have a question about a statement in the SAS Certified Professional Prep Guide - Advanced Programming Using SAS 9.4.

I thought that WHERE statements were evaluated after tables are joined in a PROC SQL query. On page 68: "In all types of joins, PROC SQL generates a Cartesian product first, and then eliminates rows that do not meet any subsetting criteria that you have specified."

However, when discussing outer joins on page 79, the text reads, "Note: To further subset the rows in the query output, you can follow the ON clause with a WHERE clause. The WHERE clause subsets the individual detail rows before the outer join is performed. The ON clause then specifies how the remaining rows are to be selected for output." 

I might be missing something, but these statements seem contradictory. Are there situations where a WHERE clause (not dataset option) applies before the join is performed? 

[ahmedalattar]

Hi @hcstritz 
Have a look at these two papers

101-30: The SQL Optimizer Project: _Method and _Tree in SAS®9.1
200-2013: Exploring the PROC SQL _METHOD Option

Here is an additional link to look at in order to understand the typical order of SQL statements execution 

SQL Order of Execution: Understanding How Queries Run | DataCamp

Note: SAS Proc SQL does not support limit nor offset statements!

They should provide a nice explanation for what you are asking about

Hope this helps

[sbxkoenk]

---

@hcstritz wrote:

...
On page 68: "In all types of joins, PROC SQL generates a Cartesian product first, and then eliminates rows that do not meet any subsetting criteria that you have specified."

...

This is what I think.
Conceptually ( ! ) that statement is correct.
But physically (physical execution) ... the "query optimizer" evaluates your code to avoid building massive, inefficient Cartesian products in memory or on disk. So, in most cases the Cartesian product will never materialize and the query optimizer will use a different approach.
In general the where-clause acts as an input filter and not an output filter.

If you do not see the Cartesian Product Log Message (see below), then you are good and the full Cartesian product of the participating tables was not built.

NOTE: The execution of this query involves performing one or more Cartesian 
      product joins that can not be optimized.

Selecting Data from More Than One Table By Using Joins : SAS Help Center

SAS Tutorial | Mastering the WHERE Clause in PROC SQL - YouTube

Good luck,

Koen