Test Table SQL statement
Create TableA (IDintUnsigned not NULL Primary Keyauto_increment, nameChar( -) not NULL default "') Engine=MyISAMdefaultCharSet=UTF8;Create TableB (IDintUnsigned not NULL Primary Keyauto_increment, nameChar( -) not NULL default "', a_idint not NULL) Engine=MyISAMdefaultCharSet=UTF8;Insert intoAValues(NULL,'Zhang San'),(NULL,'John Doe'),(NULL,'Harry');Insert intoBValues(NULL,'Tom',1),(NULL,'Jack',2),(NULL,'Wally',1),(NULL,'Joan',4);
Table results:
Table A. Table B
INNER JOIN (join or INNER join): Use comparison operators to match rows in two tables based on the values of the columns that are common to each table
/*INNER JOIN * /SelectA.*B.* fromaInner Joinb ona.ID=b.a_id;/*equivalent to * /SelectA.*B.* fromA, bwherea.ID=b.a_id;
Outer Joins. An outer join can be a left outer join, a right outer join, or a full outer join.
A left outer join (either outer join): If a row in the left table does not have a matching row in the right table, all select list columns in the right table in the associated result set row are null values.
/* LEFT OUTER join * / Select A.*, B.* from aleftjoin b on = b.a_id;
A right outer join (either a outer join): A reverse join of a left outer join. All rows of the right table will be returned. If a row in the right table does not have a matching row in the left table, a null value will be returned for left table.
/* RIGHT outer join * / Select A.*, B.* from arightjoin b on= b.a_id;
Full join or FULL outer join: A complete outer join returns all rows from the left and right tables. When a row does not have a matching row in another table, the selection list column for the other table contains a null value. If there are matching rows between the tables, the entire result set row contains the data values of the base table.
/* full Join */ Select A.*, B.*from fulljoin on = b.a_id;
Result error, what reason?
Cross join: A cross join without a WHERE clause will produce a Cartesian product of the table involved in the join. The number of rows in the first table multiplied by the number of rows in the second table equals the size of the Cartesian product result set. (Table1 and table2 cross-joins generate 3*3=9 Records)
/* Cross join (with where statement) */ Select A.*, B.*from crossjoin on = b.a_id;
/* Cross join (without where statement) */ Select A.*, B.*from crossjoin B;
/* Equivalent to */
Select a.*,b.* from a A, B;
MySQL inner join, leftist, right join, full join, cross join difference