CODEWITHSUNDEEP
sql-serverjoinleft-join
Raj Rao
Raj Rao

Reputation: 9148

SQL Server - Join Question - 3 tables

Consider the example from MSDN documentation:

SELECT p.Name, pr.ProductReviewID
FROM Production.Product p
LEFT OUTER JOIN Production.ProductReview pr
ON p.ProductID = pr.ProductID

In this example, it is clear that the table on the left is "Production" and that is where all rows will be returned from, and then only those that match in ProductReview.

But now consider the following hypothetical query with 3 tables A,B,C

select * from A
inner Join B on A.field1 = B.field1
left outer join C on C.field2 = b.Field2

Which is the left table in this query (from which all records will be returned, regardless of a match to C)? Is it A or B? Or is it the result of the join from A & B?

My confusion arises from the following MSDN documentation, which states that "Outer joins can be specified in the FROM clause only" which would mean that the left table in my hypothetical query is A, but then I dont have an ON clause that specifies the join condition - in which case is my hypothetical query a bad one?

Upvotes: 1

Views: 966

Answers (5)

Andriy M
Andriy M

Reputation: 77747

They use the term FROM clause in a general (broad) sense meaning the whole section of the query that starts from the keyword FROM and includes all the joins there are.

Here's a fuller context (note the previous sentence):

Inner joins can be specified in either the FROM or WHERE clauses. Outer joins can be specified in the FROM clause only.

See? They mean you cannot specify an outer join in the WHERE clause as is the case with inner joins. You can only do that in the FROM clause (that is, after however many other joins too). The result will be applied to the result of the previous joins.

Upvotes: 1

SSRS Developer
SSRS Developer

Reputation: 11

From your joins 

A inner Join B on A.field1 = B
left outer join C on C.field2 = b.Field2

You need to have records from table A and B. The left join only has data from table C field field2 matching the B table, but note that table A field2 does not have to match.

To see your data for table C run the following:

select c.*
from A inner Join B on A.field1 = B.field1 left outer join C on C.field2 = b.Field2

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135938

Since there is an INNER JOIN between A and B, only rows from B that match A will qualify for the LEFT JOIN to C.

Upvotes: 1

Thomas
Thomas

Reputation: 64674

The terms 'left" and "right" are not sufficiently specific in this context. Instead, you should use the terms "preserved" and "unpreserved". In that light, tables A and B are preserved and table C is unpreserved.

The reference in the MSDN documentation is meant to imply you cannot use joins (outer or otherwise) in the Select, Where, Group By, Having or Order By clauses outside of a subquery (where they are still in a From clause).

Upvotes: 1

Phil Sandler
Phil Sandler

Reputation: 28046

I'm not 100% sure I understand you question, but assuming I am understanding it correctly:

Your "left" table in your hypothetical query is B, since your ON condition specifies the B.Field2.

Upvotes: 1

Related Questions