Explain which table to choose "FROM" in a JOIN statement

Question

I'm new to SQL and am having trouble understanding why there's a FROM keyword in a JOIN statement if I use dot notation to select the tables.columns that I want. Does it matter which table I choose out of the two? I didn't see any explanation for this in w3schools definition on which table is the FROM table. In the example below, how do I know which table to choose for the FROM? Since I essentially already selected which table.column to select, can it be either?

For example:

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

They are both the from tables..., you can say from orders and join customers and the query will return the same results if it is an inner join. On left join, the "from" table is the left table. This confused me when I started using sql so note this, a left join is the same thing as a left outer join the only difference is syntax — CSharper, Oct 03 '14 at 18:30
Yes. It can be either. In your example, the two table names could be swapped, and the rows returned would be the same. (Where the placement of the table names does matter is with a "outer" join operation, i.e. "a LEFT JOIN b " is equivalent to "b RIGHT JOIN a", but it is different from "a RIGHT JOIN b". — spencer7593, Oct 03 '14 at 18:41
As far as the definition of the `FROM` table: what you're actually selecting "from" is the result from the join operation, not one table or the other. What the SQL is specifying is "join" operation on two tables "`Customers JOIN Orders ON ...`". The query is selecting "from" the result of the join operation. (The order of the expressions in the equality comparison don't matter either. `a.col = b.col` is equivalent to specifying `b.col = a.col`; a property of the equality comparison operator.) It's best practice to qualify *all* column references in the query (as is shown in your example.) — spencer7593, Oct 03 '14 at 19:18
The SELECT is "from" the result of everything after the FROM. The FROM is not about just the first table after it. — philipxy, Apr 13 '20 at 23:57

score 8 · Accepted Answer · answered Oct 03 '14 at 18:32

The order doesn't matter in an INNER JOIN.

However, it does matter in LEFT JOIN and RIGHT JOIN. In a LEFT JOIN, the table in the FROM clause is the primary table; the result will contain every row selected from this table, while rows named in the LEFT JOIN table can be missing (these columns will be NULL in the result). RIGHT JOIN is similar but the reverse: rows can be missing in the table named in FROM.

For instance, if you change your query to use LEFT JOIN, you'll see customers with no orders. But if you swapped the order of the tables and used a LEFT JOIN, you wouldn't see these customers. You would see orders with no customer (although such rows probably shouldn't exist).

score 4 · Answer 2 · answered Oct 03 '14 at 18:33

4

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

answered Oct 03 '14 at 18:33

subas_poudel

456
2
17

1

+1. What the query is selecting "from" is the resultset specified by the join operation, not one table or the other. – spencer7593 Oct 03 '14 at 19:24

score 1 · Answer 3 · answered Oct 03 '14 at 18:31

1

For an inner join it does not matter which table is in the from clause and which is in the join clause. For outer joins it of course does matter, as the table in the outer join is allowed to have "missing" records.

answered Oct 03 '14 at 18:31

Mureinik

297,002
52
306
350

score 1 · Answer 4 · answered Oct 03 '14 at 18:33

It does not matter for inner joins: the optimizer will figure out the proper sequence of reading the tables, regardless of your choice for the ordering.

For directional outer joins, it does matter, because these are not symmetric. You choose the table in which you want to keep all rows for the first FROM table in a left outer join; for the right outer join it is the other way around.

For full outer joins it does not matter again, because the tables in full outer joins are used symmetrically to each other.

In situations when ordering does not matter you pick the order to be "natural" to the reader of your SQL statement, whatever that means for your model. SQL queries very quickly become rather hard to read, so the proper ordering of your tables is important for human readers of your queries.

score 0 · Answer 5 · answered Oct 03 '14 at 18:34

0

Well in your current example the from operator can be applied on both tables.

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

->Will work like your code

The comma will join the two tables.

From just means which table you are retrieving data from.

In your example, you joined the two tables using different syntax. it could also have been :

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

all the code written will generate same results

answered Oct 03 '14 at 18:34

Jean Raymond Daher

306
3
11

6

Good examples, but I would highly advise against writing queries like you do in the first example – CSharper Oct 03 '14 at 18:35
could you explain why ? – Jean Raymond Daher Oct 03 '14 at 18:37
1

It's sloppy syntax and it's harder to debug/enhance let alone read. If you were joining 4 tables it would get messy quick. Take a look at this post I just found http://stackoverflow.com/questions/11251751/which-join-syntax-is-better – CSharper Oct 03 '14 at 18:41
3

The old-school comma syntax for the join operation is valid and it's still supported. And that's a good thing, for all the legacy SQL out there that still runs. But I can't fathom a good reason OP would want to use the comma operator rather than the improved syntax using the `JOIN` keyword. – spencer7593 Oct 03 '14 at 18:46

Explain which table to choose "FROM" in a JOIN statement

5 Answers5