January 3, 2006 at 8:14 pm
I need to gather some evidence for a case. I do recall hearing or reading somewhere that performing an INNER JOIN after a LEFT OUTER JOIN could potentially return obscure results and that 1 solution was to keep LEFT OUTER joining to get the correct results.
Can someone explain or is there info I can read up on about this?
January 3, 2006 at 9:14 pm
I think it boils down to the data and what you are trying to achieve. The LEFT OUTER join will retrieve all rows from the table on the left of the join whether they match or not. In the subsequent INNER JOIN, obviously if something doesn't match up, you will lose some rows.
In AdventureWorks, I can get a listing of all products whether or not they have been sold:
so.SalesOrderID, p.Name as ProductName
Production.Product as P left join Sales.SalesOrderDetail as so
p.ProductID = so.ProductID
Then, if I want to see the subcategory, I can join on that table. The problem is, the product table allows NULL in the subCateogoryID column. If I want to see all of the products, I'll have to do a left join again.
so.SalesOrderID, p.Name as ProductName, psc.Name as SubCategory
Production.Product as P left join Sales.SalesOrderDetail as so
p.ProductID = so.ProductID left join Production.ProductSubCategory psc
p.ProductSubCategoryID = psc.ProductSubCategoryID
After looking at the data, I see that the products without a category are things I don't care about, like ball bearings. So, in this case I may go ahead and do an INNER JOIN because I don't want to see products unless they have a subcategory assigned. The other reason to do an INNER JOIN would be if the ProductSubCategoryID foreign key did not allow nulls so I would be guaranteed to get all of the products.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
January 3, 2006 at 10:44 pm
>>I do recall hearing or reading somewhere that performing an INNER JOIN after a LEFT OUTER JOIN could potentially return obscure results
Nope, I think what you heard is that applying a filter in a WHERE clause to a table that was LEFT JOIN'ed to, converts that join to an INNER join and changes the resultset.
This left join, that attempts to filter Table2 ...
SELECT a.*, b.*
FROM Table1 As a
LEFT JOIN Table2 As b
On a.JoinColumn = b.JoinColumn
WHERE b.SomeOtherColumn = 'ABC'
... produces a different result than ...
SELECT a.*, b.*
FROM Table1 As a
LEFT JOIN Table2 As b
On a.JoinColumn = b.JoinColumn AND b.SomeOtherColumn = 'ABC'
January 5, 2006 at 5:52 am
"I do recall hearing or reading somewhere that performing an INNER JOIN after a LEFT OUTER JOIN could potentially return obscure results"
Right... I think I know what you mean... you have to be very careful once you start with LEFT JOINs. Let's suppose this model: You have tables Products, Orders and Customers. Not all products necessarily have been ordered, but every order must have customer entered. Task: Show all products, and if the product was ordered, list the ordering customers; i.e., product without orders will be shown as one row, product with 10 orders will have 10 rows in the resultset. This calls for a query designed around "FROM Products LEFT JOIN Orders". Now someone could think "OK, Customer is always entered into orders, so I can make inner join from orders to customers". Wrong. Since the table Customers is joined through left-joined table Orders, it has to be left-joined itself... otherwise the inner join will propagate into the previous level(s) and as a result, you will lose all products that have no orders.
Illustration:
create table #products (prod_id int, prod_name varchar(30))
create table #customers (cust_id int, cust_name varchar(30))
create table #orders (order_id int, customer int, product int, quantity int)
insert into #products (prod_id, prod_name) values (1, 'Computer')
insert into #products (prod_id, prod_name) values (2, 'LCDScreen')
insert into #products (prod_id, prod_name) values (3, 'Keyboard')
insert into #products (prod_id, prod_name) values (4, 'Mouse')
insert into #customers (cust_id, cust_name) values (1, 'John Talbot')
insert into #customers (cust_id, cust_name) values (2, 'Samantha Grawl')
insert into #customers (cust_id, cust_name) values (3, 'Tong Li')
insert into #orders (order_id, customer, product, quantity) values (1,1,1,5)
insert into #orders (order_id, customer, product, quantity) values (2,1,2,5)
insert into #orders (order_id, customer, product, quantity) values (3,1,4,10)
insert into #orders (order_id, customer, product, quantity) values (4,2,1,1)
insert into #orders (order_id, customer, product, quantity) values (5,2,2,1)
insert into #orders (order_id, customer, product, quantity) values (6,3,4,15)
SELECT prod_name, ISNULL(cust_name, '--NO ORDERS--')
FROM #products p
LEFT JOIN #orders o ON o.product = p.prod_id
LEFT JOIN #customers c ON c.cust_id = o.customer
If you use INNER JOIN #customers instead, result will not include product Keybord, since none were ordered. That is, once you join any table using LEFT JOIN, any subsequent tables that are joined through this table, need to keep LEFT JOINs. But it does not mean that once you use LEFT JOIN, all joins have to be of that type... only those mentioned above, that are dependant on the first performed left join. It is perfectly OK in the above example to make INNER JOIN from Products to Prod_Cathegory, if I only want to see products that really have a cathegory.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply