Information on extended query

Donna Kerr

I'm sorry if this is not the correct way to ask a question (my first time). The qrp and by extension the extended queries have fallen on me. I have some basic knowledge of SQL but, I am not certain what this is. If someone could please explain this I would greatly appreciate it. In the extended query for the shipping/delivery report I have a statement PART.ID* = WORK_ORDER.PART_ID. My question is the '*' is it a left join, if not what is it?

Thank You
Mike

j-voth

MBusey-

Yes that is the old style of doing joins in SQL where the join was performed in the predicate and the * indicates which of the tables is the preserved table (i.e. *= means LEFT OUTER JOIN; =* means RIGHT OUTER JOIN).

For example from the sample Northwinds database that shipped with SQL Server 2000 and earlier:

SELECT Customers.CustomerId
FROM Customers, Orders
WHERE Customers.Customerid *= Orders.CustomerId
ORDER BY Orders.CustomerId

Will return all customers from the Customers table who do and do NOT have any orders. For customers that do NOT have any orders, the right part of the result set (the data from the orders table) will be all NULL.

That same query in the up-to-date style is:

SELECT Customers.CustomerId
FROM Customers
LEFT OUTER JOIN Orders
ON Customers.CustomerId = Orders.CustomerId
ORDER BY Orders.CustomersId

Now, a real zinger:

SELECT Customers.CustomerId
FROM Customers, Orders
WHERE Customers.Customerid *= Orders.CustomerId

AND Orders.CustomerId IS NULL
ORDER BY Orders.CustomerId

will produce different results than:

SELECT Customers.CustomerId
FROM Customers
LEFT OUTER JOIN Orders
ON Customers.CustomerId = Orders.CustomerId
WHERE Orders.CustomerId IS NULL
ORDER BY Orders.CustomersId

Why? It is because the order in which SQL evaluates the statements. When the OUTER JOIN is done in the predicate, the statements are evaulated BEFORE the result set of the OUTER JOIN. Since there are no NULL entries in the Orders table, the result set contains all customers. When the OUTER JOIN is done in the new syntax, an intermediate result table is generated before the predicate is applied. In so doing, the intermediate table contains rows where the Orders.CustomerId is NULL.

Does that make sense? Please let me know!

Take care,
Alex

Wichtige_Lösungen_in_der_Wissensdatenbank_August_2012.pdf