Confused About Itzik Ben-Gan's Logical Query Processing Order in His SQL Server 2005 Book and SQL Server 2008 Book

Confused about Itzik Ben-Gan's Logical Query Processing order in his SQL Server 2005 book and SQL Server 2008 book

The logical processing order is also documented in this Books Online entry. Be careful to distinguish logical processing order from physical processing order. As the BOL entry notes:

The following steps show the logical processing order, or binding
order, for a SELECT statement. This order determines when the objects
defined in one step are made available to the clauses in subsequent
steps. For example, if the query processor can bind to (access) the
tables or views defined in the FROM clause, these objects and their
columns are made available to all subsequent steps. Conversely,
because the SELECT clause is step 8, any column aliases or derived
columns defined in that clause cannot be referenced by preceding
clauses. However, they can be referenced by subsequent clauses such as
the ORDER BY clause. Note that the actual physical execution of the
statement is determined by the query processor and the order may vary
from this list.

The query optimizer is free to translate the logical requirement specified by the query into any physical execution plan that produces the correct results. Generally, there are many physical alternatives for a given logical query, so it is quite usual for a physical plan to differ fundamentally from the logical processing order (for binding purposes) described above.

SQL Fieldname vs Aliasname

I don't understand your question. This is a reasonably formed SQL query:

SELECT c_Supplier.Supplier_ID AS Entidad_ID, c_Supplier.Name,
       c_Supplier.RFC, c_Supplier_Direccion.Description,
       c_Supplier_Direccion.Address, c_Supplier_Phone.Phone
FROM c_Supplier LEFT JOIN
     (c_Supplier_Direccion LEFT JOIN
      c_Supplier_Phone
      ON c_Supplier_Direccion.Supplier_Direccion_ID = c_Supplier_Phone.Supplier_Direccion_ID
     ) ON c_Supplier.Supplier_ID = c_Supplier_Direccion.Supplier_ID
WHERE (c_Supplier.Supplier_ID = 1);

(I would recommend table aliases for readability, but that is a separate issue.)

It has no alias called A_ID anywhere in the query, so there is no reason to ever expect a reference to A_ID to work (unless it is a column in one of the tables).

And, SQL doesn't allow the re-use of table aliases in the SELECT where they are defined or the WHERE clause. This is not an MS Access limitation; it is how the SQL language is defined.

If you want to do so in MS Access, you can use a subquery and reference the table alias in the outer query.

Performance of OR?

You can't count on short circuit evaluation in TSQL.

The optimiser is free to evaluate the conditions in which ever order it sees fit and may in some circumstances evaluate both parts of an expression even when the second evaluation cannot change the result of the expression (Example).

That is not to say it never does short circuit evaluation however. You may well get a start up predicate on the expensive condition so it is only executed when required.

Additionally the presence of the OR in your query can convert a sargable search condition into an unsargable one meaning that indexes are not used optimally. Especially in SQL Server 2005 (In 2008 OPTION (RECOMPILE) can help here).

For example compare the plans for the following. The version with OR ends up doing a full index scan rather than an index seek to the specific values.

DECLARE @number INT;
SET number  = 0;

SELECT COUNT(*)
FROM master..spt_values
WHERE @number IS NULL OR number = 0

SELECT COUNT(*)
FROM master..spt_values
WHERE  number = 0

Left Outer Join Does not preserve Left Table

For the non matching rows po.UserName will be NULL so LEN(LTRIM(RTRIM(po.UserName))) is NULL

NULL > 0 evaluates to UNKNOWN not TRUE so when the predicate is in the WHERE you are turning your outer join back into an inner one. Similarly for FUNCS as SQLMenace points out.

You might want to Download Itzik Ben Gan's Logical Query Processing poster.

Conceptually the following happens (this should not be confused with how it is physically implemented however!)

For your first query:

• Cartesian Product on #tmp_rep, po_Questions
• Then the ON Filter is applied which effectively does an INNER JOIN on Q_ID = Certificate but also excludes any po_Questions rows that don't match your predicate.
• Then the non matching Outer Rows from #tmp_rep are added back in. These will have NULL for all columns from po_Questions
• There is no WHERE clause so this is the final result.

