SQL Joins where join-condition column contains a NULL

From: Jeff Zucker <jeff_at_vpservices.com>
Date: Sat, 06 Oct 2001 17:46:39 -0700
Message-ID: <3BBFA5EF.B9C519BC_at_vpservices.com>

I am working on joins for the perl module SQL::Statement and I have some questions regarding NULLs in SQL92 (as a standard, I don't really care about particular implementations).

Given two tables called "lower" and "upper", each composed of a single row:

  lower : number | lowcase

-------+--------

            NULL | x

  upper : number | upcase

-------+--------

           NULL | Y And Given the statement template:

  SELECT lowcase, upcase
    FROM lower <join_type> JOIN upper USING (number)

Is this results table correct (omiting the USING clause with UNION)?

   join_type results

---

   INNER          0 rows
   LEFT           1 row:  x,NULL
   RIGHT          1 row:  NULL,Y
   FULL           2 rows: 1)x,NULL  2)NULL,Y
   UNION          2 rows: 1)x,NULL  2)NULL,Y

And would the results be the same if NATURAL or ON lower.number = upper.number were used instead of the USING clause?

-- 
Jeff

Received on Sun Oct 07 2001 - 02:46:39 CEST