Various joins in MySQL (cross join, inner join, left [outer] Join)

Various joins in MySQL

1. Cartesian Product (cross join)
In MySQL, you can think of cross join or omit cross as join, or use ','
For example
Select * From Table1 cross join Table2
Select * From Table1 join Table2
Select * From Table1, Table2

Because the returned result is the product of the two joined data tables, when, On or using conditions are generally not recommended, because when there are too many data table ProjectsIt will be very slow.
Generally, left [outer] Join or right [outer] Join is used.

2. Inner join
In MySQL, inner join is called an equijoin, that is, an equijoin condition must be specified.
In MySQL, cross and inner join are divided together.
See MySQL help manual
Http://dev.mysql.com/doc/Refman/5.0/en/join.html
Join_table:
Table_reference [inner | cross] Join table_factor [join_condition]

3. MySQL outer connections are divided into left outer connections and right connections,
That is, in addition to returning results that meet the connection conditions, the left table (left join) Or the right table (right join) does not meet the connection conditions, the corresponding use of NULL.

A. Left [outer] Join
Select column_name from Table1 left [outer] Join Table2 on table1.column = table2.column
In addition to returning results that meet the connection conditions, you also need to display the left table that does not meet the connection conditionsCorresponding to null

B. Right [outer] Join
Select column_name from Table1 right [outer] Join Table2 on table1.column = table2.column
Right is similar to left join, except for Displaying results that meet the connection conditions., You also need to display data columns that do not meet the connection conditions in the right table, corresponding to the use of null

--------------------------------------------
Add the where, on, and using display conditions.
1. Where clause
2. On
3. Using clause. If the two columns of the join two tables have the same nameYou can use using
For example
Select <column_name> from <Table1> left join <Table2> using (<column_name>)

Connecting two extra tables
Example:
Mysql> select artists. Artist, CDs. Title, genres. Genre
-> From CDs
-> Left join genres
-> On CDs. genreid = genres. genreid
-> Left join artists
-> On CDs. artistid = artists. artistid;
Or
Mysql> select artists. Artist, CDs. Title, genres. Genre
-> From CDs
-> Left join genres
-> On CDs. genreid = genres. genreid
-> Left join artists
-> On CDs. artistid = artists. artistid
-> Where (genres. Genre = 'pop ');
--------------------------------------------

Note:

When querying multiple tables in MySQLThink about which connection method is more efficient.
1. Cross join (Cartesian Product) or inner join
[Inner | cross] Join
2. left Outer Join left [outer] Join or right outer join right [outer] Join

Note that the connection conditions where, on, and using are specified.

--------------------------------------------
Understand the usage of MySQL join defined in the MySQL manual:
// Understand the following definition

table_references:
table_reference[,
table_reference]...

// Use ',' to separate different Join expressions
A table reference is also known as a join expression.


table_reference:
table_factor
|join_table


// Each join expression consists of the table_factor and joi tables.The N expression forms join_table.

table_factor:

tbl_name[[As]alias] [index_hint)]
| (table_references)
| {OJ
table_referenceLeft Outer Jointable_reference
Onconditional_expr}


// Data table table_factor. Pay attention to its recursively defined table_ References


join_table:
table_reference[Inner | cross] Join
table_factor[join_condition]
|table_referenceStraight_join
table_factor
|table_referenceStraight_jointable_factorOncondition
|
table_referenceLeft [outer] Jointable_reference join_condition
|
table_referenceNatural [left [outer] Jointable_factor
|table_referenceRight [outer] Join
table_reference join_condition
|table_referenceNatural [right [outer] Jointable_factor


// Join expression of the data table join_table

join_condition:
Onconditional_expr

| Using (column_list)

// The Connection condition definition of the connection expression uses on or using

index_hint:
Use {index | key} [for join] (index_list
)
| Ignore {index | key} [for join] (index_list)
| Force {index | key} [for join] (index_list)


index_list:
index_name[,index_name]...

Note the following for join mentioned in the MySQL manual:

1.
In MySQL,CROSS JOINIs a syntactic equivalentINNER JOIN(They can replace each other). In standard SQL, they are not equivalent.INNER JOINIs used withONClause,CROSS JOINIs used otherwise.
Mentioned in the manual
In standard SQL, cross join (Cartesian product) is different from inner join. However, in MySQL, the two are the same, that is, [cross | inner] Join, which can be replaced by each other, you can only use join.

2. A table reference can be aliased usingtbl_name AS alias_nameOrtbl_name alias_name:

Select t1.name, t2.salary
From employee as T1 inner join info as T2 on t1.name = t2.name;
Aliases can be used for data tables.

3., Operator
For example
Select * From Table1, Table2
Since inner join is the same as cross join in MySQL, inner join and is the same in MySQL, both produce the Cartesian Product Cartesian product of the two tables.
(Equal to the number of rows in two tables)

However, the priority of the number is lower than that of inner join, cross join, and left join.

Therefore
If you mix comma joins with the other join types when there is a join condition, an error of the form unknown column 'col _ name' in 'on Clause' may occur.

4. When to use on and where
On should specify the connection conditions when the user data table is connected;

Where is used to restrict the selected columns

For example, on a. Column = B. Column
Where a. Column = 'hello'

5. You can use left join to view the joined tables that do not meet the connection conditions.Because the left join that does not meet the condition is displayed as null.
If there is no matching row 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 rows in a table that have no counterpart in another table:


Select left_tbl .*
From left_tbl left join right_tbl on left_tbl.id = right_tbl.id
Where right_tbl.id is null;

This example finds all rows in left_tbl with an ID value that is not present in right_tbl (that is, all rows in left_tbl with no corresponding row in right_tbl ). this assumes that right_tbl.id is declared not null.


6.
You do not need to specify the names of the connection conditions for a table to be connected.
On a. Column = B. column is used only when it is different from a. column_a = B. column_ B
You can use using (column)
Of course, you can also use multiple using (C1, C2, C3)

The using (column_list) clause names a list of columns that must exist in both tables. if tables A and B both contain columns C1, C2, and C3, the following join compares corresponding columns from the two tables:


A left join B using (C1, C2, C3)

7.
Others:
#
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 to left join. To keep code portable pair SS databases, it is recommended that you use left join instead of right join.
#
The {OJ... left Outer Join ...} syntax shown in the join syntax description exists only for compatibility with ODBC. the curly braces in the syntax shoshould be written literally; they are not metasyntax as used elsewhere in syntax descriptions.

#
Straight_join is similar to join, Except t that the Left table is always read before the right table. this can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.