Multiple inner join, correct syntax-CodePudding

I think I have either misunderstood the syntax or there is another syntax for multiple joins.

So, I ran across following query (SQLite3):

cur.execute('''Select department_name AS 'Department',
                (Employees.first_name ||' '|| Employees.last_name) AS 'Salesman',
                Orders.order_id AS 'OrderID',
                Products.product_name AS 'Product',
                ProductsOrders.cost AS 'Sales_Worth'
                From Departments
                Inner JOIN Employees ON Employees.department_id = Departments.department_id
                Inner JOIN Orders ON Orders.employee_id = Employees.employee_id
                INNER JOIN Products ON Products.product_id = ProductsOrders.product_id
                INNER JOIN ProductsOrders ON ProductsOrders.order_id = Orders.order_id
                ORDER BY Department''')

Clearly : department_name is a field of Departments table.

But, how can we state all of the above using only "From Departments"

If Orders.order_id, Products.product_name, ProductsOrders.cost are not fields of Departments table?

Shouldn't we be stating:

FROM Orders, FROM Products, FROM ProductsOrders

as well?

(I have obeyed the following syntax before):

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

CodePudding user response：

If you do a join, you are selecting from the result of the join, not the original table, so you only need to specify the table (or its alias) if the column's name is ambiguous.

CodePudding user response：

Select department_name AS 'Department'

The key with this is to make sure you alias the table. If more than one table has a given field name, your query will fail

Shouldn't we be stating FROM Orders, FROM Products, FROM ProductsOrders as well?

Not really. The point of JOIN is to pull from multiple tables that are related. If there are unrelated records, consider a separate query.

CodePudding user response：

Shouldn't we be stating FROM Orders, FROM Products, FROM ProductsOrders as well?

Something like this already exists and it is valid and allowed in most databases:

SELECT Departments.department_name AS 'Department',
       (Employees.first_name ||' '|| Employees.last_name) AS 'Salesman',
       Orders.order_id AS 'OrderID',
       Products.product_name AS 'Product',
       ProductsOrders.cost AS 'Sales_Worth'
FROM Departments, Employees, Orders, Products, ProductsOrders
WHERE Employees.department_id = Departments.department_id
  AND Orders.employee_id = Employees.employee_id
  AND Products.product_id = ProductsOrders.product_id
  AND ProductsOrders.order_id = Orders.order_id
ORDER BY Department

But the above syntax has been replaced by the ANSI-compliant syntax that uses the keywords INNER JOIN and one ON clause for each joined table (instead of stacking all the join conditions in the WHERE clause).

Although the 2 syntaxes are equivalent in terms of performance (at least for SQLite), the new syntax is what you should learn, because in time you will find that it is more readable, flexible and descriptive of what you are doing (especially when you deal with other types of joins such as CROSS/LEFT/RIGHT/FULL joins).

As a sidenote: never use single quotes for table/column names/aliases. Use (if needed) double quotes, backticks or square brackets.