sql2-Sub-query, join query

SQL Common advanced queries include: Join queries, subqueries.

Sub-query:

 Useflowershopdb--subquery: Use another SELECT statement as a condition or data source in a SELECT statement.--Query BLOCK: A SELECT clause.--nested queries: Nesting another query block within one query block. The inner query is called a subquery, and the outer query is called the main query.--1 unrelated subqueries: subqueries do not depend on the main query, and subqueries can be executed independently.--as a conditionSELECT *  fromtb_detailedWHEREc_id=(SELECTc_id fromTb_categoryWHEREC_name='Birthday Flower')--as a columnSELECT(SELECTC_name fromTb_categoryWHEREc_id= +),(SElECTD_remark fromTb_detailedWHEREd_id= -)--2 correlated subqueries: subqueries depend on the main query, and subqueries cannot be executed independently.SELECTDe.*  fromtb_detailed deWHEREde.c_id=(SElECTca.c_id fromTb_category CAWHEREca.c_id=1001  andca.c_id=de.d_id)--keyword: in all--NOTE: subqueries must be enclosed ().--in indicates whether the query result is in a subquery result set--all indicates that the query result satisfies all the criteria of the subquery--any represents the query result to satisfy any one of the criteria of the subquery--In for multiple result setsSELECT *  fromtb_detailedWHEREd_idinch(SElECTd_id fromTb_detailedWHERED_remark='Birthday with Flowers')-- All-->all, <all, >=all, <allSELECTDe.*  fromtb_detailed deWHEREde.d_id>  All(SElECTpr.d_id fromTb_product PRWHEREPr.p_price>3)--equivalentSELECTDe.*  fromtb_detailed deWHEREde.d_id>(SElECT MAX(pr.d_id) fromTb_product PRWHEREPr.p_price>3)-- any-->any, <any, <=any, >=anySELECTDe.*  fromtb_detailed deWHEREde.d_id>  any(SElECTpr.d_id fromTb_product PRWHEREPr.p_price>3)--equivalentSELECTDe.*  fromtb_detailed deWHEREde.d_id>  any(SElECT MIN(pr.d_id) fromTb_product PRWHEREPr.p_price>3)--Key Words:--EXISTS is used to check if a subquery returns at least one row of data, and the subquery does not actually return any data, but instead returns TRUE or false "specify subquery to detect the existence of a row"SELECT *  fromTb_userWHERE EXISTS(SELECT NULL)--equivalent toSELECT *  fromTb_userSELECTDe.*  fromtb_detailed deWHERE EXISTS(SElECTca.c_id fromTb_category CAWHEREca.c_id=1001  andca.c_id=de.d_id)--equivalent toSELECTDe.*  fromtb_detailed deWHEREde.c_idinch(SElECTca.c_id fromTb_category CAWHEREca.c_id=1001  andca.c_id=de.d_id)--not EXISTS If the subquery does not return rows, the WHERE clause in not EXISTS is satisfiedSELECTDe.*  fromtb_detailed deWHERE  not EXISTS(SElECTca.c_id fromTb_category CAWHEREca.c_id=1001  andca.c_id=DE.D_ID)

Join query:

CREATE DATABASESelectdb UseSelectdbSELECT *  fromASELECT *  fromB--Multi-table conditional querySELECT *  fromA, bWHEREA.a=B.A--jion Table Connection--[join IS and which table is connected, on what is the connection relationship?]--Inner connection INNER join--only matching rows are connectedSELECTA.a,a.b,a.c,b.d,b.e fromAINNER JOINB onA.a=B.A--left outer connection [OUTER] Join--contains all rows from the table on the left (regardless of whether there are rows in the right table that match them), and all rows in the right tableSELECTA.a,a.b,a.c,b.d,b.e fromA Left JOINB onA.a=B.A--right outer connection-[OUTER] Join--contains all rows of the right table (regardless of whether there are rows in the left table that match them), and all rows in the left tableSELECTA.a,a.b,a.c,b.d,b.e fromA Right JOINB onA.a=B.A--fully connected full [OUTER] Join--contains all rows from the left and right two tables, regardless of whether there are rows in the other side of the table that match them. SELECTA.a,a.b,a.c,b.d,b.e fromA Full JOINB onA.a=B.A--Cross Join--Generate Cartesian product-it does not use any matching or selection criteria, but instead directly matches each row in one data source with each row of another data source--each piece of data in a crosses 3*3 with each data in B.SELECT *  fromA Cross JOINB--self-connect--self-connection refers to the same table itself connected to itself. This unary join is typically used to extract data from a reflexive relationship (also known as a recursive relationship). SELECT * fromB B1JOINB B2 onB1. A=B2. DSELECT *  fromB

More Search Links: http://www.cnblogs.com/hoojo/archive/2011/07/16/2108129.html

sql2-Sub-query, join query