How can we find out
- Which products are provided by which suppliers?
- Which customers placed which orders?
- Which customers are buying which products?
Such reports require data from multiple tables. Enter joins.
Creating a report that returns the employee id and order id from the
Orders table is not difficult.
But this is not very useful as we cannot tell who the employee is that got this order. The next sample shows how we can use a join to make the report more useful.
Using full table names as prefixes can make SQL queries unnecessarily wordy. Table aliases can make the code a little more concise. The example below, which is identical in functionality to the query above, illustrates the use of table aliases.
An alias can be called whatever you want. Though typically it's the first letter(s) of the table name, it can be whatever makes sense to you as the Developer. For example, the alias for a table called Courses can be c, or crs, or debbie, etc.
Multi-table joins can get very complex and may also take a long time to process, but the syntax is relatively straightforward.
Note that, to join with a table, that table must be in the
FROM clause or must already be joined with the table in the
FROM clause. Consider the following.
SELECT table1.column, table2.column, table3.column
JOIN table3 ON (table2.column=table3.column)
JOIN table2 ON (table1.column=table2.column)
The above code would break because it attempts to join
table2 has been joined with