How do these queries execute?

Question

1

select c.c_name, a.n_name
from    retail.client c left join retail.area a
on  c.c_nationkey = a.n_nationkey
and a.n_name is null;

2.

select c.c_name, a.n_name
from    retail.client c left join retail.area a
on  c.c_nationkey = a.n_nationkey
where   a.n_name is null;

The following queries returns same number of rows.

when I do

    select c.c_name, a.n_name
    from    retail.client c left join retail.area a
    on  c.c_nationkey = a.n_nationkey
    and a.n_name is null
MiNUS
    select c.c_name, a.n_name
    from    retail.client c left join retail.area a
    on  c.c_nationkey = a.n_nationkey
    where   a.n_name is null

it returns 0 rows. so I think there must be some difference in the way they get executed.can any one explain. And I would be thankful If some one refers me execution order of t-sql statement. I am a beginner in Teradata sql.

Is there any article on the internet --explaining order of execution of sql statements in different types of databases. Thank you very much.

I guess you have a typo error, because both are exactly same queries. — Lalit Kumar B, Feb 22 '16 at 11:57
@jarlh The OP was asking about `explaining order of execution of sql statements in different types of databases` so the tags you have edited out might be important to the question. — MT0, Feb 22 '16 at 11:59
@GordonLinoff The first query has only a `JOIN`, the second has a `JOIN` and a `WHERE`. — xpy, Feb 22 '16 at 11:59
EXPLAIN on the query you are trying to execute will give you a peek into 'how' the query is going to be executed. EXPLAIN select c.c_name ..... ; — k0n3ru, Feb 22 '16 at 12:22

score 1 · Answer 1 · answered Feb 22 '16 at 12:52

Those queries will not return the same result.

Q1 returns all rows from client with NULL in n_name. When you EXPLAIN it you will see that the optimizer eliminated the join:

  1) First, we lock retail.c for read on a reserved RowHash to prevent
     global deadlock.
  2) Next, we lock retail.c for read.
  3) We do an all-AMPs RETRIEVE step from retail.c by way of an
     all-rows scan with no residual conditions into Spool 1
     (group_amps), which is built locally on the AMPs.  The size of
     Spool 1 is estimated with low confidence to be 1,536 rows (
     136,704 bytes).  The estimated time for this step is 0.08 seconds.
  4) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1.  The total estimated time is 0.08 seconds.

In fact this is exactly the same as

select c.c_name, NULL S n_name
from    retail.client c

But Q2 returns only where c_nationkey doesn't exists in n_nationkey, i.e. this is similar to a NOT EXISTS. On the actaul data in this sample database there will be no row returned.

Regarding details on the logical execution of a SQL SELECT have a look at Itzik Ben-Gan's new articles on SQL Server:

Logical Query Processing: What It Is And What It Means to You

Logical Query Processing: The FROM Clause and Joins

Teradata is almost the same, just doesn't support CROSS/OUTER APPLY, but adds QUALIFY for filtering the result of an OLAP-Function:

FROM - WHERE - GROUP BY - HAVING - Windowed Aggregates/OLAP - QUALIFY - SELECT - ORDER BY

The MINUS query does not return zero rows, it returns the same as Q1, just as a DISTINCT result.

Please explain this on (got doubt on this one) 'SELECT * FROM S1DTPLCS.T5DS_ACCOUNT_PARTY AS ACCTPARTY INNER JOIN S1DTPLCS.T5DS_PART_INFO as PARTINFO ON ACCTPARTY.CUST_NO_RM = PARTINFO.CUST_NO_RM WHERE ACCTPARTY.PRI_SEC_CD = 'PRI' MINUS SELECT * FROM S1DTPLCS.T5DS_ACCOUNT_PARTY AS ACCTPARTY INNER JOIN S1DTPLCS.T5DS_PART_INFO as PARTINFO ON ACCTPARTY.CUST_NO_RM = PARTINFO.CUST_NO_RM AND ACCTPARTY.PRI_SEC_CD = 'PRI';' — Polukanti Gouthamkrishna, Feb 23 '16 at 04:32
@PolukantiGouthamkrishna: Those queries do exactly the same, for Inner Joins the placement of search-conditions (ON vs. WHERE) doesn't matter. So you return a result set and subtract exactly the same result set -> empty result — dnoeth, Feb 23 '16 at 07:18

How do these queries execute?

1 Answers1