SQL must know will be · < 10> note; link table


To meet the increasing workload without fail. A well-designed database or application is called a good scalability(scale well).


Association (JOIN) is a kind of mechanism, to tables in a SELECT statement, so it is called the connection

Create a connection

Create a connection is very simple, can specify ways to link all the tables and their associated:

1 SELECT vend_name, prod_name, prod_price
2 FROM Vendors, Products
3 WHERE Vendors.vend_id = Products.vend_id;

Warning: the fully qualified name

Ambiguity may be referenced columns, must use the fully qualified name (partition table and column names with a period). If you are citing a no table name restrictions of ambiguous names, most DBMS returns an error.

1 the importance of the WHERE clause

Use the WHERE clause to establish the association seems a bit strange, but is actually a very good reason. Remember, connection in a SELECT statement several tables, the corresponding relation is constructed in operation.

Cartesian product(cartesian product)

The results returned by the no join condition table relationships for Cartesian product. The number of rows retrieved will be the number of rows in the first table multiplied by second the number of rows in the table.

Note: don't forget the WHERE clause

To ensure that all connections are WHERE clause, otherwise the DBMS will return more than the desired data much more data. Similarly, to ensure the correctness of the WHERE clause. The filter condition not correct will cause DBMS to return incorrect data.

Tip: fork connection

Sometimes, link returns of the Cartesian product, also known as cross linking(cross join).

2 inner connection

Equijoin equality test between two tables based on. The association also called internal connection(inner join)

And inline before the query grammar the same simple equivalent:

1 SELECT vend_name, prod_name, prod_price
2 FROM Vendors INNER JOIN Products
3 ON Vendors.vend_id = Products.vend_id;

3 join multiple tables

SQL does not limit the number of tables in a SELECT statement can link. The basic rules to create connection is the same. First, list all the tables, and then define relationships between tables. For example:

1 SELECT prod_name, vend_name, prod_price, quantity
2 FROM OrderItems, Products, Vendors
3 WHERE Products.vend_id = Vendors.vend_id
4 AND OrderItems.prod_id = Products.prod_id
5 AND order_num = 20007;

Note: performance considerations

Each specified in table DBMS Association at run time, to handle the connection. This treatment can be very expensive, so should pay attention to, don't join the unnecessary table. The more connected to the table, fell more.

Tip: to do the experiment

As you can see, the execution of any given SQL operation is generally more than one method. Little is absolutely right or wrong method. Performance may be affected by the type of operation, the use of DBMS, the data in the table, whether there are index or key conditions. Therefore, it is necessary to test different selection mechanism, find out the most suitable method for the specific circumstances of the.

Posted by Daniel at November 22, 2013 - 6:02 PM