SQL statement and set of UNION, intersection JOIN (inner joins, outer join), cross-connect (CROSS JOIN Cartesian product), difference (NOT IN) two

2010-11-20  来源:本站原创  分类:Database  人气:103 

Induction

(A) in connection with the internal connection to connect queries that match the criteria set out rows of data, it is connected using the comparison operators compare the value of the column. Connection within three categories:
1, the equivalent connection: in the join condition to use the equal sign (=) operator to compare the value of the column are connected, the results are listed in all the columns in the connection table, including one of the duplicate columns.
Cases, listed below to connect with authors and publishers the equivalent table in the same city the author and publishers:
SELECT *
FROM authors AS a INNER JOIN publishers AS p
ON a.city = p.city
2, ranging from connection: the connection conditions of use in addition to equal comparison operator other than more of the connected column column values. These operators include >,>=,<=,<,!>,!< and <>.
3, the natural connection: the connection conditions of use equals (=) operator to compare the value of the column was connected, but it is a select list that included the query result set column, and remove the duplicate columns in the connection table.
Example, in the select list authors and publishers tables to delete duplicate columns (city and state):
SELECT a. *, p.pub_id, p.pub_name, p.country
FROM authors AS a INNER JOIN publishers AS p
ON a.city = p.city
(B) outer join outer join to return to the query result set contains not only meet the join condition of the line, but also the left table (left outer join or left join)), the right table (right outer join or right connection) or two one side of the table access (full outer join) all the rows.
left join (left join) returns include all records in left table and right table in the join fields are equal the record;
right join (the right connections), including the right to return all records in the table and left the table in the join fields are equal the record;
For example 1: SELECT a. *, b. * FROM luntan LEFT JOIN usertable as b
ON a.username = b.username
Example 2: SELECT a. *, b. *
FROM city as a FULL OUTER JOIN user as b
ON a.username = b.username
(C) the cross-connection cross-connect without a WHERE clause, which returns two tables are connected to the Cartesian product of all rows and return the result set number of rows equal to the first table that match the query number of rows of data multiplied by the second table that meet the query number of rows. Example, titles table in the six books, while publishers table in the eight publishing houses, the following cross-connect the number of records retrieved will be equal to 6 * 8 = 48 lines.
For example: SELECT type, pub_name
FROM titles CROSS JOIN publishers
ORDER BY type

Example explanation

In connection: the line connecting only match left outer join: left side of the table contains all rows (regardless of the existence of the right of the table row to match them), and the right side of the table all the rows that match the right outer join: the right side of the table contains all the line (left side of the table regardless of whether there is match them in rows), and left the table all the rows that match the full outer join: contains the left and right all the rows the two tables, regardless of whether the other side of the table there and they match line.
Cross-connect generates Cartesian product - it does not use any match or selection criteria, but directly to a data source to another data source for each row and each

Line eleven matches

To give an example.
Table A
id name
1
2 Li
3 Wang

Table B
id address A_id
1 1 Beijing
2 Shanghai 3
3 Nanjing 10
/******************************** Left join left join ************* ****************************/
Inclusive: A Table B Table tolerance, left to connect the left table is complete. (Left join or left outer join)
SQL statement is as follows:
SELECT A.name, B.address
FROM A
LEFT JOIN B ON A.id = B. A_id
Query results:
name address
Li Zhang Beijing NULL
Wang Shanghai
/******************************** Right join right connection ************* ****************************/
Inclusive: B table inclusive of Table A, the right to connect the right table is complete. (Right join or right outer join)
SQL statement is as follows:
SELECT A.name, B.address
FROM A
RIGHT JOIN B ON A.id = B. A_id
Query results:
name address
Shanghai Wang Zhang Beijing
NULL Nanjing
/******************************** Inner join within the connection ************* ****************************/
Exclusive: A, B form at least one match, only to return rows. The intersection of two tables
SQL statement is as follows:
select A.name, B.address from A
inner join B
on A.id = B. A_id

Query results:
name address
Shanghai Wang Zhang Beijing
connections within the inner join is equivalent to the following sql:
SELECT A.name, B.address
FROM A, B
WHERE A.id = B. A_id
/******************************** Full join all connected ************* ****************************/
Note: full outer join returns in connection of the two data sets of all data, regardless of whether they have a matching row. Functionally, it is equivalent to

For these two data sets were left outer join and right outer join, then use the elimination of duplicate lines and operating results of the above two sets and a result set

. (Full join, or full outer join)
SQL statement is as follows:
select * from A
full join B
Query results:
id name id address A_id
1 1 1 Beijing
2 Li 1 1 Beijing
3 Wang 1 1 Beijing
1 2 3 Shanghai
2 Lee 2 Shanghai 3
3 King 2 Shanghai 3
1 3 Nanjing 10
2 Li 3 Nanjing 10
Nanjing King 3 3 10
/******************************** CROSS JOIN (without conditions where ...)****** *****************************/
Note: Returns 3 * 3 = 9 records, that Cartesian product
SQL statement is as follows:
SELECT * FROM A
CROSS JOIN B
Query results:
id name id address A_id
1 1 1 Beijing
2 Li 1 1 Beijing
3 Wang 1 1 Beijing
1 2 3 Shanghai
2 Lee 2 Shanghai 3
3 King 2 Shanghai 3
1 3 Nanjing 10
2 Li 3 Nanjing 10
Nanjing King 3 3 10
CROSS JOIN is equivalent to:
select * from A, B

Note:
1. On A.id = B.id equivalent to using (id) / / here the same field name to
2 When a table from MySQL to retrieve information, you can choose which prompted it an index.
If EXPLAIN shows that MySQL uses the index list of possible errors in the index, this feature is very useful.
By specifying USE INDEX (key_list), you can tell MySQL to use indexes may be the most appropriate index to find rows in the table.
Choose one of two optional methods IGNORE INDEX (key_list) can be used to tell MySQL not to use a particular index.

Efficiency:
1.inner join faster than the left join Note: inner join within a connection equivalent to the following sql: SELECT A.name, B.address FROM A, B WHERE A.id = B. A_id
So the general use of the general connection on it.
(2) connect the field to build an index

相关文章