SQL Learning Series (i) Multi-table query

In the database, there is a certain connection between the tables, if you need to make a series of queries on the data, it is not enough to get the data information from a table or a library, it is necessary to extract the required data from multiple tables or multiple databases, and some simple SELECT statement query is unable to meet the needs of users. At this point, you need to refer to advanced queries.

The following uses a and B to represent the database tables.

1. Inner JOIN query-INNER JOIN use comparison operators to compare the data in individual tables and to list all rows of data in each table that match the criteria.

Keyword: INNER join or join

(1). Equivalent connection

Statement format: Select a *, B * from a as a INNER joins B as B on a.id=b.id

(2). Non-equivalent connections-a non-equivalent connection is a comparison operator other than the equivalent (=) operator, mainly >, <, >=, <=, <>, and scope operators between

Statement format: Select a *, B * from a as a INNER joins B as B on A.id=b.id and b.age<40

2. Outer JOIN-Full connection specifies that all matching rows in two tables and unmatched rows are returned.

Keyword: full OUTER JOIN

Statement format: SELECT * from A *, b * from a as a full OUTER JOIN B as B on a.id=b.id

3. Left OUTER join-left OUTER JOIN specifies that all matching rows in the two table and rows that do not match in the left table of the JOIN keyword are returned.

Keywords: left OUTER JOIN

Statement format: SELECT * from A *, b * from A as a left OUTER JOIN B as B on a.id=b.id

4. Right outer join-right OUTER join specifies that all matching rows in the two tables are returned, and that there are no matching rows to the right of the Join keyword.

Keyword: Right OUTER JOIN

Statement format: SELECT * from A *, b * from a as a right OUTER JOIN B as B on a.id=b.id

5. Cross-query-using a cross-query will return a possible combination of all the rows in the two tables, that is, in the result set, all two tables that might match the data rows will be displayed. The cross-query uses where and does not use where sub-statement two cases.

Do not use the WHERE clause: all rows in the connected two tables will be returned.

Use the WHERE clause: all rows in the concatenated two tables are returned minus the data rows that do not conform to the WHERE clause query criteria.

(1) Do not use the WHERE clause

Statement format: SELECT * from A *, b * from A as a cross JOIN B as B

(2) using the WHERE clause

Statement format: Select a *, B * from A as a cross JOIN B as B where a.id=b.id and number = ' 10 '

6. Natural connection-when querying multiple tables, not only can you connect to two different tables, but you can also query a table with yourself, which is called Natural connection

Statement format: Select a *,b * from a as a, as B where a.id=b.id

7. Federated queries-using the union operator to combine two or more two SELECT statement query result collections into one result set is called a federated query

Statement format: SELECT * from A where age=20 UNION select * from B where sex= ' man '

8. Cross-query-use the INTERSECT operator to return the intersection of two or more two SELECT statement query result sets is a cross-query.

Statement format: SELECT * FROM A INTERSECT select * from B where age=20

9. Differential query-use the except operator to return two or more two SELECT statements the difference set of query result set is the difference query

Statement format: SELECT * FROM A EXCEPT select * from B where age<35

Write the comparative basis, hope can help you, thank you for your reading.

　　　

SQL Learning Series (i) Multi-table query