Android Program ape must master the SQLite database table query

Fundamentals of SQL queries: two scenarios. First, single-table query: Filter the records in the table based on the Where condition, form an intermediate table (this intermediate table is not visible to the user), and then select the corresponding column according to the Select column of select to return the result finally. Second, two-table connection query: The two-table quadrature (Cartesian product) with on conditions and connection type filtering to form an intermediate table; then filter the records of the intermediate table based on the Where condition and return the query results according to the column specified by select. Third, multi-table connection query: First and second table in accordance with the two tables to join the query, and then use the query results and the third table to do the connection query, and so on, until all the tables are connected, and finally form an intermediate result table, and then filter the records of the intermediate table according to where conditions, and returns the result of the query based on the column specified by select. The process of understanding SQL queries is a theoretical basis for SQL optimization.
Cross JOIN Statement 1: Implicit cross-joins, no crosses joins. SELECT tt.id, TT. Order_number, Tp.id, TP. Namefrom TABLE TT, portable TP WHERE tt.id=1; get four rows of data statement 2: Explicit cross joins, using the Intersect join. SELECT Tt.id,tt. ORDER_NUMBER,TP.ID,TP. Namefrom TABLE TT, portable TP WHERE tt.id=1; The result of statement 1 and statement 2 is the same, 2*2 data.
Second, the internal connection (INNER join) was originally built with SQLite table, and now used to use Navicat. INNER JOIN (INNER join): There are two types, explicit and implicit, that return data rows in the join table that meet the join criteria and query criteria. (The so-called link table is the database in the form of queries formed in the intermediate table). For example: The following statement 3 and statement 4 result is the same. Statement 3: Implicit inner join, without inner join, forms a Cartesian product of two tables. SELECT o.id,o.order_number,c.id,c.namefrom CUSTOMERS c,orders owhere c.id=o.customer_id; Statement 4: Internal connections shown, generally called Inner joins, inner JOIN, the resulting intermediate table is a Cartesian product with two tables filtered by on condition. Select O.id,o.order_number,c.id,c.namefrom CUSTOMERS C INNER JOIN ORDERS O on c.id=o.customer_id; Statement 5: Look directly at the sample select A.name, B.qty from Student A, (select Id,count (*) Qty from student Group ID) b where a.id =b.id;

Statement 6: At work, be sure to put Method 2, write Method 1
Method one saves very much time.
INNER JOIN ... The syntax format for on is:

From ((Table 1 INNER join table 2 on table 1. Field number = Table 2. Field number) INNER JOIN table 3 on table 1. Field number = Table 3. Field number)
INNER join table 4 on Member. Field number = Table 4. Field number) INNER join table X on Member. Field number = Table X. Field number www.2cto.com

Syntax Two: Large amounts of data can be very time consuming.

From table 1

INNER JOIN table 2 on table 1. Field number = Table 2. Field number

INNER JOIN table 3 on table 1. Field number = Table 3. Field number

INNER JOIN table 4 on Member. Field number = Table 4. Field number

INNER JOIN table X on Member. Field number = table x. Field number

Statement 7. Add a sample here the nested SELECT statement, query results, as a table B, to query    Select a. Product number, A. Initial quantity + B.numfrom  Warehouse A, (select product number, SUM (quantity) as num from Danju  GROUP by product number As b,where A. Product number =b. Product number

Summarize
The connection query is the core of the SQL query, and the connection type of the connection query is selected according to actual requirements. Assuming improper selection, not only can not improve query efficiency, but will bring some logic errors or poor performance. Here is a summary of the two-table connection query selection method based on:

1, check two tables related columns equal data with the internal connection.
2, col_l is a subset of the Col_r when using the right outer connection.
3, Col_r is a subset of col_l with left outer connection.
4, Col_r and col_l each other has the intersection but each other is not a subset of time with the whole outside.
5, the differential operation when using a joint query.
When querying multiple tables, these different connection types can be written to a piece. Like what:
SELECT T1. C1,t2. Cx,t3.cy
From TAB1 T1
INNER JOIN TAB2 T2 on (T1. C1=t2. C2)
INNER JOIN TAB3 T3 on (T1. C1=t2. C3)
Left OUTER joins TAB4 on (T2. C2=t3. C3);
WHERE T1. X >t3. Y
The above SQL query is a demonstration of multi-table joins.

Android Program ape must master the SQLite database table query