understanding join

im looking at this code:

select customers.name
from customers, orders, order_items, books
where customers.customerid = orders.customerid
an开发者_如何学Pythond orders.orderid = order_items.orderid
and order_items.isbn = books.isbn
and books.title like '%java%';

and find it difficult to understand it.

first i have 4 tables, and the code is making a dynamic table,

should i look at it different order to understand it better?

first, im selecting customer.name, but only after the last code is run and i have my dynamic table, right?

should i try and line each 2 tables line by line using the dynamic table that was made a line earlier?

and also while the table is being made, does all the data from the table is written or only what i ask in the where?

ill appreciate your help.

---

As far as theory goes, when you query multiple tables in an attempt to join all the table, a temporary cross-product of all the tables involved is calculated. This would imply that the total number of rows be the multiplication of the number of rows of each table.

In practice however, the implementation of SQL (MySQL in your case) will take care to efficiently compute the query. A query execution path is computed, based on various parameters, and used to obtain the desired output.

A better way of writing the above statement would be to follow the new syntax:

SELECT customers.name FROM customers
INNER JOIN orders ON customers.customerid = orders.customerid
INNER JOIN order_items ON orders.orderid = order_items.orderid
INNER JOIN books ON order_items.isbn = books.isbn
WHERE books.title LIKE '%java%'

Read more from here for reasons why.

---

Rapid Fire did a good job explaining the "cross product" aspect. Here are some additional hints.

First and foremost: SQL is not procedural code. Its a query. The whole query takes place simultaneously. You need to read the whole query to understand it all. There is no "order" that you read queries in. For an analogy, X = Y is the same as Y = X... similarly, you read the whole query as a single unit.

Next, SQL abstracts you away from the table creation process. Try to think of it from a mathematical point of view, based on Relational Algebra. That is, Tables are Relations (or Tuples) and so forth. Look up C. J. Date's books and read them. There is a mathematical language that SQL was designed off of, and learning that will greatly improve your ability to understand databases.