Question about inner join

[eldan88]

Hey Guys. I am learning about inner join, and I am a little confused about how the syntax work

 

On one example there is the following syntax

 

$query = "SELECT family.Position, food.Meal ".
"FROM family, food ".
    "WHERE family.Position = food.Position";

 

On all the other examples I came across the following syntax

 

SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

 

 

 

Why on the first example the table name is prepend to the column name on the select statement? and why there is no syntax "INNER JOIN ON"

 

Thanks for your help in advance!

[Barand]

Use the second syntax (ie INNER JOIN .. ON).

 

1. The first syntax can only be used for the (default) INNER JOIN. If you want a LEFT or RIGHT JOIN you need the second syntax so keep it consistent.

2. The second syntax separates the structure of the query from the selection criteria in the WHERE clause.

3. Your queries will run faster.

 

Strictly you only need to specify table.column where two or  more tables contain the same column name thus removing ambiguity about which one to use. Personally I prefer to document the origin of each column using a short table alias (especially if the table name is something like "bacterialogicalculturegrowthstatistitics") so I would write that second query as

SELECT c.ID, c.NAME, o.AMOUNT, o.DATE
FROM CUSTOMERS c
     INNER JOIN ORDERS o 
        ON c.ID = o.CUSTOMER_ID;

[eldan88]

Thanks for the reply Barand. So you are saying that the following would be the default for INNER JOIN?

 

$query = "SELECT family.Position, food.Meal ".
"FROM family, food ".
    "WHERE family.Position = food.Position";

[Barand]

I am saying if you use that syntax you can only get an INNER JOIN (the default join type)

 

It is equivalent to (but less efficient than)

SELECT family.Position, food.meal
FROM family
    JOIN food ON family.Position = food.Position