MariaDB Crash Course - Ben Forta [46]
* * *
Tip: It Pays to Experiment
As you can see, there is often more than one way to perform any given SQL operation. And there is rarely a definitive right or wrong way. Performance can be affected by the type of operation, the amount of data in the tables, whether indexes and keys are present, and a whole slew of other criteria. Therefore, it is often worth experimenting with different selection mechanisms to find the one that works best for you.
* * *
Summary
Joins are one of the most important and powerful features in SQL, and using them effectively requires a basic understanding of relational database design. In this chapter, you learned some of the basics of relational database design as an introduction to learning about joins. You also learned how to create an equijoin (also known as an inner join), which is the most commonly used form of join. In the next chapter you learn how to create other types of joins.
16. Creating Advanced Joins
In this chapter, you learn all about additional join types—what they are and how to use them. You also learn how to use table aliases and how to use aggregate functions with joined tables.
Using Table Aliases
Back in Chapter 10, “Creating Calculated Fields,” you learned how to use aliases to refer to retrieved table columns. The syntax to alias a column looks like this:
Input
SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')')
AS vend_title
FROM vendors
ORDER BY vend_name;
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:
• To shorten the SQL syntax
• 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';
Analysis
Notice that the three tables in the FROM clauses all have aliases. customers AS c establishes c as an alias for customers, and so on. This enables you to use the abbreviated c instead of the full text customers. In this example, the table aliases were used only in the WHERE clause, but aliases are not limited to just WHERE. You can use aliases in the SELECT list, the ORDER BY clause, and in any other part of the statement as well.
It is also worth noting that table aliases are only used during query execution. Unlike column aliases, table aliases are never returned to the client.
Using Different Join Types
So far, you have used only simple joins known as inner joins or equijoins. You now take a look at three additional join types: the self join, the natural join, and the outer join.
Self Joins
As mentioned earlier, one of the primary reasons to use table aliases is to be able to refer to the same table more than once in a single SELECT statement. An example demonstrates this.
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 to determine whether 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:
Input
SELECT prod_id, prod_name
FROM products
WHERE vend_id = (SELECT vend_id
FROM products
WHERE prod_id = 'DTNTR');
Output
+---------+----------------+
| prod_id | prod_name |
+---------+----------------+
| DTNTR | Detonator |
| FB | Bird seed |
| FC | Carrots |
| SAFE | Safe |
| SLING | Sling |
| TNT1 | TNT (1 stick) |
| TNT2 | TNT (5 sticks) |
+---------+----------------+
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