Notes 0004

Joining Multiple Tables

The general format of the SELECT statement is:

SELECT column1, column2, column3...
FROM table1, table2, table3...
WHERE table1.joincolumn = table2.joincolumn...

What all this means is that if we've declared foreign keys on tables, we may join the two relations (linked by some key). The join condition specifies on which keys the tables are to be joined.

For example, take our productorder and product tables from notes 0002. Let's say we wanted to get the name of the product in the order list (instead of a product_id). Now, because those two tables have a relationship (namely built on product_id field), we can join them to make such a selection possible.

select order_id,productorder.quantity,description from productorder, product where productorder.product_id = product.product_id;

This produces:

  ORDER_ID   QUANTITY DESCRIPTION
---------- ---------- -------------
         1          2 mice
         1          2 keyboard
         2          5 mice
         3          3 keyboard

Notice that the output contains data from both tables.

Using this approach, we can link as many tables as we want in a single query. This is where the power of a relational database lies.

Note: If you don't specify the join condition (leave the WHERE part blank, the result is a Cartesian product of the two tables. Each field from one table is joined to each field in the other. For example:

select order_id,productorder.quantity,description from productorder, product;

Gets us:

  ORDER_ID   QUANTITY DESCRIPTION
---------- ---------- ------------
         1          2 mice
         1          2 mice
         2          5 mice
         3          3 mice
         1          2 keyboard
         1          2 keyboard
         2          5 keyboard
         3          3 keyboard

Note that each order_id (each order entry) has both the mouse and keyboard associated with it; which is wrong.

Joining Using Set Operations

The basic set operations of UNION, INTERSECT and MINUS are also available for joining tables.

The idea is that we have two SELECT statements, and we join the results.

They're briefly described next:

UNION: Returns all rows from both queries, but only displays duplicate rows once.

UNION ALL: Returns all rows from both queries, and displays all duplicate rows.

INTERSECT: Returns all rows that are returned by both queries.

MINUS: Returns the rows returned by the first query minus the matching rows returned by the second query.

For example, suppose we needed an imagined query to get all order ids where quantity is greater than 3, or the product is a mouse (product_id = 3).

First query would look like this:

select order_id from productorder where quantity > 3;

And returns:

  ORDER_ID
----------
         2

Second query is:

select order_id from productorder where product_id = 3;

With the resulting output being:

  ORDER_ID
----------
         1
         2

Now, obviously we can easily combine these two queries into 1, but lets assume we just wanted to use a UNION...

select order_id from productorder where product_id = 3 UNION select order_id from productorder where quantity > 3;

The result is an unexciting:

  ORDER_ID
----------
         1
         2

Now, it doesn't seem like it's doing much, but its actually evaluating these two SELECT statements and then does a UNION of the results. To see this a bit clearer, use UNION ALL.

select order_id from productorder where product_id = 3 UNION ALL select order_id from productorder where quantity > 3;

Produces:

  ORDER_ID
----------
         1
         2
         2

The duplicate there is because right now, we've used the UNION ALL, which doesn't remove duplicates. So 1,2 came from one query, and the other 2 came from another.

We can also do the intersection, which selects only items that were turned by both queries. So, taking:

select order_id from productorder where product_id = 3 INTERSECT select order_id from productorder where quantity > 3;

(this is just the query above, except with INTERSECT instead of UNION ALL) Anyway, it produces:

  ORDER_ID
----------
         2

The result that we got is 2, since 2 is returned by both queries. We can also do the MINUS:

select order_id from productorder where product_id = 3 MINUS select order_id from productorder where quantity > 3;

This returns:

  ORDER_ID
----------
         1

Since first query returns 1,2 and the second query returns just 2. Which means that after minus, we're left with just 1.

Note that results of MINUS also depend on the order of the queries. For example, if we swap the queries like:

select order_id from productorder where quantity > 3 MINUS select order_id from productorder where product_id = 3;

Simply returns:

no rows selected

Interesting, heh? Well, the first query returns just 2, and the second query returns 1,2. So, after the MINUS, we're left with nothing.

© 2006, Particle