mysql advanced join-using table aliases and using join conditions

Using table aliases

In addition to aliases being used for column names and calculated fields, SQL also allows aliases for table names. There are two main reasons for doing this:

1. Shorten the SQL statement;

2. Allow the same table to be used multiple times in a single SELECT statement.

Please see the SELECT statement below. It is basically the same statement used in the example in the previous chapter, but changed to use an alias:

Input:

select cust_name,cust_contact from customers as c,order as o,orderitems as oi where c.cust_id o.cust_id and oi.order_num = o.order_num and prod_id = 'TNT2';

Analysis: You can see that all three tables in the FROM clause Has an alias. customers AS c establishes c as an alias for customers , and so on. This enables using the abbreviated c instead of the full name customers . In this example, the table alias is used only in the WHERE clause. However, table aliases can be used not only in the WHERE clause, but also in SELECT lists, ORDER BY clauses, and other parts of statements.

It should be noted that table aliases are only used during query execution. Unlike column aliases, table aliases are not returned to the client.

Using Joins and Join Conditions

Before summarizing the content about joins, it is necessary to summarize some points about joins and their use.

1. Pay attention to the type of connection used. Generally we use inner joins, but it is also effective to use outer joins.

2. Make sure to use the correct connection conditions, otherwise incorrect data will be returned.

3. The connection condition should always be provided, otherwise a Cartesian product will be obtained.

4. A join can contain multiple tables, and even different join types can be used for each join. Although this is legal and generally useful, each connection should be tested separately before testing them together. This will make troubleshooting easier.

The above is the detailed content of mysql advanced join-using table aliases and using join conditions. For more information, please follow other related articles on the PHP Chinese website!