what is difference between 'where' and different 'joins' in mysql ??

Question

Possible Duplicate:
In MySQL queries, why use join instead of where?

Joins are always confusing for me..can anyone tell me what is difference between different joins and which one is quickest and when to use which join and what is difference between join and where clause ?? Plz give ur answer in detail as i already read about joins on some websites but didn't get the concept properly.

Answer 1

This query:

SELECT c.customer_name, o.order_id, o.total
FROM customers c, orders o
WHERE c.id = o.customer_id

and this

SELECT c.customer_name, o.order_id, o.total
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id

make no difference on what is executed on the mysql server but the join is (imho) way more readable. Expecially for more complex joins:

Here is an article about the topic: http://www.mysqlperformanceblog.com/2010/04/14/is-there-a-performance-difference-between-join-and-where/

Answer 2

Rather than quote an entire Wikipedia article, I'm going to suggest your their article on SQL Joins.

An SQL JOIN clause combines records from two or more tables in a database. It creates a set that can be saved as a table or used as is. A JOIN is a means for combining fields from two tables by using values common to each. ANSI standard SQL specifies four types of JOINs: INNER, OUTER, LEFT, and RIGHT. In special cases, a table (base table, view, or joined table) can JOIN to itself in a self-join.

A programmer writes a JOIN predicate to identify the records for joining. If the evaluated predicate is true, the combined record is then produced in the expected format, a record set or a temporary table, for example.

There is an older syntax that uses WHERE clauses to imply INNER JOINs. While it works, and will produce queries that run exactly like queries specified with the INNER JOIN syntax, it is deprecated because most people find it more confusing.

Here's the documentation for the MySQL JOIN syntax.