Is there any benefits doing it this way?

psycorpse

I have a question regarding joins

i have this sql statement now..

    $sql = "SELECT company.name as 'company_name', address.address as 'company_address', address.city as 'city', states.state_code as 'state', address.zip_code as 'zip'
FROM company, address, states
WHERE company.name $compare '%$search_for%' AND company.id=address.company_id AND address.state_id=states.id";

Is there any benefits to using LEFT JOIN like this?

    $sql = "SELECT company.name as 'company_name', address.address as 'company_address', address.city as 'city', states.state_code as 'state', address.zip_code as 'zip'
    FROM company
    LEFT JOIN address ON company.id=address.company_id 
    LEFT JOIN states ON address.state_id=states.id
    WHERE company.name $compare '%$search_for%'";

It seems that when I tried to use the left joins it was taking longer to display the records. I ran the same search string so it wasn't like it was picking different records each time. Maybe I am not using the LEFT JOIN correctly??? I am not sure..

nemonoman

Left Joins are slower.

You would use a left join if you were uncertain about related records existing.

It appears from your naming that you expect that for every company id you'll have an address record with that company id?

And for every address.stateid you'll have a state.id?

If you can answer the above questions YES then you don't need and should NOT use an OUTER or LEFT join.

Left / outer joins are used where it's not certain that related records exist. Example: company id exists, but no address record for that company exists.

Inner or standard join

SELECT company.name , address.address
FROM company, address
WHERE company.id=address.company_id

AND company.id='Foo'

would NOT produce a record in the above case

Outer or Left join

SELECT company.name, address.address
FROM company
LEFT JOIN address ON company.id=address.company_id

WHERE company.id='Foo'

WOULD produce a record with company name returned, and null values for address

psycorpse

Thanks that helps out. I knew about what the joins did but I wasn't sure if one way was faster or better for that matter... Thanks. I will use the inner join way... just because that data should be there and it was much faster.

Thanks again.

Sxooter

In a setup where there ARE always records in the righthand table to match the ones in the left hand table, a left join should be equal to a standard join. It's only when there are few matches that the left join should cost more, and then it's just because more rows have to be delivered. Any database that's significantly slower for a left join that's equivalent to an inner join (i.e. every row has a mate) has a bad planner or query executor.