0

im looking at this code:

select customers.name
from customers, orders, order_items, books
where customers.customerid = orders.customerid
and 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.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Tal Pergricht
  • 57
  • 3

2 Answers2

3

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.

Community
  • 1
  • 1
RabidFire
  • 6,280
  • 1
  • 28
  • 24
0

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.

Dragontamer5788
  • 1,957
  • 1
  • 12
  • 20
  • I think this statement could be dangerous for beginners: "SQL is not procedural code. Its a query. The whole query takes place simultaneously." Nothing is ever simultaneously, we all need a good technique to read a query and every query optimiser will apply a "procedure" to interpret your query and send it for execution. For performance reasons, all these things must be known, no sql server is a magical black box where you can dump data and retrieve it instantaneously. – ceteras Dec 02 '10 at 12:48
  • 1
    @ceteras: Sure, optimization is important, and understanding the parsing sequence and optimization will allow you to create more efficient queries. But the original poster is having issues with basic semantics, ie: joins. This is absolutely crucial to use SQL in any way. For now, it is more important to understand SQL from the "Black Box" perspective. After he gets an understanding of that... then he can start worrying about optimization. – Dragontamer5788 Dec 02 '10 at 15:32