How do these queries execute?

Database
Teradata Employee

How do these queries execute?

There are three SQL statements (a, b & c) below, each of which performs a left join. Can anyone please explain how the execution of these statements differs from the other?

a) 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;

b) 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;

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

Thanks!
Tags (4)
1 REPLY
N/A

Re: How do these queries execute?

There's a simple rule:
*First* there's the join based on every condition in ON, *then* the WHERE-conditions are applied.

a: "a.n_name is null" is part of the JOIN-condition, so it's returning all rows for client, but the name column is NULLed for all rows where a.n_name is not null.
This is exactly the same as:
select c.c_name, NULL as n_name
from retail.client c

b: "a.n_name is null" is part of the WHERE-condition, so it's returning rows with NULL in a.n_name and rows which can't be joined, because a.n_nationkey doesn't exist in area.
If a.n_name is defined as NOT NULL it's the same as a NOT IN/NOT EXISTS

c: all rows from client, if they can't be joined (because a.n_nationkey doesn't exist in area) there's a NULL for a.n_name.

Have a look at "Chapter 2: Join Expressions" in the "SQL Data Manipulation Language" manual, especially the parts about Outer Join up to the excellent "Outer Join Case Study".

Dieter