Join Abfragen

[Günni]

So, das LEFT und RIGHT JOIN habe ich jetzt soweit verstanden. Jetzt habert es nur noch an dem INNER JOIN, LEFT (OUTER) JOIN und RIGHT (OUTER) JOIN.
Was bewirkt das outer bzw. inner???

[Günni]

Join Abfragen

Hi,
könnte mir jemand den unterschied INNER JOIN, LEFT (OUTER) JOIN und RIGHT (OUTER) JOIN erklären. Was bewirken die einzelnen JOIN Abfragen??? Blicke da irgendwie nicht so ganz durch.

[MelloPie]

7.20 JOIN Syntax
MySQL supports the following JOIN syntaxes for use in SELECT statements:

table_reference, table_reference
table_reference [CROSS] JOIN table_reference
table_reference INNER JOIN table_reference join_condition
table_reference STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference join_condition
table_reference LEFT [OUTER] JOIN table_reference
table_reference NATURAL [LEFT [OUTER]] JOIN table_reference
{ oj table_reference LEFT OUTER JOIN table_reference ON conditional_expr }
table_reference RIGHT [OUTER] JOIN table_reference join_condition
table_reference RIGHT [OUTER] JOIN table_reference
table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference

Where table_reference is defined as:

table_name [[AS] alias] [USE INDEX (key_list)] [IGNORE INDEX (key_list)]

and join_condition is defined as:

ON conditional_expr |
USING (column_list)

Note that in versions before Version 3.23.16, the INNER JOIN didn't take a join condition!

The last LEFT OUTER JOIN syntax shown above exists only for compatibility with ODBC:

A table reference may be aliased using tbl_name AS alias_name or tbl_name alias_name:
mysql> select t1.name, t2.salary from employee AS t1, info AS t2
where t1.name = t2.name;

INNER JOIN and , (comma) are semantically equivalent. Both do a full join between the tables used. Normally, you specify how the tables should be linked in the WHERE condition.
The ON conditional is any conditional of the form that may be used in a WHERE clause.
If there is no matching record for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find records in a table that have no counterpart in another table:
mysql> select table1.* from table1
LEFT JOIN table2 ON table1.id=table2.id
where table2.id is NULL;

This example finds all rows in table1 with an id value that is not present in table2 (that is, all rows in table1 with no corresponding row in table2). This assumes that table2.id is declared NOT NULL, of course. See section 12.5.5 How MySQL Optimizes LEFT JOIN and RIGHT JOIN.
The USING (column_list) clause names a list of columns that must exist in both tables. A USING clause such as:
A LEFT JOIN B USING (C1,C2,C3,...)

is defined to be semantically identical to an ON expression like this:
A.C1=B.C1 AND A.C2=B.C2 AND A.C3=B.C3,...

The NATURAL [LEFT] JOIN of two tables is defined to be semantically equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both tables.
RIGHT JOIN works analogously as LEFT JOIN. To keep code portable across databases, it's recommended to use LEFT JOIN instead of RIGHT JOIN.
STRAIGHT_JOIN is identical to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases where the join optimizer puts the tables in the wrong order.
As of MySQL Version 3.23.12, you can give hints about which index MySQL should use when retrieving information from a table. This is useful if EXPLAIN shows that MySQL is using the wrong index. By specifying USE INDEX (key_list), you can tell MySQL to use only one of the specified indexes to find rows in the table. The alternative syntax IGNORE INDEX (key_list) can be used to tell MySQL to not use some particular index.

[Günni]

Das hier ist auch sehr hilfreich http://www.little-idiot.de/mysql/mysql-118.html

[Günni]

Blick´s immer noch nicht... *Schäm*