Left Join and Right Join - Are they the same?

[sarah]

Is there a difference between a right outer join and a left outer join in Vertica? I friend told me that Vertica only supports a left outer join because that's what what most developers use. Is that true?

[JimKnicely]

They aren't the same thing, but they can produce the same result sets depending on the ordering of table in the FROM clause.

Say we have the following two tables:

Code: Select all

dbadmin=> SELECT * FROM tab1 ORDER BY col1;
 col1 |  col2
------+---------
    1 | Jim
    2 | Jerry
    3 | Brian
    4 | Patrick
(4 rows)

Code: Select all

dbadmin=> SELECT * FROM tab2 ORDER BY col1;
 col1 | col2
------+-------
    1 | Jim
    2 | Jerry
    3 | Will
    5 | Steve
(4 rows)

This LEFT join:

Code: Select all

dbadmin=> SELECT tab1.col1 tab1_col1, tab1.col2 tab1_col2, tab2.col1 tab2_col1, tab2.col2 tab2_col2 FROM tab1 LEFT JOIN tab2 ON tab1.col1 = tab2.col1 ORDER BY 1;
 tab1_col1 | tab1_col2 | tab2_col1 | tab2_col2
-----------+-----------+-----------+-----------
         1 | Jim       |         1 | Jim
         2 | Jerry     |         2 | Jerry
         3 | Brian     |         3 | Will
         4 | Patrick   |           |
(4 rows)

Produces the same result set as this RIGHT join:

Code: Select all

dbadmin=> SELECT tab1.col1 tab1_col1, tab1.col2 tab1_col2, tab2.col1 tab2_col1, tab2.col2 tab2_col2 FROM tab2 RIGHT JOIN tab1 ON tab1.col1 = tab2.col1 ORDER BY 1;
 tab1_col1 | tab1_col2 | tab2_col1 | tab2_col2
-----------+-----------+-----------+-----------
         1 | Jim       |         1 | Jim
         2 | Jerry     |         2 | Jerry
         3 | Brian     |         3 | Will
         4 | Patrick   |           |
(4 rows)

But not this RIGHT join:

Code: Select all

dbadmin=> SELECT tab1.col1 tab1_col1, tab1.col2 tab1_col2, tab2.col1 tab2_col1, tab2.col2 tab2_col2 FROM tab1 RIGHT JOIN tab2 ON tab1.col1 = tab2.col1 ORDER BY 1;
 tab1_col1 | tab1_col2 | tab2_col1 | tab2_col2
-----------+-----------+-----------+-----------
           |           |         5 | Steve
         1 | Jim       |         1 | Jim
         2 | Jerry     |         2 | Jerry
         3 | Brian     |         3 | Will
(4 rows)

And this LEFT join:

Code: Select all

dbadmin=> SELECT tab1.col1 tab1_col1, tab1.col2 tab1_col2, tab2.col1 tab2_col1, tab2.col2 tab2_col2 FROM tab2 LEFT JOIN tab1 ON tab1.col1 = tab2.col1 ORDER BY 1;
 tab1_col1 | tab1_col2 | tab2_col1 | tab2_col2
-----------+-----------+-----------+-----------
           |           |         5 | Steve
         1 | Jim       |         1 | Jim
         2 | Jerry     |         2 | Jerry
         3 | Brian     |         3 | Will
(4 rows)

Produces the same result set as this RIGHT join:

Code: Select all

dbadmin=> SELECT tab1.col1 tab1_col1, tab1.col2 tab1_col2, tab2.col1 tab2_col1, tab2.col2 tab2_col2 FROM tab1 RIGHT JOIN tab2 ON tab1.col1 = tab2.col1 ORDER BY 1;
 tab1_col1 | tab1_col2 | tab2_col1 | tab2_col2
-----------+-----------+-----------+-----------
           |           |         5 | Steve
         1 | Jim       |         1 | Jim
         2 | Jerry     |         2 | Jerry
         3 | Brian     |         3 | Will
(4 rows)

But not this RIGHT join:

Code: Select all

dbadmin=> SELECT tab1.col1 tab1_col1, tab1.col2 tab1_col2, tab2.col1 tab2_col1, tab2.col2 tab2_col2 FROM tab2 RIGHT JOIN tab1 ON tab1.col1 = tab2.col1 ORDER BY 1;
 tab1_col1 | tab1_col2 | tab2_col1 | tab2_col2
-----------+-----------+-----------+-----------
         1 | Jim       |         1 | Jim
         2 | Jerry     |         2 | Jerry
         3 | Brian     |         3 | Will
         4 | Patrick   |           |
(4 rows)

Hopefully this simple example was helpful and didn't make you anymore confused!