Changing outer joins to inner join

Hi,
I have a main table that references many tables (something like Orders
table, referencing Customers,Employees...).
As an example, there are many orders that have Null value for their
CustomerID therefore I need an Outer Join from Orders table to retrieve all
rows.
Now a View that joins Customers to Orders cannot be indexed because of the
Outer Join.
I was thinking of adding a row to Customers with value of (#) for
CompanyName column and assigning its ID to all Orphan rows in Orders.
I must:
- Write trigger on Orders table to insert ID of # record for new orphan
orders instead of Null.
- Filter # record when I need a real Inner Join.
- and some other considerations...
Instead an Inner Join between Customers and Orders can return all orders and
this query can be used in indexed view.
The performance of queries from main(Orders) table is the most critical
issue. Is it a good decision for changing such queries to Inner Join?
I appreciated any suggestion.
LeilaDo you really have to use an index view ? I just wanna keep track that even
it is an outer join the indexes will be used if possible and if existing. I
would´t got the way you are describing although this situation is uncommon
to me, to enter an order where no customer is assigned to it. (?) Ok, but i
would´t worsen up the situation by building a trigger and doing this kind of
workaround. I would build my best on the possibiliies i have, perhaps there
is a way to tune up our queries ?!
http://www.sql-server-performance.com/tuning_joins.asp
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Leila" <leilas@.hotpop.com> schrieb im Newsbeitrag
news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a main table that references many tables (something like Orders
> table, referencing Customers,Employees...).
> As an example, there are many orders that have Null value for their
> CustomerID therefore I need an Outer Join from Orders table to retrieve
> all
> rows.
> Now a View that joins Customers to Orders cannot be indexed because of the
> Outer Join.
> I was thinking of adding a row to Customers with value of (#) for
> CompanyName column and assigning its ID to all Orphan rows in Orders.
> I must:
> - Write trigger on Orders table to insert ID of # record for new orphan
> orders instead of Null.
> - Filter # record when I need a real Inner Join.
> - and some other considerations...
> Instead an Inner Join between Customers and Orders can return all orders
> and
> this query can be used in indexed view.
> The performance of queries from main(Orders) table is the most critical
> issue. Is it a good decision for changing such queries to Inner Join?
> I appreciated any suggestion.
> Leila
>|||Hi
You can change your outer joins to a union of a query that uses an inner
join plus a join where the where clause eliminates those returned by the
inner join.
If you want to use a default for your order id, make the column not nullable
with the default.
I would have to ask why you have orders without customers?
John
"Leila" <leilas@.hotpop.com> wrote in message
news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a main table that references many tables (something like Orders
> table, referencing Customers,Employees...).
> As an example, there are many orders that have Null value for their
> CustomerID therefore I need an Outer Join from Orders table to retrieve
> all
> rows.
> Now a View that joins Customers to Orders cannot be indexed because of the
> Outer Join.
> I was thinking of adding a row to Customers with value of (#) for
> CompanyName column and assigning its ID to all Orphan rows in Orders.
> I must:
> - Write trigger on Orders table to insert ID of # record for new orphan
> orders instead of Null.
> - Filter # record when I need a real Inner Join.
> - and some other considerations...
> Instead an Inner Join between Customers and Orders can return all orders
> and
> this query can be used in indexed view.
> The performance of queries from main(Orders) table is the most critical
> issue. Is it a good decision for changing such queries to Inner Join?
> I appreciated any suggestion.
> Leila
>|||Thanks Jens,
>I just wanna keep track that even
> it is an outer join the indexes will be used if possible and if existing
- I do agree, but indexes and tuning the queries can help to limited extent.
If they could be ultimate solution for all situations, Indexed Views
wouldn't be invented! What if your query needs a covering index with more
that 16 columns? What if the index length exceeds 900 bytes? You will not be
able to create useful indexes.
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:#0Fi1lCSFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Do you really have to use an index view ? I just wanna keep track that
even
> it is an outer join the indexes will be used if possible and if existing.
I
> would´t got the way you are describing although this situation is uncommon
> to me, to enter an order where no customer is assigned to it. (?) Ok, but
i
> would´t worsen up the situation by building a trigger and doing this kind
of
> workaround. I would build my best on the possibiliies i have, perhaps
there
> is a way to tune up our queries ?!
> http://www.sql-server-performance.com/tuning_joins.asp
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Leila" <leilas@.hotpop.com> schrieb im Newsbeitrag
> news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> > I have a main table that references many tables (something like Orders
> > table, referencing Customers,Employees...).
> > As an example, there are many orders that have Null value for their
> > CustomerID therefore I need an Outer Join from Orders table to retrieve
> > all
> > rows.
> > Now a View that joins Customers to Orders cannot be indexed because of
the
> > Outer Join.
> > I was thinking of adding a row to Customers with value of (#) for
> > CompanyName column and assigning its ID to all Orphan rows in Orders.
> > I must:
> > - Write trigger on Orders table to insert ID of # record for new orphan
> > orders instead of Null.
> > - Filter # record when I need a real Inner Join.
> > - and some other considerations...
> >
> > Instead an Inner Join between Customers and Orders can return all orders
> > and
> > this query can be used in indexed view.
> > The performance of queries from main(Orders) table is the most critical
> > issue. Is it a good decision for changing such queries to Inner Join?
> > I appreciated any suggestion.
> > Leila
> >
> >
>|||Thanks John,
Using union is great idea but in my query, the main table(orders) is joined
with other parent tables(like customers, employees..).
The same situation can exist: orders with EmployeeID=Null as well.
Please note that orders without customer is just an example, I skipped
describing the real situation (and why orphan rows exist) because tables in
northwind are famous and everybody is familiar with them.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:e3LJqvCSFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi
> You can change your outer joins to a union of a query that uses an inner
> join plus a join where the where clause eliminates those returned by the
> inner join.
> If you want to use a default for your order id, make the column not
nullable
> with the default.
> I would have to ask why you have orders without customers?
> John
> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> > I have a main table that references many tables (something like Orders
> > table, referencing Customers,Employees...).
> > As an example, there are many orders that have Null value for their
> > CustomerID therefore I need an Outer Join from Orders table to retrieve
> > all
> > rows.
> > Now a View that joins Customers to Orders cannot be indexed because of
the
> > Outer Join.
> > I was thinking of adding a row to Customers with value of (#) for
> > CompanyName column and assigning its ID to all Orphan rows in Orders.
> > I must:
> > - Write trigger on Orders table to insert ID of # record for new orphan
> > orders instead of Null.
> > - Filter # record when I need a real Inner Join.
> > - and some other considerations...
> >
> > Instead an Inner Join between Customers and Orders can return all orders
> > and
> > this query can be used in indexed view.
> > The performance of queries from main(Orders) table is the most critical
> > issue. Is it a good decision for changing such queries to Inner Join?
> > I appreciated any suggestion.
> > Leila
> >
> >
>|||"Leila" <leilas@.hotpop.com> wrote in message
news:edXR8yCSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> - I do agree, but indexes and tuning the queries can help to limited
> extent.
> If they could be ultimate solution for all situations, Indexed Views
> wouldn't be invented! What if your query needs a covering index with more
> that 16 columns? What if the index length exceeds 900 bytes? You will not
> be
> able to create useful indexes.
Not familiar with your DDL, or exactly how you're relating these tables to
one another, but columns with a lot of NULL values don't tend to make very
good indexes either... Are you really relating these tables to one another
using a CustomerID field that can be NULL? How do you keep track of who to
ship the order to? Or, more importantly, where to send the bill? In a
situation like this, would it make sense to generate all your NULL
customerid orders in a separate 'exception'-type query instead of plopping
them down in the middle of orders that were actually placed by customers?
Just wondering...
Instead of going through all the trigger-writing, etc., why not just UPDATE
all current NULL customerid's with the default value you specified; then
define the column as NON-NULL with a Default?|||Thank Michael,
Please note that orders without customer is just an example, I skipped
describing the real situation (and why orphan rows exist) because tables in
northwind are famous and everybody is familiar with them.
"Michael C#" <xyz@.abcdef.com> wrote in message
news:K%Fae.3416$RP1.1452@.fe10.lga...
> "Leila" <leilas@.hotpop.com> wrote in message
> news:edXR8yCSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> > - I do agree, but indexes and tuning the queries can help to limited
> > extent.
> > If they could be ultimate solution for all situations, Indexed Views
> > wouldn't be invented! What if your query needs a covering index with
more
> > that 16 columns? What if the index length exceeds 900 bytes? You will
not
> > be
> > able to create useful indexes.
> Not familiar with your DDL, or exactly how you're relating these tables to
> one another, but columns with a lot of NULL values don't tend to make very
> good indexes either... Are you really relating these tables to one
another
> using a CustomerID field that can be NULL? How do you keep track of who
to
> ship the order to? Or, more importantly, where to send the bill? In a
> situation like this, would it make sense to generate all your NULL
> customerid orders in a separate 'exception'-type query instead of plopping
> them down in the middle of orders that were actually placed by customers?
> Just wondering...
> Instead of going through all the trigger-writing, etc., why not just
UPDATE
> all current NULL customerid's with the default value you specified; then
> define the column as NON-NULL with a Default?
>
>