Multi-table database query

Database multi-Table query is also called join query. This is a join query based on two tables. If one query requires operations on multiple tables, it is called join query, the result set or result of the join query is called the connection between tables. In fact, connection query queries data through the Association of common columns between tables. It is the most important feature of relational database queries. Select Table 1. field name, table 2. field name ,... from table 1, Table 2 where join condition query category: 1. perform a connection operation on the same table in a self-join query. internal Connection query, <divided into three types: natural connection, equivalent connection, and non-equivalent connection> 3. outer Join query, <divided into three types: left Outer Join, right outer join, and full outer join> (the left join is the same as the left outer join, and the right join is the same as the right outer join, mysql does not support full connections. in oracle, the left connection is in the form of "(+)". For example: select *. name (+) = B. name) 4. cross-join query, also known as unconditional query. 5. join query a self-join query a table to establish a connection with itself. Just like two separate tables, you can connect one row of a table to another row in the same table. For example, query the records of all the students whose scores of the selected "101" course are higher than those of the "9505201" student, and sort them by score. * from sclass x, sclass y where x. cno = '000000' and x. degree> y. degree and y. sno = '000000' and y. cno = '000000' order by x. degree desc Inner join is the most common connection method. It returns only the rows matching the relationship between two data sets. Connect the rows that are located within the overlapping parts of two cross datasets. inner join uses a comparison operator to compare data in some columns of a table, and lists the data rows in these tables that match the connection conditions. According to the comparison method used, the inner join query operation lists the data rows that match the connection condition. It uses the comparison operator to compare the column values of the connected columns. Syntax: select field name list from table name [inner] join table name on join condition [where condition expression] example: select student table. student ID, student table. name, student table. class Code, sequence table. course code, course name table. course score from student table inner join student table on student table. student ID = student table. student ID where student table. class code = ''000000'' select x. sno, x. sname, y. cno, y. degree from student xINNER join sclass y on x. sno = y. sno where x. sclass = ''95 1 ''internal connections are divided into three types: 1. equijoin: the so-called equijoin means that tables are connected by the" Equal "relationship to generate a temporary table, then the temporary table is processed and the final result is generated. All columns in the connected table, including duplicate columns, are listed in the query results. (1) query the sno, cname, and degree columns of all students: SELECT x. sno, y. cname, x. degreeFROM score x, course yWHERE x. cno = y. cno (2) query the average score of the course selected in the "95033" Class: SELECT y. cno, avg (y. degree) as "average score" FROM student x, score yWHERE x. class = ''000000'' and x. sno = y. sno group by y. cno (3) query student of all students, score table information: select x. *, y. * from student x, score y where x. sno = y. sno uses equijoin to list authors and publishers in the same city in the authors and publishers tables: SELECT * FROM authors AS a inner join publishers S p ON a. city = p. city2, non-equivalent connection: the connection between tables is not "equal", but other relationships. These operators include >,>=, <=, <,!> ,! <And <>. (1) query the sno, cno, rank columns of all students: select sno, cno, rankfrom score, gradewhere degree between low and upp order by rank (2) select x. sno, x. sname, y. cno, y. degreefrom student x, score ywhere x. sno! = Y. sno3, natural join: removing duplicate columns in the equijoin is a natural join. (1) select x. sno, x. sname, y. cno, y. degreefrom student x, scorewhere x. sno = y. sno uses a natural connection. In the selection list, delete the duplicate columns (city and state) in the authors and publishers tables: SELECT. *? P. pub_id? P. pub_name? P. countryFROM authors AS a inner join publishers AS p ON a. city = p. city ---------------------------- 3. Outer join: different from the internal JOIN, an outer join not only lists the rows that match the connection condition, but also lists the left table (when the outer join is left), right table (right Outer Join) or two tables (full outer join) All data rows that meet the search criteria. External connections are the expansion of internal connections. Apart from connecting the data rows that overlap the two data sets, you can also return unmatched or all data in the Left or Right dataset as required. that is, left outer join; right outer join; full outer join ). some data in the result set returned by the outer connection looks exactly the same as the data returned by the inner connection, but some data is also different from the data returned by the inner connection, some parts of these data rows are from the data in the table, and the other part is the NULL value, the reason for these NULL values is that the other table does not contain a connection to a data item that matches the specified table, the returned query result set contains only the rows that meet the query conditions (WHERE search conditions or HAVING conditions) and connection conditions. When an external connection is used, it returns to the query result set that contains not only rows that meet the connection conditions, but also the left table (when the left outer connection is used) and the right table (when the right outer connection is used) or all data rows in two edge join tables (all Outer Join. Note: The external connection is strong so that records that do not meet the conditions also appear in the result set. Outer join Syntax: Select field name list from table name Left | Right | Full [Outer] join table name ON join condition Outer join is divided into: 1. Left outer join (left outer join or left join): The results table contains all records that meet the conditions in the first table. If the record is matched on the connection condition, the second table returns the corresponding value; otherwise, the second table returns a null value. Select student table. Student ID, student table. Name, student table. course code, student table. Course score from student table left outer join student table on student table. Student ID = student table. Student ID 2. Right outer join (right outer join or right join): contains all records that meet the conditions in the second table in the result table. If the record is matched on the connection condition, the first table returns the corresponding value; otherwise, the first table returns a null value. Select student table. Student ID, student table. Name, student table. course code, student table. Course score from student table right outer join student table on student table. Student ID = student table. Student ID 3. Full outer join (full outer join or full join): The results table contains all the records that meet the conditions in the two tables. If it is a tuple matched on the connection condition, the other table returns the corresponding value; otherwise, the other table returns a null value. Select student table. student ID, student table. name, Region table. course code, course name table. course score from student table full outer join student table on student table. student ID = student table. example of student ID: SELECT. *, B. * FROM student as a left JOIN sclass as bON. sno = B. sno and. sno = ''9502101'' goSELECT. *, B. * FROM student as a right JOIN sclass as bON. sno = B. sno and. sno = ''9502101'' goSELECT. *, B. * FROM student as a full JOIN sclass as bON. sno = B. snogo ------------------------------ 4. Cross join: No join conditions are used for cross join to limit the result set. Records of each table are combined in the form of cartesian products, is to use the rows in two data sources to combine in all possible ways, that is, each row in the dataset must form a new row with each row in the other table. for example, one table has three records, and the other table has four records. After a cross join operation, the result set is composed of 12 records. cross join does not have a WHERE clause. It returns the Cartesian product of all data rows in the two joined tables, the number of rows returned to the result set is equal to the number of rows that meet the query conditions in the first table multiplied by the number of rows that meet the query conditions in the second table. For example, if there are 6 types of books in the titles table, and there are 8 publishers in the publishers table, the number of records retrieved by the following cross join will be 6*8 = 48 rows. Select * from student, sclassSELECT * FROM student a cross join sclass order by a. sno ---------------------------- 5. The UNION operator can combine the query results of two or more SELECT statements into one result set for display, that is, the UNION query is executed. The syntax format of UNION is: select_statementUNION [ALL] selectstatement [UNION [ALL] selectstatement] [… N] Where selectstatement is the SELECT query statement to be combined. The ALL option combines ALL rows into the result set. If this parameter is not specified, only one row is retained for the duplicate row in the Union query result set. During a joint query, the column title of the query result is the column title of the first query statement. Therefore, to define a column title, it must be defined in the first query statement. To sort the Union query results, you must also use the column name, column title, or column number in the first query statement. When using the UNION operator, ensure that there are the same number of expressions in the selection list of each joint query statement, and each query selection expression should have the same data type, or they can be automatically converted to the same data type. During automatic conversion, the system converts low-precision data types to high-precision data types. In UNION statements that contain multiple queries, the execution sequence is from left to right. Brackets can be used to change the execution sequence. For example: Query 1 UNION (query 2 UNION query 3) select topicbody, posttime from bbs_topicunion allselect replybody, posttime from bbs_reply -------------------------- 6. Complex query-composite join select. student ID,. name, B. course code, B. course score, c. course name, d. instructor code: from student Table a, student table B, curriculum c, Teaching Table dwhere (. student ID = B. student ID) and (B. course code = c. course code) and (c. course code = d. course code) connect SELECT dbo with more than three tables. kb. xq, dbo. kbk. kcmc, dbo. kbk. lbdh, dbo. kbk. jsmc, dbo. kb. jse, dbo. bj. bj, dbo. kb. jc, 2 AS num, dbo. kb. zc, CASE dbo. kb. ds WHEN ''single ''then'' 1' 1' when'' dual ''then'' '2' when''' ''then'' '0'' end as ds, dbo. kb. zc1, dbo. kb. zc2FROM dbo. kb INNER JOINdbo. bj ON dbo. kb. bh = dbo. bj. bh INNER JOINdbo. kbk ON dbo. kb. xq = dbo. kbk. xq AND dbo. kb. bh = dbo. kbk. bh ANDdbo. kb. kcdm = dbo. kbk. kcdmWHERE (dbo. kb. jc = 1) OR (dbo. kb. jc = 3) OR (dbo. kb. jc = 5) OR (dbo. kb. jc = 7) OR (dbo. kb. jc = 9) OR (dbo. kb. jc = 11) ---------------------------- conclusion: No connection can be used to directly connect columns of the text, ntext, or image data type, but these columns can be indirectly connected. Example: SELECT p1.pub _ id? P2.pub _ id? P1.pr _ infoFROM pub_info AS p1 inner join pub_info AS p2ON DATALENGTH (p1.pr _ info) = DATALENGTH (p2.pr _ info)