Re: Conditional Relationships ?

I am going to double post this to both comp.databases.theory and to microsoft.public.sqlserver.programming. That way, Steve cannot bitch so much about the way I try to educate people with all that annoying Standard SQL stuff <g>.

Fridiric Brouard came up with a nice trick for writing a similar join. That is, a join on one table, say a basic table of student data, with either a table of data particular to domestic students or another table of data particular to foreign students, based on the value of a parameter. This differs from a true UNION JOIN in that it has to have a "root" table to use for the outer joins.

CREATE TABLE Students
(student_nbr INTEGER NOT NULL PRIMARY KEY,
 student_type CHAR(1) NOT NULL DEFAULT 'D'

     CHECK (student_type IN ('D', 'F', ..))  ...);

CREATE TABLE DomesticStudents
(student_nbr INTEGER NOT NULL PRIMARY KEY,

     REFERENCES Students(student_nbr),
 ...);

CREATE TABLE ForeignStudents
(student_nbr INTEGER NOT NULL PRIMARY KEY,

     REFERENCES Students(student_nbr),
 ...);

SELECT Students.*, DomesticStudents.*, ForeignStudents.*   FROM Students

       LEFT OUTER JOIN
       DomesticStudents
       ON CASE Student.student_type
          WHEN 'D' THEN 1 ELSE NULL END
          = 1
         LEFT OUTER JOIN
         ForeignStudents
         ON CASE Student.student_type
            WHEN 'F' THEN 1 ELSE NULL END
            = 1;

The UNION JOIN was defined in SQL-92, but I know of no SQL product that has implemented it. As the name implies, it is a cross between a UNION and a FULL OUTER JOIN. The definition followed easily from the other infixed JOIN operators. The syntax has no searched clause

  <table expression 1> UNION JOIN <table expression 2>

The statement takes two dissimilar tables and puts them into one result table. It preserves all the rows from both tables and does not try to consolidate them. Columns which do not exist in one table are simply padded out with NULLs in the result rows. Columns with the same names in the tables have to be renamed differently in the result.  It is equivalent to

  <table expression 1>
   FULL OUTER JOIN
   <table expression 2>
   ON 1 = 2;

Any searched expression which is always FALSE will work. As an example of this, you might want to combine the medical records of male and female patients into one table with this query.

  SELECT *
    FROM (SELECT 'male', prostate FROM Males)

         OUTER UNION
         (SELECT 'female', pregnancy FROM Females);

'male'   no      NULL      NULL
'male'   no      NULL      NULL
'male'   yes     NULL      NULL
'male'   yes     NULL      NULL
NULL     NULL    'female'  no
NULL     NULL    'female'  no
NULL     NULL    'female'  yes
NULL     NULL    'female'  yes