For your second query:

• Cartesian Product on #tmp_rep, po_Questions
• Then the ON Filter is applied which effectively does an INNER JOIN on Q_ID = Certificate.
• Then the non matching Outer Rows from #tmp_rep are added back in. These will have NULL for all columns from po_Questions
• Then the WHERE clause is evaluated. This will definitely remove all rows from the previous step and possibly additional rows too.

RANK OVER PARTITION BY on aggregate functions

In databases that support window functions, this would normally be written as:

SELECT cm.*
FROM (SELECT c.country, MONTH(o.order_date) as Month, SUM(od.price * od.units) AS revenue,
             RANK() OVER (PARTITION BY country, MONTH(o.order_date) ORDER BY SUM(od.price * od.units) ) AS Rank 
      FROM orders o LEFT JOIN
           customers c
           ON o.customer_id = c.customer_id  JOIN
           order_detail od 
           ON o.order_id = od.order_id
      GROUP BY country, Month
     ) cm
HAVING Rank <= 3;

Merging records

I think you were looking for a UNION ALL

DECLARE @TableA TABLE(
        ID INT,
        Category VARCHAR(50)
)

INSERT INTO @TableA (ID,Category) SELECT 1, 'Books'
INSERT INTO @TableA (ID,Category) SELECT 2, 'Fruits'
INSERT INTO @TableA (ID,Category) SELECT 3, 'Vegetables'

DECLARE @TableB TABLE(
        ID INT,
        CategoryID INT,
        Item VARCHAR(50)
)

INSERT INTO @TableB (ID,CategoryID,Item) SELECT 1,1,'Rytham of Music'
INSERT INTO @TableB (ID,CategoryID,Item) SELECT 2,1,'My Biography'
INSERT INTO @TableB (ID,CategoryID,Item) SELECT 3,1,'Jungal Book'
INSERT INTO @TableB (ID,CategoryID,Item) SELECT 4,2,'Apple'
INSERT INTO @TableB (ID,CategoryID,Item) SELECT 5,2,'Orenge'
INSERT INTO @TableB (ID,CategoryID,Item) SELECT 6,2,'Pinnaple'
INSERT INTO @TableB (ID,CategoryID,Item) SELECT 7,3,'Spinach'

SELECT  ID,
        MergedCategory
FROM    (
            SELECT  ID,
                    Category + ' -- From TableA' MergedCategory,
                    CAST(ID AS VARCHAR(10)) + '\' AS CategoryID
            FROM    @TableA
            UNION ALL
            SELECT  ID,
                    Item,
                    CAST(CategoryID AS VARCHAR(10)) + '\' + CAST(ID AS VARCHAR(10)) + '\'
            FROM    @TableB
        ) sub
ORDER BY CategoryID

SQL invalid column name

Your alias would be available if you wrapped this whole thing in an outer query, but since you're on the same level as the alias, you need to derive it again with the same logic:

Select  

  h.Type,
  a.name,
  CASE 
    WHEN a.name LIKE '%-%' THEN LEFT(a.name, Charindex('-', a.name) - 1) 
    ELSE a.name
  END as FirstPart,
  CASE
    WHEN a.name LIKE '%-%' THEN RIGHT(a.name, Charindex('-', Reverse(a.v)) - 1)
  END as LastPart
from Table1 a
  left join Table2 h 
    on CASE WHEN a.name LIKE '%-%' THEN LEFT(a.name, Charindex('-', a.name) - 1) ELSE a.name END = h.ID

If you wrapped this, you could reference by subquery:

select * 
from 
 (Select h.Type, a.name,
  CASE WHEN a.name LIKE '%-%' THEN LEFT(a.name, Charindex('-', a.name) - 1) ELSE a.name END as FirstPart,
  CASE WHEN a.name LIKE '%-%' THEN RIGHT(a.name, Charindex('-', Reverse(a.v)) - 1) END as LastPart
  from Table1 a ) inn
left join Table2 h 
  on inn.FirstPart = h.ID

As a side note, if you can avoid the join on a like match, you'll get better performance.

---

Related Topics