SQL Statement Joint query detailed
2011-03-01 18:58:22| Category: MySQL| Report | Font size Subscription
Example:
Person table and User table are not constrained
Person Table:
User table:
There are several associations
1.UNION
Format:
Query statements
UNION [ALL] query statement
[UNION [all] query statement] [... n]
Description
The all option means that all rows are merged into the result collection. When the item is not specified, only one row is retained by the repeating row in the result collection of the union query.
In union statements that include multiple queries, the order in which they are executed is from left to right, using parentheses to change the order of execution. For example:
Query 1 Union (query 2 union query 3)
Execution: Select id,name from the user UNION Select id,name from;
Note:The number of SQL sentence columns
must be the same, the field can be arbitrary
2.JOIN
Join is used to join two tables on an on condition, with four main types:
(i) Internal connection
INNER join: Internally joins records from two tables, and the inner join returns rows only if there is at least one row that belongs to two tables that matches the join condition. (indicates intersection)
(ii) External connection
Left Join/left OUTER join: Outer joins records from two tables and contains all records from the table on the right. If a record in the left table does not have a matching record in the right table, all select list columns in the right table in the associated result set are null values. Understand that even if the on condition is not met, the records in the left table are all displayed, and the right table field of the record in the result set is a null value. (Difference set)
Right join/right OUTER join: Outer joins records from two tables, and contains all records in the table on top. In short, it is the opposite of the left join. (Difference set)
Full Join/full OUTER join: The complete outer join returns all rows from the left and right tables. It is the left join and right join and merge, and the data of both tables is displayed. (also set)
(iii) Cross-linking
The cross join does not take a WHERE clause, it returns the Cartesian product of all data rows of the two connected tables, and the number of rows returned to the result set equals the number of data rows in the first table that match the query criteria multiplied by the number of data rows in the second table that meet the query criteria.
basic syntax for join (note the result comparison):
1. ImplementationSELECT * from user as A
joins person as B on a.id=b.id;Or
SELECT * from user as A
inner join person as B on a.id=b.id;
The results are as follows:
2. ImplementationSelect
a.* from user as A joins person as B on a.id=b.id;3. ImplementationSELECT * from user as A left
join person as B on a.id=b.id;4.SELECT * from
person as A right
joins user as B on a.id=b.id;
5.full Join
MySQL 5 does not support full join so union with left and right associations
SELECT * from user
Left JOIN money on User.id=money.id
UNION
SELECT * from user
Right JOIN money on User.id=money.id
6. ImplementationSELECT * from the user cross JOIN person;
Smart RememberSELECT * from A LEFT join B where condition one: Table takes the from most recent table as the guideline (a table) and the second: Join B is placed in table A to leave (where the opposite side is the opposite of its own left and right) its third: from A table as a guideline, Joi n table has multi-field content, or null value for less
Right join just the oppositeSELECT * from A right join B where condition
