CST 363 - Week 2

SQL Join

SQL is very flexible in its ability to join tables together. Normally tables are joined by matching primary keys with foreign keys because it allows for improved data integrity and faster queries via indexing. However, it is possible to combine tables without using primary and foreign keys. This practice is generally not recommend or used for the majority of SQL queries, but there may be instances where a join can be completed using standard keys (as long as the data types of the columns match). Since I am new to SQL, I did a bit of searching and found this example. 

Suppose we have two tables, dispatch and orders. The primary key for the orders table, ORD_NUM, is used as a foreign key in the dispatch table. However, we want to join our dispatch table and orders table using the column ORD_AMOUNT, which is found in both tables but is not a key. Since ORD_AMOUNT is of the same type in both tables, we can join the tables using it. 

Example Statement

Select the dispatch number, dispatch date, and sum of all order amounts that are equal to the order amount corresponding to each dispatch number. Use the dispatch and orders tables.

SQL Statement

SELECT a.dis_num, a.dis_date, SUM(b.ord_amount) 

FROM dispatch a, orders b

WHERE a.ord_amount=b.ord_amount

GROUP BY a.dis_num, b.dis_date;