Efficiency Notes for multiple table join queries in SQL

Recently when doing a Web site encountered a problem, I need to output B and C table two fields, but the tangle is, the program gets the parameter is the ID in table A, and a table and B table and C table associated with another field. As a result, I would need to read data from 3 tables, and at first I wanted to use SQL's multiple-table join query, but I was worried about inefficiency. If I read the value of a field associated with B or C first, and then run another SQL statement using that value to read the values in table B and C, is it more efficient? Thinking long, finally decided to write a program to test.

The following code is discuz! X2.5 's database, for example, inquires the username of Tid, a topic publisher of 1.

The code is as follows	Copy Code
<?php $start = time (); for ($i = 1; $i < 10000; $i + +) { $writerInfo = Db::fetch_first ("Select A.username from Pre_common_member as a, pre_forum_thread as B WHERE b.tid= ' 1 ' and A.uid=b.authorid '); } Echo (Time ()-$start); ?>

This code saves the current timestamp before executing the SQL query, executes the 10,000 multiple table join queries, and then subtracts the elapsed time stamp from the time stamp before the start to get the run time.
Then change the code in the loop block to read as follows:

The code is as follows	Copy Code
$tid = Db::fetch_first ("Select Authorid from Pre_forum_thread WHERE tid= ' 1"); $user = Db::fetch_first ("Select username from pre_common_member WHERE uid= ' $tid [Authorid] '");

This time, the SQL statement is divided into two executions.
To make the results accurate, the two pieces of code were executed three times, resulting in the first code executing at 13, 13, and 15 seconds, while the second code execution time was 23, 21, 22, respectively.
It can be seen that the SQL of multiple table joins is divided into several executions, the time will be about 80% longer than a multiple table join, the conclusion is that the direct execution of a row of multiple table joins more efficient

Here's a little digression, but it's also about the multiple-table joint query

Inner join,full outer join,left join,right jion
Internal connection inner Join both tables are satisfied with the combination
Full outer the same combination of two tables, a table has, B table does not have the data (shown as null), the same B table has
Table A does not display as (null)
A table left JOIN B table left JOIN, based on a table, a table of all the data, B table some combination. No null
A table right join B table, with the B table as the basis, B table of all the data, a table of some combinations. No null

Execution in Query Analyzer:
--Build Table Table1,table2:
CREATE TABLE table1 (ID int,name varchar (10))
CREATE TABLE table2 (ID int,score int)
INSERT INTO table1 Select 1, ' Lee '
INSERT INTO Table1 Select 2, ' Zhang '
INSERT INTO table1 Select 4, ' Wang '
Insert INTO table2 Select 1,90
Insert INTO table2 Select 2,100
Insert INTO table2 select 3,70
such as table
-------------------------------------------------
table1|table2|
-------------------------------------------------
idname|idscore|
1lee|190|
2zhang|2100|
4wang|370|
-------------------------------------------------

The following are performed in Query Analyzer

One, outer connection
1. Concept: Include left outer joins, right outer joins, or full outer joins

2. Left-side join: A/outer JOIN
(1) The result set of the left outer join includes all the rows of the left table specified in the OUTER clause, not just the rows that the join columns match. If a row in the left table does not have a matching row in the right table, all picklist columns in the right table in the associated result set row are null (NULL).
(2) SQL statement

The code is as follows	Copy Code
SELECT * FROM table1 LEFT join table2 on Table1.id=table2.id -------------Results------------- Idnameidscore ------------------------------ 1lee190 2zhang2100 4wangNULLNULL ------------------------------

Remarks: Contains all the clauses of Table1, returns table2 corresponding fields according to the specified criteria, and does not conform to null display

3. Right connection: Right-hand join or outer join
(1) A right outer join is a reverse join of a left outer join. All rows from the right table will be returned. If a row in the right table does not have a matching row in the left table, a null value is returned for left table.
(2) SQL statement

The code is as follows	Copy Code
SELECT * FROM table1 right join table2 on Table1.id=table2.id -------------Results------------- Idnameidscore ------------------------------ 1lee190 2zhang2100 NULLNULL370

------------------------------
Remarks: Contains all the clauses of table2, returns table1 corresponding fields according to the specified criteria, and does not conform to null display

4. Complete outer join: Full JOIN or fully outer join
(1) A full outer join returns all rows in the left and right tables. When a row does not match rows in another table, the select list column for the other table contains null values. If there is a matching row between the tables, the entire result set row contains the data values for the base table.
(2) SQL statement

The code is as follows	Copy Code
SELECT * FROM table1 full join table2 on Table1.id=table2.id -------------Results------------- Idnameidscore ------------------------------ 1lee190 2zhang2100 4wangNULLNULL NULLNULL370

------------------------------
Comments: Returns the and left and right connections (see upper left, right-hand connection)

Two, the internal connection
1. Concept: An inner join is a join that compares the values of the columns to be joined with a comparison operator

2. INNER JOIN: Join or INNER JOIN

3.sql statement

The code is as follows	Copy Code
SELECT * FROM table1 join table2 on Table1.id=table2.id -------------Results------------- Idnameidscore ------------------------------ 1lee190 2zhang2100

------------------------------
Remarks: Returns only the Table1 and table2 columns that match the criteria

4. Equivalence (same as the following execution effect)

The code is as follows	Copy Code
A:select a.*,b.* from table1 a,table2 b where a.id=b.id B:select * FROM table1 Cross join Table2 where table1.id=table2.id

(Note: Cross join after the condition can only be used where, can not be used on)

Cross-connect (complete)

1. Concept: A cross join without a WHERE clause produces a Cartesian product of the table involved in the join. The number of rows in the first table multiplied by the number of rows in the second table equals the size of the Cartesian product result set. (Table1 and table2 cross joins generate 3*3=9 Records)

2. Cross-connect: Cross join (without conditions where ...)

3.sql statement

The code is as follows	Copy Code
SELECT * FROM table1 cross join Table2 -------------Results------------- Idnameidscore ------------------------------ 1lee190 2zhang190 4wang190 1lee2100 2zhang2100 4wang2100 1lee370 2zhang370 4wang370

------------------------------
Note: Returns the 3*3=9 record, that is, the Cartesian product

4. Equivalence (same as the following execution effect)

code is as follows	copy code
a:select * from Table1,table2