CC SQL 16 - create advanced join

Home > Preview

The flashcards below were created by user dau108 on FreezingBlue Flashcards.

  1. using table aliases
    • In addition to using aliases for column names and calculated fields, SQL also enables you to alias table names. There are two primary reasons to do this:
    • 1. To shorten the SQL syntax
    • 2. To enable multiple uses of the same table within a single SELECT statement.

    Take a look at the following SELECT statement. It is basically the same statement as an example used in the previous chapter, but it has been modified to use aliases:

    • Input
    • SELECT cust_name, cust_contact
    • FROM customers AS c, orders AS o, orderitems AS oi
    • WHERE c.cust_id = o.cust_id
    • AND oi.order_num = o.order_num
    • AND prod_id = 'TNT2';
  2. using different join types
    • self join
    • natural join
    • outer join
  3. self join
    Suppose that a problem was found with a product (item id DTNTR), and you therefore wanted to know all of the products made by the same vendor so as to determine if the problem applied to them, too. This query requires that you first find out which vendor creates item DTNTR, and next find which other products are made by the same vendor. The following is one way to approach this problem:


    • SELECT prod_id, prod_name
    • FROM products
    • WHERE vend_id = (SELECT vend_id
    • FROM products
    • WHERE prod_id = 'DTNTR');

    • Analysis

    This first solution uses subqueries. The inner SELECT statement does a simple retrieval to return the vend_id of the vendor that makes item DTNTR. That ID is the one used in the WHERE clause of the outer query so all items produced by that vendor are retrieved. (You learned all about subqueries in Chapter 14, "Working with Subqueries." Refer to that chapter for more information.)Now look at the same query using a join:

    • Input

    • SELECT p1.prod_id, p1.prod_name
    • FROM products AS p1, products AS p2
    • WHERE p1.vend_id = p2.vend_id
    • AND p2.prod_id = 'DTNTR';

    • Analysis

    The two tables needed in this query are actually the same table, and so theproducts table appears in the FROM clause twice. Although this is perfectly legal, any references to table products would be ambiguous because MySQL could not know to which instance of the products table you are referring.

    To resolve this problem, table aliases are used. The first occurrence of products has an alias of p1, and the second has an alias of p2. Now those aliases can be used as table names. The SELECT statement, for example, uses the p1 prefix to explicitly state the full name of the desired columns. If it did not, MySQL would return an error because there are two columns named prod_id and prod_name. It cannot know which one you want (even though, in truth, they are one and the same). The WHEREclause first joins the tables (by matching vend_id in p1 to vend_id in p2), and then it filters the data by prod_id in the second table to return only the desired data.
  4. natural joins
    Whenever tables are joined, at least one column appears in more than one table (the columns being joined). Standard joins (the inner joins you learned about in the previous chapter) return all data, even multiple occurrences of the same column. Anatural join simply eliminates those multiple occurrences so only one of each column is returned.

    How does it do this? The answer is it doesn'tyou do it. A natural join is a join in which you select only columns that are unique. This is typically done using a wildcard (SELECT *) for one table and explicit subsets of the columns for all other tables. The following is an example:

    • Input

    • SELECT c.*, o.order_num, o.order_date,
    • oi.prod_id, oi.quantity, OI.item_price
    • FROM customers AS c, orders AS o, orderitems AS oi
    • WHERE c.cust_id = o.cust_id
    • AND oi.order_num = o.order_num
    • AND prod_id = 'FB';

    • Analysis

    In this example, a wildcard is used for the first table only. All other columns are explicitly listed so no duplicate columns are retrieved.
  5. outer joins
    Most joins relate rows in one table with rows in another. But occasionally, you want to include rows that have no related rows. For example, you might use joins to accomplish the following tasks:

    Count how many orders each customer placed, including customers who have yet to place an order

    List all products with order quantities, including products not ordered by anyone

    Calculate average sale sizes, taking into account customers who have not yet placed an order

    In each of these examples, the join includes table rows that have no associated rows in the related table. This type of join is called an outer join.The following SELECT statement is a simple inner join. It retrieves a list of all customers and their orders:

    • Input

    • SELECT customers.cust_id, orders.order_num
    • FROM customers INNER JOIN orders
    • ON customers.cust_id = orders.cust_id;

    Outer join syntax is similar. To retrieve a list of all customers, including those who have placed no orders, you can do the following:

    • Input

    • SELECT customers.cust_id, orders.order_num
    • FROM customers LEFT OUTER JOIN orders
    • ON customers.cust_id = orders.cust_id;

    • Analysis

    Like the inner join seen in the previous chapter, this SELECT statement uses the keywords OUTER JOIN to specify the join type (instead of specifying it in the WHEREclause). But unlike inner joins, which relate rows in both tables, outer joins also include rows with no related rows. When using OUTER JOIN syntax you must use theRIGHT or LEFT keywords to specify the table from which to include all rows (RIGHTfor the one on the right of OUTER JOIN, and LEFT for the one on the left). The previous example uses LEFT OUTER JOIN to select all the rows from the table on the left in the FROM clause (the customers table). To select all the rows from the table on the right, you use a RIGHT OUTER JOIN as seen in this example:
  6. using joins with aggregate functions
    To demonstrate this, let's look at an example. You want to retrieve a list of all customers and the number of orders that each has placed. The following code uses the COUNT() function to achieve this:

    • Input

    • SELECT customers.cust_name,
    • customers.cust_id,
    • COUNT(orders.order_num) AS num_ord
    • FROM customers INNER JOIN orders
    • ON customers.cust_id = orders.cust_id
    • GROUP BY customers.cust_id;

Card Set Information

CC SQL 16 - create advanced join
2012-04-08 13:46:28
CC SQL 16 create advanced join

CC SQL 16 - create advanced join
Show Answers:

What would you like to do?

Home > Flashcards > Print Preview