Left Join and Right Join - Are they the same?
Moderator: NorbertKrupa
Left Join and Right Join - Are they the same?
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?
Have a GREAT day!
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Left Join and Right Join - Are they the same?
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:
This LEFT join:
Produces the same result set as this RIGHT join:
But not this RIGHT join:
And this LEFT join:
Produces the same result set as this RIGHT join:
But not this RIGHT join:
Hopefully this simple example was helpful and didn't make you anymore confused!
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)
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)
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)
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)
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)
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)
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)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.