Shulou(Shulou.com)05/31 Report--

SQL statement how to achieve joint query, for this problem, this article details the corresponding analysis and answer, hoping to help more want to solve this problem of small partners to find a simpler and easier way.

sql statement joint query detailed explaination2011 -03-01 18:58:22| Category: mysql| Subscribe

Examples:

The person and user tables have no constraints

There are several associations

1.UNION

Format:

query statement

UNION [ALL] query statement

[UNION [ALL] query statement][…n]

Description:

The ALL option merges all rows into the result set. When this item is not specified, duplicate rows in the federated query result set retain only one row.

In UNION statements that include multiple queries, the order of execution is left-to-right, which can be changed by using parentheses. For example:

Query 1 UNION (Query 2 UNION Query 3)

Select id,name from user UNION select id,name from person;

Note: sql sentence column number must be the same, the field can be arbitrary 2.JOIN

JOIN is used to join two tables according to the ON condition. There are four main types:

(i) Internal connections

INNER JOIN: Inner joins records in two tables, returning rows only if at least one row belonging to both tables meets the join criteria. (indicates intersection)

(ii) External connections

LEFT JOIN / LEFT OUTER JOIN: externally joins records from two tables and contains all records from the left table. If a record in the left table has no matching record in the right table, all select list columns in the right table in the associated result set are null. It is understood that even if the ON condition is not met, all records in the left table are displayed, and the right table field of such records in the result set is null. (difference set)

RIGHT JOIN / RIGHT OUTER JOIN: externally joins records from two tables and contains all records from the right table. In short, it is the reverse of LEFT JOIN. (difference set)

FULL JOIN / FULL OUTER JOIN: Complete outer join returns all rows in the left and right tables. That is, LEFT JOIN and RIGHT JOIN and merge, and all the data in the left and right tables are displayed. (union)

(iii) Cross-connections

A cross join without a WHERE clause returns the Cartesian product of all rows in the joined tables, and the number of rows returned to the result set is equal to the number of rows in the first table that match the query multiplied by the number of rows in the second table that match the query.

Basic syntax for JOIN (note the results contrast):

1. perform select * from user as A join 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 were as follows:

2. Execute select A.* from user as A join person as B on A.id = B.id; 3. execute select * from user as A left join person as B on A.id = B.id; 4.select * from person as A right

join user as B on A.id=B.id;

5.full join

MySQL 5 does not support full join, so join 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. execute select * from user CROSS JOIN person;

Select * from A left join B where condition one: the table from the nearest table as the criterion (A table) two: join B to be placed in the table A left (where the opposite left and right are exactly opposite to their own left and right) three:from A table as the criterion, Join table has multi-field content, less NULL right join exactly the opposite select * from A right join B where condition

About sql statement how to achieve joint query questions to share the answer here, I hope the above content can be of some help to everyone, if you still have a lot of doubts not solved, you can pay attention to the industry information channel to learn more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.