0

THIS IS NOT DUPLICATE QUESTION. I AM NOT ASKING THE DIFFERENCE BETWEEN A LEFT AND RIGHT JOIN. I AM ASKING WHAT CONSTITUTES A "LEFT" TABLE VERSUS A "RIGHT" TABLE

I understand how to write left and outer joins. I am only looking to verify if my understanding of how to determine what constitutes the "left" and "right" table is correct.

The following is a typical definition of a LEFT/RIGHT OUTER JOIN

A LEFT OUTER JOIN contains all records of the "left" table even if it has no matches in the "right" table specified in the join. A RIGHT OUTER JOIN contains all records in the "right" table even if it has no matches in the "left" table.

What I find lacking from this and all definitions I have read is how do you determine which is the left and right table.

The following code creates 2 table variables and populates it with sample data:

DECLARE @widget TABLE
(
    WidgetId int,
    ColorId int
)

DECLARE @color TABLE
(
    ColorId int,
    ColorName varchar(15)
)


INSERT INTO @widget
SELECT 1,1
UNION
SELECT 2,NULL
UNION
SELECT 1,2

INSERT INTO @color
SELECT 1,'Red'
UNION
SELECT 2, 'Blue'

Suppose one wants to write a query that returns all widgets. If the widget has a color then that should be included in the query results. The following 2 queries achieve this:

SELECT w.*,c.ColorName FROM @widget w LEFT OUTER JOIN  @color c ON w.ColorId = c.ColorId

SELECT w.*,c.ColorName FROM @color c RIGHT OUTER JOIN  @widget w ON w.ColorId = c.ColorId

Please verify if the following statement is correct:

For a Left Outer Join

To determine which is the left and right table, write the query on one line with no line breaks.

Looking at the following portion of the Left Outer Join. Because @widget is to the left of the Join statement that makes it the "Left" table. Likewise, @color is the "Right" table.

@widget w LEFT OUTER JOIN @color

For a Right Outer Join

To determine which is the left and right table, write the query on one line with no line breaks.

Looking at the following portion of the Right Outer Join. Because @widget is to the right of the Join statement that makes it the "Right" table. Likewise, @color is the "Left" table.

@color w RIGHT OUTER JOIN @widget

Dave
  • 649
  • 5
  • 13
  • 1
    Think "main table left join optional data". – jarlh Sep 05 '19 at 13:33
  • 4
    Yes, the "LEFT" table is the one to the left of the keyword `JOIN`. I personally hate when people reverse the tables in the `ON` clause, as I've never seen a reason why they do that, and it's seems to be needless obfuscation. – daShier Sep 05 '19 at 13:45
  • @daShier the order of the comparison fields in an ON is irrelevant. a=b comparison is logically the same as b=a comparison. Why do you consider them reversed? – UnhandledExcepSean Sep 05 '19 at 13:57
  • Not an answer, but think of it less in terms of tables and more in terms of sets of data. A large data set that is pared down by inner joins and the WHERE to get the core data and then left joined to get extra data makes simple sense to me. RIGHT JOINs are far more confusing in my opinion. This is a nice visual that may help to clarify https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ – UnhandledExcepSean Sep 05 '19 at 14:07
  • @UnhandledExcepSean, I understand that `a=b` is the same as `b=a`, which is why I find it unnecessary to reverse "right and left" in the `ON` clause. What is the reason that is done? Is there any advantage to `x JOIN y ON y.a=x.a`? Why not keep the order the same: `x JOIN y ON x.a=y.a`? The last leaves no ambiguity as to "left" vs "right". – daShier Sep 05 '19 at 14:45
  • @UnhandledExcepSean, sorry, I did a poor job in typing out my example: I had not intended to leave out the `LEFT`. In my comment, it should have read: Is there any advantage to `x LEFT JOIN y ON y.a=x.a`? Why not keep the order the same: `x LEFT JOIN y ON x.a=y.a`? The last leaves no ambiguity as to "left" vs "right" – daShier Sep 05 '19 at 14:58
  • 1
    @daShier From a style perspective, consistency is desired, but I never even consider the ON when thinking of the left database versus right because it is not consistent and not needed to be since you can simply look at your table specified after the JOIN. ANSI89 joins used to reflect that style and it was gotten rid of because it was far more confusing in use. I don't have a problem with using a specific style, but it does not reflect a change in behavior. – UnhandledExcepSean Sep 05 '19 at 15:09
  • I only made w.ColorId = c.ColorId the same for both queries because it shows that it is not the table on the left/right of the equal sign that constitutes which is the left or right table. I didn't mention this is in my post because didn't want to make it any wordier. My opinion is that the left table should also be and to the left of the equal. Just seems logical. – Dave Sep 05 '19 at 20:14
  • You are asking a question about a particular presentation of outer joins. Not all presentations use the phrasing you are unclear about. And do their examples not clarify? Both the question & accepted answer linked as a duplicate do not use that phrasing. It is straightforward & simple to see from the definition/description here & in the duplicate's answer that the left operand of the outer join is the '"left"' table of your quote. So this is reasonably called a duplicate. PS Oracle calls the right left join or the left table of a right join the "outer" table. – philipxy Sep 06 '19 at 01:20
  • Surely you will agree that this is a duplicate of [Which table exactly is the “left” table and “right” table in a JOIN statement (SQL)?](https://stackoverflow.com/q/4109704/3404097)? I googled 'site:stackoverflow.com Which table is the left table of a left join'. Before considering posting please always google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings, names & line numbers & with & without 'site:stackoverflow.com' and read many hits & answers. If you post a question, use one phrasing as title. [ask] – philipxy Sep 06 '19 at 01:21
  • @TimBiegeleisen Maybe it would be "nice" to close this as a duplicate of the question in my last comment? Alternatively, there is the point of view in my comment before that. – philipxy Sep 06 '19 at 01:27

0 Answers0