Explain which table to select "FROM" in the JOIN statement

Question

I'm new to SQL and don't understand why the JOIN has the FROM keyword if I use dot notation to select the tables.columns I want. Does it matter which table I choose from the two? I did not see any explanation for this in the definition of w3schools, whose table contains the FROM table. In the example below, how do I know which table to select for FROM ? Since I essentially already chose which table.column to select, can it be?

For instance:

 SELECT Customers.CustomerName, Orders.OrderID FROM Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName; 

Answer 1

The order does not matter in the INNER JOIN .

However, this does matter in LEFT JOIN and RIGHT JOIN . In LEFT JOIN table in the FROM is the main table; the result will contain every row selected from this table, while the rows named in the LEFT JOIN table may be missing (these columns will be NULL as a result). RIGHT JOIN similar, but vice versa: rows may not be in the table named FROM .

For example, if you change your request to use LEFT JOIN , you will see customers without orders. But if you changed the order of the tables and used LEFT JOIN , you would not see these clients. You will see orders without a customer (although such lines probably should not exist).

Answer 2

The from statement refers to a join, not a table. The join of the table will create a set from which you will select the columns.

Answer 3

For inner join doesn't matter which table is in the from clause and in the join clause. For outer join this, of course, matters because the table in outer join allowed to have "missing" records.

Answer 4

It does not matter for internal joins: the optimizer will determine the correct sequence for reading tables, regardless of your choice for ordering.

For directional external connections this matters because they are not symmetrical. You select the table in which you want to save all rows for the first FROM table in the left outer join; for the right outer join this is the other way around.

For complete outer joins this does not matter, because tables in full outer joins are used symmetrically to each other.

In situations where the order does not matter, you select the order "natural" for the reader of your SQL statement, no matter what it means for your model. SQL queries very quickly become quite difficult to read, so the correct order of your tables is important for readers of your queries.

Answer 5

Well in your current example, the from statement can be applied to both tables.

 SELECT Customers.CustomerName, Orders.OrderID FROM Customers,Orders WHERE Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName; 

-> Will work as your code

A comma joins two tables.

From just which table you are retrieving data from.

In your example, you joined two tables using a different syntax. it could also be:

 SELECT Customers.CustomerName, Orders.OrderID FROM Orders INNER JOIN Customers ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName; 

all written code will generate the same results