CODEWITHSUNDEEP
mysqlsqljoin
benishky
benishky

Reputation: 981

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

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;

Upvotes: 8

Views: 5281

Answers (5)

Jean Raymond Daher
Jean Raymond Daher

Reputation: 394

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

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726967

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.

Upvotes: 1

subas_poudel
subas_poudel

Reputation: 456

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.

Upvotes: 4

Barmar
Barmar

Reputation: 782166

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).

Upvotes: 9

Mureinik
Mureinik

Reputation: 312086

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.

Upvotes: 1

Related Questions