How to Best Organize the Inner Joins in (Select) Statement

how to best organize the Inner Joins in (select) statement

For most queries, order does not matter.

• An INNER JOIN is both associative and commutative so table order does not matter
• SQL is declarative. That is, how you define the query is not how the optimiser works it out. It does not do it line by line as you wrote it.

That said...

• OUTER JOINs are neither associative nor commutative
• For complex queries, the optimiser will "best guess" rather than go through all possibilities which "costs" too much. Table order may matter here

Issue regarding the Order of Inner Join in SQL Server

Always: ORDER is arbitrary without an ORDER BY.

This means that even if you did use ORDER BY for the INSERT, then a later SELECT isn't guaranteed to return with this order unless you use ORDER BY on the SELECT.

In other words: there is no implied or natural order to a table or view.

You can only have a defined order with ORDER BY

Edit

However. it should be noted that INNER JOINs are both commutative and associative. That is, you get the same results. You're asking about ORDER which I answered above

For more, see

• How order of joins affect performance of a query
• how to best organize the Inner Joins in (select) statement

How to Organize Multiple Joins SQL

Of course you can join on multiple tables in a query. That is a big part of the power of SQL.

In your particular case, you don't need the join to the Suppliers table, because the column is already in Products.

Also, you need to be careful about your SELECT and GROUP BY clauses. In general, you should put all non-aggregated columns in the GROUP BY:

SELECT O.OrderID, O.OrderDate, C.City, C.Country, C.PostalCode, C.ContactName, 
       O.CustomerID, O.ShipperID, D.ProductID,
       COUNT(D.ProductID) as ProductCount,
       P.SupplierID
FROM Customers C INNER JOIN
     Orders O 
     ON O.CustomerID = C.CustomerID INNER JOIN
     OrderDetails D 
     ON O.OrderID = D.OrderID INNER JOIN
     Products P
     ON D.ProductID = P.ProductID
GROUP BY O.OrderID, O.OrderDate, C.City, C.Country, C.PostalCode, C.ContactName, 
       O.CustomerID, O.ShipperID, D.ProductID, P.SupplierId
ORDER BY OrderDate DESC;

The WHERE 1=1 is also unnecessary.

I wonder if this query really does what you want. However, you don't state what you actually want the query to do, so I'm merely speculating.

Interchanging FROM and INNER JOIN

The two queries are syntactically equivalent (so they do produce the same results). I don’t know what makes you think that one is more expensive than the other, but it is surely not the case.

SQL is a descriptive language, as opposed to a procedural language. You tell the database the results you want, and let it decide how to proceed. The query planner parses the query and produces the execution plan it thinks is best. It will happily "start" from one table or the other, regardless of the order in which they appear in the query.

SQL Sub-query or INNER-JOIN?

join is faster than subquery.

subquery makes for busy disk access, think of hard disk's read-write needle(head?) that goes back and forth when it access: User, SearchExpression, PageSize, DrilldownPageSize, User, SearchExpression, PageSize, DrilldownPageSize, User... and so on.

join works by concentrating the operation on the result of the first two tables, any subsequent joins would concentrate joining on the in-memory(or cached to disk) result of the first joined tables, and so on. less read-write needle movement, thus faster

INNER JOIN ON vs WHERE clause

INNER JOIN is ANSI syntax that you should use.

It is generally considered more readable, especially when you join lots of tables.

It can also be easily replaced with an OUTER JOIN whenever a need arises.

The WHERE syntax is more relational model oriented.

A result of two tables JOINed is a cartesian product of the tables to which a filter is applied which selects only those rows with joining columns matching.

It's easier to see this with the WHERE syntax.

As for your example, in MySQL (and in SQL generally) these two queries are synonyms.

Also, note that MySQL also has a STRAIGHT_JOIN clause.

Using this clause, you can control the JOIN order: which table is scanned in the outer loop and which one is in the inner loop.

You cannot control this in MySQL using WHERE syntax.

SQL Query with and without inner join

They are semantically the same. A decent optimizer will recognize this an generate equally efficient plans. An experienced SQL coder will also recognize this ;)

Joe Celko quote, "There are at least seven ways to write a query; only two are worth
using."

---

Related Topics