Basic usage Tutorials for JOIN statements in Mysql and their field impact on performance _mysql

Basic use of JOIN statements

The SQL (MySQL) JOIN is used to get data from these tables based on the relationships between the fields in two or more tables.

Joins are usually used with the on keyword, and the basic syntax is as follows:

... From table1 inner| Left| Right JOIN table2 on Conditiona
Table1 is often called the left table, and table2 is called the right table. The ON keyword is used to set matching criteria to qualify which rows are desired in the result collection. If you need to specify additional criteria, you can add a WHERE condition or LIMIT to limit the number of records returned.

The following are the most common two-table joins to illustrate the use of MySQL join, see "MySQL Join multiple tables" for multiple table joins.

MySQL JOIN Classification

The JOIN is roughly divided into the following three categories by function:

1. INNER join (inner join): Gets a record of a connection matching relationship in two tables.
2. Left join: Gets the left table (table1) full record, that is, the right table (table2) does not have a corresponding matching record.
3. Right join: On the contrary to the left join, get the right table (table2) full record, that is, the left-hand table (table1) does not match the corresponding record.

About MySQL full join fully connected

MySQL does not provide full join in the SQL standard: All two table records are taken out, regardless of whether they have a corresponding record. To work around this problem, you can use the UNION keyword to combine the left join with the right join to simulate a full join.

MySQL INNER JOIN

The INNER JOIN is used to obtain a record of a connection matching relationship in two tables. Here are two raw data tables:

The user who owns the article in the article table is associated with the user table through the UID field. By looking at the data, it is not hard to find that there are no articles published for uid=3 users, and aid=4 in the article cannot find the corresponding record in the UID table (the user may have been deleted and the article to which it belongs) has been preserved.

We list the data that corresponds to the user one by one of the article used.

SELECT ... INNER JOIN ... The ON statement is as follows:

SELECT article.aid,article.title,user.username from article INNER JOIN user on article.uid = User.uid

Return query results as follows:

For INNER JOIN, equate to the following SQL statement:

SELECT article.aid,article.title,user.username from article,user WHERE article.uid = User.uid

CROSS JOIN

A CROSS join is a cross connection, without the specified on condition:

SELECT article.aid,article.title,user.username from article CROSS JOIN user

The result is the product of the two data tables connected, that is, the Cartesian product.

In fact, in MySQL (MySQL only) the CROSS join behaves the same as the INNER join, and the result of not specifying on conditions is Cartesian product, which results in an exact match of two tables.

The INNER join and the CROSS join can omit the INNER or CROSS keyword, so the following SQL effect is the same:

Flat view print?

... From table1 INNER JOIN table2 ...

From table1 CROSS JOIN table2 ...

From table1 JOIN table2

The performance impact of field character set encoding for join

Let's take a look at the sample code:

Build utf-8 encoded table T1:

CREATE TABLE IF not EXISTS ' T1 ' (
 ' name ' varchar ' NULL DEFAULT ',
 KEY ' name ' (' name ')
) Engine=myisam DEFAULT Charset=utf8;

Random inserts some data, the quantity is bigger, the later experiment result clearer, steals a lazy, constructs the stochastic string inserts the statement

INSERT into T1 (name) 
Select Concat (
char (Round ((rand ()) *25) +97),
char (Round ((rand ()) *25) +65),
CHAR (((rand ()) *25) +65, char (
round ((rand ()) *25) +97), char (
round ((rand ()) *25) +65),
char (round (round) ( (rand ()) *25) +65,
char (Round ((rand ()) *25) +97),
char (Round ((rand ()) *25) +65)
)

Each time you insert a record, write a loop with the familiar script (Python,php,shell, etc.) and execute more than 10,000 times.

Copy the table into a new table T2, delete some of the data, 1000 or so. (Recommended use phpMyAdmin)

Then copy the T2 as T3 and change the field to gb2312 encoding.

Using a LEFT JOIN statement, write a statement to find out what records T2/T3 is less than T1.

The statement is simple, as follows:

SELECT Sql_no_cache t1.name, t2.name from
T1 left
JOIN t2 in t1.name = T2.name
WHERE t2.name is NULL 
L Imit 0, 30

Note Join Sql_no_cache to disable the MySQL cache.

First look at the code consistent T2 table, phpMyAdmin execution results:

Show Row 0-29 (1,129 total, query takes 0.0010 seconds)
The average time elapsed is about 0.0010 seconds.

SELECT Sql_no_cache t1.name, t3.name from
T1 left
JOIN t3 on t1.name = T3.name
WHERE t2.name is NULL 
LIMIT 0, 30

phpMyAdmin Execution Results:

Show Row 0-29 (30 total, query takes 0.1871 seconds)
Difference two order of magnitude!

Query Statement Explanation: