Connection query in SQL and its optimization principle

Connection query is the main task of SQL, only a good grasp of the connection query and its optimization method is mastered the essence of SQL. Recently encountered in the interview about the connection query questions, feel that the answer is not very good, summed up.

For specific examples, refer to: http://www.w3school.com.cn/sql/sql_join.asp

Summarize:

Connection query principle and code optimization: If you want to make a connection query to Table1 and table2 two tables, the DBMS first finds the first tuple in Table1 and then scans the Table2 table from the beginning, looking for the Table1 tuple that corresponds to the table2 first tuple, Once found, the first tuple of the Table1 and the second tuple of the table2 are stitched together, and if the table2 is scanned again and not found, it begins to match the second tuple of Table1. It was not until the Table1 scan was completed. Returns the connection table to the query. Through the connection query principle can be clear, two tables of connection query is a two-layer loop, if the record number of Table1 is m,table2 the number of records is n then the time to make a connection query is the complexity of O (m*n). A visible connection query is time consuming, and if the table has a very large number of records, the query efficiency becomes very low. So in the writing of SQL statements, especially complex connection query is to optimize the query at the code level, the principle of optimization is to put the connection operation as far as possible at the end, as far as possible to the single table query, select operations in the front so that will be to a certain extent reduce the number of records connected query table, improve execution efficiency. SQL statements are also optimized by the DBMS itself.

Inner connection, left outer link, right outer link:

Inner Join=join can be understood as a natural connection, and is the most commonly used connection query that returns a matching row (at least one match will be returned).

An out join typically has a left-out join and a right-out join is a connection query that is relative to the inner join, where an out-of-row join refers to a table that is all returned if there are rows for the table to be returned, otherwise it is set to null. The right-out join is the opposite. It is easy to see the specific examples in the above connection.

Connection query in SQL and its optimization principle