INF: Restrictions for Multi-Table Outer Joins

Last reviewed: April 25, 1997
Article ID: Q45301

The information in this article applies to:

  - Microsoft SQL Server version 4.2 for OS/2

SUMMARY

The following information discusses how many tables can be involved in an outer join, and the restrictions involved.

MORE INFORMATION

Outer joins cannot be nested (t1*=t2*=t3 is illegal), but multiple outer joins are permitted if they aren't nested (t1=*t2*=t3 is allowed).

Inner joins cannot be nested inside the outer join (t1*=t2=t3 is illegal, but t1=t2*=t3 is allowed).

t1*=t2=*t3 is also permitted. It takes the Cartesian product of t1 and t3, then outer-joins the result to t2.

As long as the semantic rules are not violated, the limit is 16 tables, just as with an inner join.

The semantics regarding outer joins can be represented graphically with the tables as nodes and the joins as arcs between the nodes. Outer joins are directed arcs with the arrow pointing to the "inner" table of the pair.

The following is a graphic representation:

select * from t1,t2 where t1.c1*=t2.c1   is   t1 --> t2
select * from t1,t2 where t1.c1=*t2.c1   is   t1 <-- t2
select * from t1,t2 where t1.c1=t2.c1    is   t1 --- t2

illegal                               legal

t1-->t2-->t3     t1-->t2<--t3         t1-->t2<--t3        t1-->t2<--t3
t1<--t2<--t3      |        ^          t1<--t2-->t3         |        |
t1-->t2---t3      |        |          t1---t2-->t3         +--------+
                  +--------+

     t3            t3                      t3                 t3
      ^             |                       ^                  |
      |             v                       |                  v
t2<--t1<--t4  t2-->t1-->t4            t2<--t1-->t4       t2-->t1<--t4
      |             ^                       |                  ^
      v             |                       v                  |
     t5            t5                      t5                 t5

Arcs may enter a node or arcs may leave a node, but arcs cannot leave a node that has any arcs entering it.


Additional query words: Transact-SQL
Keywords : kbprg SSrvServer
Version : 4.2
Platform : OS/2


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 25, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.